Implementing a matrix report

The major learning outcomes of this section are as follows:

• Creating matrix reports
• Understanding and using the with clause
• Understanding and using the pivot operator
• Understanding string aggregation techniques
• Data highlighting using CSS, jQuery, and Dynamic Actions
• Advanced formatting using APEX templates.
• Understanding and using the Dynamic Query region in APEX

Matrix reports also known as pivoted reports are helpful when we have a measure to be sliced and diced on two or more dimensions. For example, let's say, we want to know the count of people (measure) for every combination of shirt color (Dimension 1) and pant color (Dimension 2). A matrix report for this will look like the following:

	Color
Apparel	Green	Yellow	Blue
Pant	2	5	7
Shirt	7	0	7

We will now see the process to create one such matrix report in APEX. The tricky part in matrix report is that the number of columns change according to the data. In the preceding example, if the data had Red as the colour of a few pants or shirts then we would have had an additional column. So creation of dynamic columns and pivoting of the aggregates make a matix report different from traditional reports. Before I dig in deeper, I wish to tell you the process that we are about to follow. We will first have a look at the with clause to understand its importance in our report. We will then try to get a hold of the pivot operator, which is the heart of generating columns on the fly to create a matrix report. We will then have a look at some of the string aggregation techniques. We are using string aggregation (listagg) to get a comma-separated list of values which is assigned to an item (P3_EMPLOYEE_LIST). This item is then used for creating a dynamic in clause of the pivot operator, and for creating dynamic headings for the dynamic columns.

Understanding the with clause

If we use a sub query at multiple places in our query then we can give a name to that sub query using the with clause, and then reference it at multiple places in a bigger query. This helps to improve the readability and the performance of the query.
The Matrix Report region of page 3 of the reference application uses the with clause. The following query is pasted for your convenience:

WITH pivot_data AS ( SELECT department_id, job_id, salary
 FROM oehr_employees)
  SELECT * FROM   pivot_data
  PIVOT ( SUM(salary)      --List Clause  
  FOR department_id          -- For clause
  IN  (&P3_EMPLOYEE_LIST.)  ); --Dynamic In Clause

The output of the query looks like the following:

Understanding the Oracle 11g's pivot operator

Let me talk a little about pivot operator. This has been used in the preceding query. The basic function of a pivot query is to aggregate the data based on the function written in its list clause and to transpose the rows of the aggregated result as columns. The pivot operator has three clauses namely, the list clause, the for clause, and the in clause. The list clause describes the aggregation functions such as sum and avg. The for clause lists the columns which are to be grouped and pivoted. The in clause lists the columns which will appear in the output. The only trick in implementing a matrix report is to make a dynamic pivot in clause (IN (&P3_EMPLOYEE_LIST.)), which we will cover in the next section. Note that we have checked the Use Generic Column Names (parse query at runtime only) radio button under the Region Source in the Source section of the Region Definition page of the Matrix Report region. This is because the type and the number of columns generated by this query will depend on the data and we cannot have a fixed number of columns.
You would see another method of making a dynamic in clause in the Implementing XML Pivot queries section of Chapter 9, Performance Analysis.

Understanding string aggregation

We are talking about string aggregation because we wish to create a comma-separated list, which can not only help us in creating a dynamic in clause, but also help us in creating dynamic headings for the columns generated by the pivot operator. The following are the ways of performing string aggregations:

• Using the listagg function of Oracle 11g: We are using this method in our matrix report. listagg lets us order the data and allows us to supply a separator for the values in the list but does not allow us to use distinct.The following is the query source of the P3_EMPLOYEE_LIST item of the reference application:
  

  select  listagg(department_id,',') WITHIN GROUP (ORDER BY department_id) from (select distinct department_id from oehr_employees)

  P3_EMPLOYEE_LIST is used in the region query to create a dynamic in clause. The query is pasted in the Understanding the with clause section. We also use P3_EMPLOYEE_LIST to create dynamic column headings. Go to the Report Attributes section of the Matrix Report region of the reference application. You would see that PL/SQL is selected in the Headings Type radio button. You would find the following code in the Function returning colon delimited headings section:
  

  return 'Job list:'||replace(v('P3_EMPLOYEE_LIST'),',',':');

  This creates dynamic column headings.
• Another method to do string aggregation is wm_concat. wm_concat allows distinct, but produces only a comma-separated list and does not order the result. The syntax is as follows:
  

  select wm_concat(distinct job_id) from oehr_employees

• String concatenation can also be done using the collect function, but the method requires the creation of a function and a type, and this involves some complexity.
• Hierarchical queries also have the potential to be used for string aggregation. We will discuss hierarchical queries in a short while.