ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC

0

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:

image

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:

image

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 http://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:

image

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:

image

It refers to the Application Module. The AppModuleImpl contains this code:

image

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:

GanttBindDuncan

No method is needed in the ViewObjectImpl class anymore. The code in the AppModuleImpl is changed to make use of the userData:

Gantt_userdate_dunacn

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.

image

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.

image

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:

image

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:

image

Create a new Java Class – called GanttBean. Add the following code to this class:

image

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:

image

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:

image

Then add a selectOneChoice element that the user can use to set the time scale zoom level:

image

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:

image

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:

image

Now everything is in place and you can run the application.

image

Toggle the Radio Buttons to try out different zooming levels:

image

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: http://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: http://technology.amis.nl/2013/02/16/using-trunc-in-sql-to-get-the-first-date-in-a-period/.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply