The challenge I will address in this article is the following: I would like to provide a nice presentation of data aggregated by time period. For example: an overview of the number of employees that was hired in each year in each department (example is drawn from table EMP). The presentation could look like this:
To extend the challenge a little bit: I would like to be able to drill down. From the year level shown in this picture, to the Quarter level and even to the Month level. The Quarter level would look similar – but more fine grained:
This article shows how this challenge can be addressed using ADF DVT – Data Visualization components, more specially the Resource Utilization Gantt Chart. It will describe how ADF BC is used in conjunction with the SQL TRUNC function and a smart bind parameter to allow for dynamic zooming to different time aggregation levels. And the approach demonstrated in this article can easily be reused for other time based presentations.
The underlying database for this application will simply be good old tables DEPT and EMP. We only need to access to the tables in read only mode. The SQL query we can use to aggregate by the the desired time period – Year, Quarter, Month – was introduced in this blog article https://technology.amis.nl/2013/02/16/using-trunc-in-sql-to-get-the-first-date-in-a-period/. The key to this query is the use of the SQL operator TRUNC that will return the start of the period that contains the date that is fed in as the first parameter and that takes a YY, Q or MM format identifier as its second parameter.
We have to morph the query presented in that article into a shape that we can use in a ViewObject and that allows us to change the aggregation level at run time. First of all I have created a ViewObject for Departments. Next I create a ViewObject ResourcesHiredView based on the following query:
You will notice how this query does the aggregation at a time zoom level that is determined through the bind parameter bind_zoomlevel. This bind parameter is then defined to take its value from the Groovy expression adf.object.viewObject.zoomLevel. This refers to a getter method called getZoomLevel() defined in the ViewObjectImpl class created for the ResourcesHiredView ViewObject.
We have a little bit of a special situation here: we want to use a bind parameter to influence the query in this ViewObject. However the page will only have a data binding for the DepartmentsView and it will access the result from this ResourcesHiredView via a View Link Accessor on the DepartmentsView. There is no binding for the ResourcesHiredView and therefore there is no direct access to the bind parameter on this ViewObject. I have designed a bit of a workaround to still be able to set a value from the page that will influence the query result from the ResourcesHiredView. I am open for suggestions as to how to approach this in a leaner, smarter and more elegant way.
The getter method on ResourcesHiredViewImpl is defined as follows:
It refers to the Application Module. The AppModuleImpl contains this code:
EDIT (21-Feb-2013): based on a comment from Duncan Mills, the following approach would be preferable: use the userData Map as a place to store the Zoomlevel rather than a member variable in the AM. This is for two reasons:
1) The AM may be recycled and given to another user
2) If you store data in the userData area you can access it directly via groovy: adf.userSession.userData.zoomLevel and that would remove the need for the intermediate wrapper in the VO Impl class
Thanks Duncan for providing me with a much more elegant (and actually correct) approach
The ViewObject would be defined in the same way, except for the expression used for the bind variable:
No method is needed in the ViewObjectImpl class anymore. The code in the AppModuleImpl is changed to make use of the userData:
END OF EDIT
The Client Interface of the Application Module exposes the setZoomLevel method, which we will be using in the ViewController to actually manipulate the value of the bind parameter.
The last thing we need to do in the Model is to create a ViewLink from DepartmentsView to ResourcesHiredView and expose an accessor for that ViewLink in the DepartmentsView.
At this point, we can test the Model project by running the AppModule and trying out whether the correct aggregation level is applied, depending on the value passed to the AppModule’s setZoomLevel method.
The ViewController project
Create a new JSF page, called for example DepartmentHireeGantt.jspx. Drag the DepartmentsView from the Data Control Palette and drop it on the page, picking the Gantt | Resource Utilization Gantt as the option. Configure the Gantt Chart like this:
Departments are the Resources with Dname the Resource Identifier. The ViewLink Accessor on the DepartmentsView is used to access the time buckets (the time related metrics). The one metric the time buckets contain in this case is the NumberOfResources attribute, a single numeric value.
After pressing OK, the page is extended and a Page Definition with the Gantt Binding is created. Go to the Page Definition, and create an Operation Binding for the setZoomLevel operation on the AppModule Data Control:
Create a new Java Class – called GanttBean. Add the following code to this class:
Note that whenever the handleZoomLevelChange method is called, the new value set for the zoom level is passed to the setZoomLevel method on the Application Module and thereby exposed as the underlying value for the bind parameter used in the query in ViewObject ResourcesHiredView. After that, the Gantt Chart gets refreshed – through the partial target refresh.
Also generate into this bean the accessor methods for the zoomLevel member variable.
Define a managed bean – viewScope – based on this Class in the adfc-config.xml file:
Then turn to the page again. Set the binding attribute on the Gantt Chart to refer to the theGantt property in the managed bean. Also set the startTime and endTime to the first of July 1980 and the first of January 1983 respectively:
Then add a selectOneChoice element that the user can use to set the time scale zoom level:
Finally, the time axis used in the Resource Utilization Gantt should be set according to the selected zoom level. This is done by first adding two methods to the GanttBean:
Each will return the currently desired level for the timescale axis.
Then the time axis element in the page needs to refer to the managed bean:
Now everything is in place and you can run the application.
Toggle the Radio Buttons to try out different zooming levels:
Resources
Download the JDeveloper 11gR1 11.1.1.6 Application that contains the sources discussed in this article: ZoomingResourceGanttChart.zip.
Blog article introducing ADF DVT Gantt Charts: https://technology.amis.nl/2013/02/16/adf-dvt-speed-date-meeting-the-gantt-charts/.
Blog article introducing the SQL TRUNC function for finding the first date of a period in which a date falls: https://technology.amis.nl/2013/02/16/using-trunc-in-sql-to-get-the-first-date-in-a-period/.
