3

Matrix Reports in APEX

Recently I saw a question on LinkedIn asking how to create Matrix style reports in APEX and found the discussion quite interesting as there were a mix of answers but nothing really concrete on how you could create one. So I thought I’d shed some light on a technique I have been using quite successfully for a number of years, with the most recent implementation just a few weeks ago.

About 4 years ago I came across a couple of posts from Anton Scheffer and Lucas Jellema from AMIS which described the use of a custom type which allowed you to pivot the last 2 columns of your query and turn it into a matrix style report. The great thing was that it is quite straight forward to use in APEX as we can use generic columns for our classic report using the option “Use Generic Column Names (parse query at runtime only)” e.g.

and for the report columns we have the option of either using “Column Names” or “PLSQL” e.g.

Here is the link to download the PIVOTIMPL function code written by Anton. I take absolutely no credit for this amazing bit of PLSQL code. All that I have done is write a simple wrapper around generating the SQL statement, replacing any substitutions, and replacing binds with the “V” function. I am considering updating Anton’s code in the future to accept binds that reference APEX session state items to boost query performance as on large data sets there can be significant degrade on performance using the “V” function.

To define your Classic report you can use a report type of “SQL Query (PLSQL function body returning SQL query)”

with a PLSQL function body returning a SQL query, similar to the following:

RETURN pd_matrix_reports.generate_matrix_query
      ( p_query_stmt => 'SELECT dept_directorate_id'||
                        ',      dept_directorate'||
                        ',      region_id'||
                        ',      region'||
                        ',      manager_id'||
                        ',      manager_name'||
                        ',      period_cal_year'||
                        ',      period_cal_year||'' ''||lpad(period_no,2,''0'') period'||
                        ',      end_year_count_link end_year_count '||
                        'FROM   pd_pdp_period_manager_vw '||
                        'WHERE  (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '||
                        'AND    (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '||
                        'AND    period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '||
                        'AND ( '||
                               'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '||
                             ')'
       , p_parse_query => FALSE
       );

and if you need to further filter the actual result set produced by the pivot operation (this is the actual data returned from the table cast so your pivoted data will now be in columns) you can concatenate the resulting string with an additional where clause e.g.

RETURN pd_matrix_reports.generate_matrix_query
      ( p_query_stmt => 'SELECT dept_directorate_id'||
                        ',      dept_directorate'||
                        ',      region_id'||
                        ',      region'||
                        ',      manager_id'||
                        ',      manager_name'||
                        ',      period_cal_year'||
                        ',      period_cal_year||'' ''||lpad(period_no,2,''0'') period'||
                        ',      end_year_count end_year_count '||
                        'FROM   pd_pdp_period_manager_vw '||
                        'WHERE  (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '||
                        'AND    (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '||
                        'AND    period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '||
                        'AND ( '||
                               'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '||
                             ')'
       , p_parse_query => FALSE
       )||
       ' AND EXISTS '||
                '( SELECT NULL '||
                  'FROM   pd_access_vw '||
                  'WHERE  dept_directorate_id = v.dept_directorate_id '||
                  'AND    region_id = v.region_id '||
                ')'||
       ' AND "2011/12 08" > 0';

In the above we can use a column filter on an actual data value e.g. “2011/12 08″ since our second last column has been transposed into columns thanks to Anton’s magical code. I say magical because it’s not the easiest piece of code to understand, but if you’re interested you can find more here’s the 11.2 documentation reference.

Here’s an example screenshot of 3 reports which use this technique to pivot 13 calendar periods in a year…

I can’t thank Anton, Lucas, and AMIS enough for documenting and sharing this code as it’s been a life and time saver in lots of past situations, so I hope you find it as useful as I have within APEX. The only thing you may want to keep your eye on is the dynamic creation of TYPES within your application schema which the code seems to be creating e.g.