Few tips on Gantt Charts

Oracle APEX offers several beautiful chart types which are based on Oracle JET Data Visualizations. Gantt Chart is one such beautiful and useful chart. However, when I have searched in Google for some help on Gantt Charts, there are not many blogs posts talking about it. So, I thought, I could write one with few basic tips which I have learned this year.

I have used Gantt Chart to display employees calendar data and my targeted output was something like below.

Pic-1

Looks simple, correct? However, it's little tricky to get there.

Multiple Tasks Per Row:

When I look at the output, first I thought, I will have to write a query which gives tasks data and employee data with 1 row per task. That is, if there are 10 tasks to be displayed, then there should be 10 rows in SQL query output. But, it's not correct. Instead, I should have 1 row for each task + 1 row for each employee (parent). So I need to write a query which will output data in below format.

Pic-2

As we can see, there is 1 row per task and also there is 1 row per each employee (highlighted in blue color). Also, observe employee id EMP1 is used as PARENT_TASK_ID for EMP1 tasks.

Next, we need to do column mapping as follows. (Gantt Chart > Series > Column Mapping)

Pic-3

In Page Designer, we can read "Help" text for each option to get more info.

Next, define the timeline for which Gantt chart should be rendered. (Gantt Chart > Series > Timeline)

Pic-4

P6_START_DATE is "Start Date" field and P6_END_DATE is "End Date" field shown in Pic-1 page. P6_END_DATE_HIDDEN is hidden page item calculated based on P6_END_DATE field. We will discuss about this in "Date Handling" section.

By this step, we should have working Gantt chart ready.

Colors and Borders:

As we can see in Pic-1, I have used different colors and borders for different tasks, based on task type and other conditions. For this, I have added new column "CSS_CLASS" in series SQL query. This column gives CSS class(es) name for each task. Next, I need to specify this column name in "Task CSS Classes" section (Gantt Chart > Series > Appearance) as shown below.

Pic-5

Gantt Chart, like any other JET chart, is rendered as "Scalable Vector Graphics". So we have limited style options which we can use. We can use "fill" for "background-color" and "stroke" for "border".

We can put our CSS in "Page > CSS > Inline" section.

e.g. CSS styles

/* orangle fill */
path.demo-f-orange {fill:orange}

/* red border */
path.demo-b-red {
    stroke: red;
    stroke-width: 1px;
}

I found SVG and CSS tutorials useful on this topic. 

Dynamic Major, Minor Axes:

On top of the Gantt chart, we can see two axes. One Major Axis and other Minor Axis.

Pic-6

We can define these axes values under "Gantt Chart > Axes" Sections. If we want to display Gantt Chart for a fixed time window, then we can choose appropriate "Time Scale" and "Zoom Scale" for Major and Minor axes. However, if users can select the time window, then fixed axes may not look good in all cases.

Luckily, we can set these options using "JavaScript Initialization Code" (Gantt Chart > Attributes)

In my example, when user selects Start and End dates and submits page, then I have added page computation which will calculate days between start and end dates and saving the value to page item P6_GANTT_DAYS. 

Then, I have written below JS code in  "JavaScript Initialization Code" section.

function(options) {
  var windowDays = $v("P6_GANTT_DAYS");

  if (windowDays > 0 && windowDays <= 90) {
    options.minorAxis = {
      "scale": "days",
      "zoomOrder": ['quarters', 'months', 'weeks', 'days']
    };
    options.majorAxis = {
      "scale": "months",
      "zoomOrder": ['months', 'weeks', 'days']
    };
  } else if (windowDays > 90 && windowDays <= 270) {
    options.minorAxis = {
      "scale": "months",
      "zoomOrder": ['quarters', 'months', 'weeks', 'days']
    };
    options.majorAxis = {
      "scale": "quarters",
      "zoomOrder": ['quarters', 'months', 'weeks', 'days']
    };
  } else if (windowDays > 270 && windowDays <= 730) {
    options.minorAxis = {
      "scale": "months",
      "zoomOrder": ['quarters', 'months', 'weeks', 'days']
    };
    options.majorAxis = {
      "scale": "years",
      "zoomOrder": ['years', 'quarters', 'months', 'weeks', 'days']
    };
  } else {
    options.minorAxis = {
      "scale": "quarters",
      "zoomOrder": ['quarters', 'months', 'weeks', 'days']
    };
    options.majorAxis = {
      "scale": "years",
      "zoomOrder": ['years', 'quarters', 'months', 'weeks', 'days']
    };
  }

  return options;
}

With this, major and minor axes are set dynamically based on time window selected.

Date Handling:

When we try to render Gantt chart for two days, lets say 01-JAN-2020 as start date and 02-JAN-2020 as end date, then we would see only one day 01-JAN-2020 is displayed in Gantt chart. This is because, when we select date in date picker item, by default, time will be set as 0 hours. So chart will display only 01-JAN-2020. And if we try to display it for one day, by selecting same Start and End dates, then chart will not be rendered at all. To over come this problem, I have created another hidden field P6_END_DATE_HIDDEN. This field value is calculated based on P6_END_DATE (i.e. P6_END_DATE + 1 day)

Highlighting Today's Date:

We can highlight today's date in major or minor axis using JavaScript. Below code shows how we can highlight todays date in minor-axis. 

• Create a Dynamic Action and give any proper name like "Highlight Todays Date"
• In "When" Section, select
• Event: After Refresh
• Selection Type: Region
• Region: Gantt Chart Region
• In "True" section, choose "Execute JavaScript Code" action and put below JavaScript code in "Code" section.

var today = new Date();
// by default, dates in major, minor axis are displayed as MM/DD format
// change below code depending on how date format is being displayed
var todayGanttFormat = (today.getMonth() + 1) + "/" + today.getDate();
//console.log(todayGanttFormat);
// gantt-chart is region static id
$("text.oj-gantt-minor-axis-label", "#gantt-chart").each(function() {
  //console.log($(this).html());
  // here we are changing color of today's date to red
  if ($(this).html() == todayGanttFormat)
    $(this).css("fill", "red");
});

Tables Structure and Gantt Chart Query: 

Tables used for this demo and sample data.

Gantt Chart Series SQL Query:

-- employee records
SELECT
    'EMP' || emp.emp_id     task_id,
    NULL                    parent_task_id,
    emp.email_address       email_address,
    emp.email_address       task_name,
    NULL                    start_date,
    NULL                    end_date,
    'EMPLOYEE'              task_type,
    NULL                    css_class,
    NULL                    cust_tooltip
FROM
    gantt_emp emp
UNION ALL
-- employee tasks
SELECT
    'TK' || task.task_id            task_id,
    'EMP' || emp.emp_id             parent_task_id,
    emp.email_address               email_address,
    task.task_name                  task_name,
    task.start_date                 start_date,
    task.end_date + 1 - 1 / 1440    end_date,
    task.task_type,
    CASE
        WHEN task.task_type = 'LEAVE' THEN
                'demo-f-orange'
    END
    || ' '
    ||
    CASE
        WHEN task.billable_flag = 'N' THEN
                'demo-b-red'
        ELSE
            NULL
    END                             css_class,
    task.task_type                  cust_tooltip
FROM
         gantt_emp emp
    JOIN gantt_tasks task ON task.emp_id = emp.emp_id

Link to demo

APEX Versions: 18.2, 19.2

Thank you.

Other posts on this topic:

Few Tips on Gantt Charts - Part 2

It's continuation of above blogpost where few more tips about Gantt Chart are discussed. Tips discussed are "Vertical Grid for Minor Axis" and "Dynamic Height for Gantt Chart".

Custom Tooltips

This blog post explains how we can show custom tooltips on Gantt Chart Task bars.

Defining Link for Gantt Chart

This blog post provides a work around for implementing "Link" feature in Gantt Chart.