In a recent article – Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions – I discussed how to use Analytical Functions in SQL to cleverly (!) derive the valid periods from a database table that contains periods of inclusion and exclusion. A valid period is a period for which there is at least one inclusion and for which there is no exclusion. I used several powerpoint based graphics to illustrate the business case. For example:
to depict the periods of inclusion and exclusion and this figure to demonstrate how to derive the valid periods (the blue bars):
After completing this article – and fiddling around in Powerpoint quite a bit – I realized that for visualizing data in a table, I have a perfect tool at my fingertips: the Data Visualization Tags (DVT) in ADF 11g are created for this very purpose: turning data into information through visualization. And this rich library of DVTs components contains – in addition to fairly straightforward visualizations such as bar charts, pie charts and line graphs – also more complex visualization components such as the Bubble Chart, Thematic Map and Gantt Chart. The Gantt Chart has three appearances: Project, Resource Utilization and Scheduling. In this article I will demonstrate using both the first and the last type of Gantt Chart how to visualize the valid periods for the policies as introduced in the previous article. I will be using ADF BC to read data from the table POLICY_PERIODS. The final result of my efforts looks like this:
Note: the previous article explains how to calculate the valid periods (the blue bars) from the periods of inclusion and exclusion (the green and red bars).
Step 1: Create the Model using ADF Business Components
The database for our current challenge contains a single table POLICY_PERIODS with columns policy_id, start_date, end_date and in_or_out – the latter column specifies a period of Inclusion or Exclusion through the values I and O.
This table can be thought to contain master records – the policies – through a select distinct policy_id from POLICY_PERIODS or perhaps this Read Only ViewObject:
The records for the periods defined for a policy are accessed through an Entity Object based ViewObject – giving us potential to start creating new policy periods or manipulating existing ones – as follows:
A ViewLink is set up between the PoliciesView and the PolicyPeriodsView:
An Application Module is created with a simple Data Model, exposing the PoliciesView with a detail collection PolicyPeriods:
The PolicyModule can be run and tested at this point.
Step 2: Create the Page with a Table on Policies and a Project Gantt on the Policy Periods
Create a new JSF page – FusedPolicyPeriods.jspx. Drag the PoliciesView to the page and drop it as read only ADF Table which supports single row selection and sorting.
Drag the PolicyPeriodsView2 collection under the PoliciesView in the Data Control palette to the page and drop it as Project Gantt Chart:
Configure the Gantt Chart like this:
Whenever the policy selection in the table changes, we need the details for that policy that are shown in the Gantt Chart to be refreshed. This means we need to configure the partialTriggers attribute for the Gantt Chart. This attribute should include a reference to the table so that whenever the table is acted upon – with for example row selection – the chart is auto-refreshed in the same request cycle. Here is how to configure the partialTriggers attribute from the property palette for the Gantt Chart:
Run the page:
click in the table on the second policy. The Gantt Chart auto-refreshes, because of the partialTrigers attribute that links the chart to the table (events):
r
Note that the distinction between periods of Inclusion and Exclusion is non-existent in this visualization: all bars are created equal. Our next step towards visualization of the (meaning of the) data is using colors for the bars: red for Exclusion and green for inclusion.
Step 3: Format the bars in the Gantt Chart
A step that took quite some time is the transformation of the standard steel-blue bars for all policy periods into green bars for inclusions and red bars for exclusions. It turns out that programmatically the Gantt Chart style can be influenced by registering custom Formats for specific ‘task types’ (note that the Gantt Charts expects to render tasks so that is the lingo used) with the Gantt Chart’s TaskbarFormatManager. An example of registering green bars – for task type ==’I’ – and red bars for task type ==’O’:
The most important piece of configuration is the third parameter passed to the TaskbarFormat constructor: the name of an image file that is to be used as background for the taskbar.
The redGradient.png is a simple PNG file with a 3 x 20 pixels red gradient rectangle:
The method addFormatsToGantt is invoked from the setter method invoked on backingbean scope bean from the binding attribute on the Gantt Chart:
Run the page again:
Now the colors are visible, bright and shiny! Change the policy selection :
Step 4: Add Tooltips to the Gantt Chart
Tooltips are the handy bubble up indications that appear when the cursor is hovering over a bar in a Gantt Chart. We can specify what – if anything – should be displayed as tooltip:
The attributes tooltipKeyLabels and tooltipKeys are used to specify which labels (or prompts) are used in the tool tip (such as Start Date, End Date and Inclusion or Exclusion) and to list the attributes from which the values should be included in the tooltip. The methods these attributes are bound to in the example of the Policy Period charts – see the figure under step 3 – are implemented as follows:
Note: the number of elements in the String arrays returned by these two methods should always be the same – they really go hand in hand.
Step 5 – Use the Scheduling Gantt Chart for the Fused Periods and the Valid Periods
Another type of Gantt Chart besides the Project Gantt Chart is the Scheduling Gantt Chart. It primarily used to show for a list of resources the schedule of actually putting these resources to use. Part of this visualization is the ability to have multiple usages for the same resource – or in other words: multiple bars on the same line. That is what I need for visualizing the Fused Periods – just one green and one red line with none, one or multiple bars signifying Inclusion or Exclusion –
and the Valid Periods – just one line with steel blue bars indicating the result of ‘subtracting’ exclusions from the inclusions.
Creating the data bound scheduling gantt chart is done by dragging and dropping the ‘collection representing the resources’ onto the page as a Scheduling Gantt chart. This collection is the PolicyResourcesView under the PoliciesView2 collection for the FusedPeriods Gantt Chart:
and the PolicyValidPeriodsResourceView1 collection for the Valid Periods Gantt Chart.
The PolicyResourcesView produces two records for each Policy: one for the Inclusion row and one for the Exclusion row in the Gantt Chart.
This ViewObject has a ViewLink to VO FusedPolicyPeriodsView – with accessor FusedPolicyPeriods that returns all the green bars (in the Inclusion row) and all red bars (in the Exclusion row).
The Gantt Chart is configured accordingly:
Here the ResourceId is the InOrOut attribute with either I or O. The View Link accessor returns the FusedPeriods i.e. the bars for the chart. The Task Type – associated with the InOrOut attribute – is used to determine the format of the bar – primarily its color: red or green.
The dvt:schedulingGantt component is connected through the binding attribute to the same backingBeanScope bean we have seen before (in which the format is set up). Through partialTriggers the component is linked to the Policies table t1: whenever the table is refreshed, so will this Gantt Chart be. Tooltips are specified in the ganttChartFormatsBean.
The situation is much the same for the ValidPeriods in the third Gantt Chart. The PolicyValidPeriodsResourceView1 collection is dragged to the page and dropped as Scheduling Gantt.
The configuration of the DVT Data Binding is as shown:
The ViewLink from PolicyValidPeriodsResourceView to ValidPolicyPeriodsView returns the Valid Policy Period rows for the current policy. This read only ViewObject was created from the query developed in the article – Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions. The query uses several in-line views and a few analytical functions and is a good example of how Oracle SQL can be made to do interesting things in order to produce data that a front end application can easily consume and render. The query contains a lot of logic that could also have been programmed into the ADF application – but probably with more effort, more code, less reuse and worse performance.
From these rows produced by the ViewObject, the TaskId is represented by ValidPeriodId (unique and meaningless), the Task Type is InOrOut (which results in I for all rows) and the InclusionStart and InclusionEnd attributes in the row are mapped to the Start Time and End Time properties of the Scheduling Gantt Chart.
The dvt:schedulingGantt is configured similar to the previous one:
The full page has now come to look like this:
which is close enough to the powerpoint slide that was the draft for the end result (or in fact: it is quite a bit better)
Resources
Download the JDeveloper 11g R1 (PS5) application sources: PolicyPeriodsGanttChart.zip.
How to on creating the Scheduling Gantt Chart by Vikram Kohli that was very useful: http://kohlivikram.blogspot.nl/2010/04/scheduling-gantt-chart-in-adf-11g.html.
Oracle component documentation on Scheduling Gantt Component: http://docs.oracle.com/cd/E12839_01/apirefs.1111/e12418/tagdoc/dvt_schedulingGantt.html.
Documentation: Fusion Web User Interface Developer’s Guide on Gantt Charts: http://docs.oracle.com/cd/E25178_01/web.1111/b31973/dv_gantt.htm#CIHEACIB.
