ADF DVT - Converting Row values as Column Headers

When you want to show row data as column headers you do not need to do any coding for creating the ADF table on the fly. Instead you can use PIVOT Table available in ADF DVT components. This has inbuilt support for showing the row values as column headers. Also this will help you do the grouping based on that value and display the values for the rows.

Today, I would take the example related to my project, so that I can show you pivot table and the queries that I have used.

In this example, I want to show the count of patient based on a cancellation code and on an weekly bases. So my row values should be converted into column headers for the Cancellation Reason. First should be group by Weekly No and then group by Cancellation Code. So final look of the Pivot table would be like the below screen shot.

Here Year-Month, Week Count, Cancellation Reason are all Group By columns based on which the No. Of Patient value is got. One more advantage of using Pivot Table is user will be allowed to move the row/column headers as per their wish. If you see the following images all of them were generated at run-time by dragging the column/row headers..

Now we will go into modelling this.

We will have to have a SQL based VO with the following query which will give us the ability for using it in pivot table.

SELECT COUNT(*) AS NO_OF_PATIENTS,
  APPOINTMENT_WEEK,
  APPOINTMENT_YR_MON,
  APPOINTMENT_MONTH_YEAR,
  CANCELLATION,
  CANCEL_REASON,
FROM appointment
GROUP BY APPOINTMENT_WEEK,
  APPOINTMENT_YR_MON,
  CANCELLATION,
  APPOINTMENT_MONTH_YEAR,
  CANCEL_REASON

Count will get the No. Of Patients and all the other values are either used as a Row Header or Column Header. Once this VO is done. You can use it to create Pivot Table.

1) Add the VO to an AM of that page.
2) Drag and drop the VO into your jsff page from the DataControls and select ADF Pivot Table from the menu as shown in the below.

3) Next we need to drag and drop the attributes into Column headers and Row Headers as per our basic requirement. In the PivotTable design view we can drag and drop the columns as shown below

Here if you see the columns that are dragged and dropped into Row/Column Header(highlighted in black) with the grey background will be shown as a header and NoOfPatient(highlighted in red) column will be the data shown in the Pivot Table.

When you drop the VO in the page you would see  PivotTableDataControl getting created in the page bindings. So going forward if you want to change any of the layout or add/remove the column then you can edit this data binding. The same pivot table design view will be shown.

In the page you would only see a dvt:pivotTable tag with the dataControl binding and default values. You are now ready to test it.

In my next post, I will cover how to add drill down behavior for the pivot table to show the details for each row value.