Matrix Report can be achieved using ADF Pivot Table, we have to set drill down parameters to have hierarchical view in the report. Data Aggregation option allows us to use mathematical operations like Sum, Average, Count, Maximum, Minimum, Standard Deviation, and Variance on data.
This article shows how to create a Drill down Matrix Report with Department wise Total Salary using ADF Pivot Table.
- Create Business Components from DEPARTMENTS and EMPLOYEES tables of HR schema.
- Drop EmployeesView1 from Data Control Palette to jspx page as ADF Pivot Table.
- In Select display attributes panel first drop DepartmentId from 'Available Attributes' list to 'Row and Columns edges', drop EmployeeId below DepartmentId in 'Row and Columns edges'.
- From 'Available Attributes' list drop FirstName, LastName, Salary, JobId to 'Data Labels' section.
- In 'Configure drilling' panel select 'Insert Drilling' option, Set 'Insert Parent Row' as 'After Children'. Check enable Drill Path for Drill Down report. Drilling option gives a Tree structure for the selected Attributes. Drill Paths are derived from the Attributes dropped in 'Row and Column Edges' In Select display attributes panel.
- Add 'Salary' attribute to configure Data Aggregation.
- Select 'Category Totals' tab and add Categories Attribute as 'DepartmentId' and 'Insert Total' as 'After'.
- Configure sorting categories based on DepartmentId attribute in ascending order.
- Finish the wizard and run the page.
- Expand DepartmentId to find the Employees, also note the Department wise Salary and Total Salary.