ADF Faces 11g: Reloading the Matrix – using the Pivot Table component

In the past months I have written several times about the Matrix component I developed for ADF Faces to be used in several of our projects. Yesterday I demonstrated this component in my presentation at OOW 2007 on ADF Faces. This morning I decided to take a look at the new PivotTable component that is available in ADF Faces 11g. I am curious to see what it can do, what it looks like, how difficult it is to set up and how my work compares to it.

In this article I will present some of my conclusions and demonstrate how you can quickly setup a ‘pivot table’ or matrix in your ADF Faces (11g) application.

The steps to go through are very straightforward. The summary is:
– make sure you create a collection in a Data Control (usually a ViewObject in an ApplicationModule, but potentially also just a Collection returning method in a class) that provides the values you want to use for row and column labels as well the cell values (or to be aggregated to become cell values)
– drag this collection to the page in which you want to create the pivot table
– in the pivot table data binding editor specify the characteristics of the rows (which attribute(s) should be displayed in header), the columns (likewise) and the cells. Furthermore, you can specify aggregations to be performed, to calculate sum, count, max, min, avg over the values in the cells within the row (giving us a row total) or the column (a column total); the pivot table binding editor has a preview option that present live data preview in the design time, which is very helpful
– tweak and tune the behavior and appearance of the pivot table in the page editor
– run the page

Let’s go through a step by step example of creating a pivot table that will present the number of staff and their combined salary per Department and Job combination. Obviously this stunningly useful matrix is based on the EMP and DEPT tables in the SCOTT schema. It is also the example I used when presenting the matrix component I created in an earlier Blog article.

1. Create a New Application, using technology template Fusion Web Application (ADF)

2. In the Model project, create an ApplicationModule HrmService with a single read only ViewObject called HrmPivotView (or any other name you fancy). Use the following query:

select d.dname
,      d.deptno
,      e.job
,      count(e.empno) count_staff
,      sum(e.sal) sum_sal
,      avg(e.sal) avg_sal
from   dept d
       join
       emp e
       on (d.deptno = e.deptno)
group
by     d.dname
,      d.deptno
,      e.job

Add the view object HrmPivotView to the ApplicationModule’s DataModel.

3. In the ViewController project, create a new JSF page, called HrmMatrix.jspx

4. Drag the HrmPivotView collection from the DataController palette to the new JSF page. Drop it as an ADF Pivot Table in the Tables category.

5. The ADF Pivot Table Data Binding editor opens. Here we will specify what the matrix should display for its rows, columns and cells.
From the list of Available Attributes, drag Dname and Deptno to the Column Edge. Next drag Job to the Row Edge. Finally drag CountStaff and SumStaff to the cell area.

We would like to have both column and row totals. The column total shows the sum of the cell values within the Department – so it gives us the total number of staff in each Department as well as the total Salary Sum in the department. We get this by adding an entry on the Data Aggregation tab. Select Dname as the attribute – to link the aggregation to the Dname column – and pick Sum as the Function and After as Aggregate Value display – to show the column total at the bottom of the column. For the row total – the sum within the Job – we do something very similar: add an aggregation entry, select the Job attribute, select the Sum Function and After as Aggregate Value Display. Also set a the Aggregate Value Label to indicate what it is you are looking at in this row total column.

Note: we can use only a single aggregation operator for the entire row or column. In this case where we have two data values – count of staff and sum of salary – it is okay to use sum for aggregating both of them. However, if my data values would have been count of staff and average salary, that would have been different: for count of staff, a sum aggregation is desirable, however for Average Salary, an overall Average is the wanted aggregation. And that seems not be possible: both data values are subjected to the same aggregation operator.

let’s take a look at the Preview tab, which presents a live data preview of what we have specified thusfar:

Close the Pivot Table Binding Editor. The page now looks like this in the editor:

6. To make the pivot table fully visible on the page, we usually need to adjust the width by setting the inlineStyle attribute to include width:700px or whatever we want the width to be

7. Time to run the page and be amazed.

Amazed? Well, it looks nice but it is what we configured and previewed, isn’t it? What is amazing about it?

Well, what we did not discuss beforehand is why this truly deserves the name of pivot table. The end user can do pivot on this data him or herself, in the webpage, at runtime. Just like those fancy BI tools do. You can simply drag the column header

to the row header section and drop it.

The pivot table is refreshed immediately and now looks like this:

Of course we can also drag the Departments to the Row Header section,

to get this:

The Pivot Table allows the end user some additional tuning options, in terms of resizing rows and columns:

The next thing to investigate for me will be the operations that can be performed on the pivot table. There is support for Drilling on the matrix. The easiest way to find out more about it, seems to be:
– add a drill listener to the pivot table
– create (have JDeveloper generate) the code for this drill listener
– add a breakpoint in the listener method
– run the page in debug mode and drill on the matrix
– in the debugger, inspect the contents of the drillEvent object

However, so far nothing seems to happen
when I try to drill.