Out of the box usage of ADF DVT Scheduling Gantt Chart to report Database Query Results using stacked bar charts per time period

Gantt Charts in ADF are interesting components to visualize data that is organized according to time. The Gantt Charts have a horizontal time axis. In rows along the vertical axis, resources or tasks are displayed. The cells in this time/resource matrix represent information about the resource or the task at some point in time or more specifically: in some time period.

In this recent blog-article, I explained how we can use the Schedule Gantt chart to present results per resource per period using something closely resembling horizontal bar charts. The key thought is that when we present data associated with a standard period, we can use the Gantt Chart’s capability to set the length of the bar to express the size of the value in a specific period. More specifically: we can use the end date property to manipulate the length of the bar. This article we will take this one step further and create a Stacked Bar Chart for each resource for each period. In this case, I will present the number of Employees hired per Department per Year and do so using Stacked Bar Charts with segments per Job. The result – driven directly from SQL query in a read only ViewObject – looks like this:

We can rapidly interpret the data: hiring was most intensive in 1981. Department 20 was both the earliest and the last to hire employees. The red segment represents the Job SALESMAN. It is clear that only department 30 ever hired any salesmen. The yellow job was hired in every department – as is the dark blue job. These represent CLERK and MANAGER respectively.

Model – preparing the data for the Gantt Chart

The data model for the Schedule Gantt consists of a collection of Resources. Using ADF BC, that would be a ViewObject – in this case returning all records from table DEPT. So the first step after creating the Database Connection is the wizard based creation of Entity Object Department, ViewObject DepartmentsView and ApplicationModule HrmService.

Next, the Schedule Gantt also expects in the data model that each resource contains an accessor to the tasks or more generally to the cells that will be represented as bars. Each element in this collection should specify a start date and end date, a unique identifier and typically a label. Additionally, it can expose a type – used to categorize and assign colors per category – and other properties for example to be used in the tooltips shown when hovering over the bars.

The accessor is most easily added using a ViewLink between the ViewObject returning the Resources and the ViewObject returning the Tasks or cells – and make sure that an accessor method for this ViewLink is exposed in the Resources ViewObject. Note that you do not need the second ViewObject at all in the Application Module’s Data Model.

I will approach the challenge in two steps. First I will create a Gantt Chart that presents bars with proportionate length for the number of employees hired per year and per department without looking at the jobs. In a second iteration I will add the Jobs and introduce the stacked-ness of the bars.

The SQL query I need to create for this first step should return a record for each cell – which means for each combination of Department and Year – that has a value associated with it. I want to have the number of employees hired and I would like to know how long the bar should be. By calculating the ratio between the value in the cell and the maximum value in all the cells, I know which percentage of the total length available – 365 days – this cell’s bar can take up. Finally, I want the query to produce the start date (first day of the year) and the end date (first day of the year plus the 365 days times the percentage) of the bar. With all this information, the Gantt Chart can easily be produced.

This query first sets up a subquery emp_count_per_d_and_y. It returns the number of employees hired per department and per year. The main query uses this subquery to calculate the start date and end date for each cell. The start date is just the first day of the year. The end date is a little bit harder to derive: it is calculated by determining the ratio between the cell value (emp_count) and the highest cell value and multiplying it with 360 [days] then adding it to the start date. The highest cell value is found using a Scalar Subquery that queries from the emp_count_per_d_and_y subquery.Additionally the query should return the unique identifier of the cell which is the combination of deptno and hireyear.

The result of this query is shown next:

Based on this query, I create a ViewObject:

Next I create the ViewLink between the DepartmentsView and the EmpCountView.

The Data Control at this point is exposed to the ADF Faces developer:

View – take the data and visualize it

Implementing the Gantt Chart that will display the data in a pretty Gantt Chart has become simple now. Create a new JSF page. Drag the DepartmentsView1 collection to the page and drop it as Gantt | Schedule. Then configure the Gantt Chart:

The label in the Appearance tab is set to EmpCount.

Some configuration on the dvt:scheduleGantt component in the jspx file

Set the startTime and endTime. Set the labelPlacement. Set the major timeAxis scale to years and the minor timeAxis scale to quarters. Set rendered to false for the second timeAxis as we do not need it.

Now we can run our page. We get the overview of the number of employees hired per department per year. The length of the bar is proportional to the number of employees hired. The bars in 1981 for departments ACCOUNTING and RESEARCH should be about a third of the long bar in (SALES,1981). And so on.

 

Model – preparing the data for the Gantt Chart with Stacked Bars

Now we are ready for the second iteration. The query should be extended to count the number of hirees not just per year and department (the cell) but also per job (the segment within the stacked bar). And then we need to calculate the length of each bar relative to the maximum number of employees (over all jobs) in a single year in a single department. The real complexity comes when we calculate the start date and the end date. Because we want to stack the count per job, the start date is not going to be the first day of the year for all bars: it is the end date of the previous job’s bar except for the first job. And the end date is now not relative to the first day of the year, but to the end date of the previous job.

 

This query starts with the subquery emp_count_per_d_and_y that we saw before. It returns the number of employees hired per department and per year. I have introduced a second subquery called emp_count_and_percentage that calculates the cell_identifier, cell_label and the emp_count_percentage for the Job in the Department in the Year. This percentage is calculated as the ratio between the count for the job, department and year combination and the maximum value for the sum of all emp_counts in a single deptno and hireyear combination (the largest number of employees hired in any department in any year).

The main query uses this subquery emp_count_and_percentage to calculate the start date and end date for each cell. The start date now is the first day of the year plus 360 [days] times the sum of the percentages of the preceding jobs in this cell – for the current Department and Year. Note: the coalesce (similar to NVL) is used to cater for the first Job dealt with in a cell for which the sum of preceding job percentages is undefined.

The end date is derived in almost the same way as the start day: it will sum all percentages of the preceding rows including the current one and multiply with 360 [days] then adding it to the start date.

The result of this query is shown next:

Based on this query, I create a ViewObject:

Next I create the ViewLink between the DepartmentsView and the EmpPerJobYearCountView.

The Data Control exposed to the ADF Faces developer is not changed:

View – take the data and visualize the Stacked Bars

Implementing the Gantt Chart that will display the data in a pretty Gantt Chart is again quite simple now. Create a new JSF page. Drag the DepartmentsView1 collection to the page and drop it as Gantt | Schedule. Then configure the Gantt Chart:

The Label for the bars is set using the CellLabel attribute:

Configure the dvt:scheduleGantt in the jspx page. Just like before set the labelPlacement, startDate and endDate. Then also set the binding attribute, the tooltipKeys and tooltipKeyLabels attributes as shown in the next figure. The bean these attributes refer to is created a little bit further on.

Create a class GanttChartColorManager. This class will set up the custom formats for the various jobs. It will also provide the tooltipKeys and tooltipKeyLabels that were already used in the dvt:scheduleGantt.

Configure the managed bean

 

Run the page:

Just to show off a little I will insert two records into table EMP then update the page to see the effect on the Gantt Chart:

Refresh the page. It will show new bars in (ACCOUNTING,1982) and (RESEARCH,1980):

and perhaps do a little Update:

resulting in:

 

Resources

Download the JDeveloper 11gR1 PS5 (11.1.1.6) Application with the code for the example introduced in this article: GanttSchedulerHorizontalBars.zip.