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.
Hi!
I really like the anytype solution, it’s very powerful but I think it does a bit too much in an APEX context. This thread (https://forums.oracle.com/forums/thread.jspa?threadID=697760) describes a different implementation, a function that uses dbms_sql to just build a sql statement and lets a reports region do the rest. I used an adapted version in the past, worked fine.
Btw, there seems to be an issue with
http://www.theapexfreelancer.com/apex/wwv_flow.show?p_flow_id=APEXFREELANCER&p_flow_step_id=ABOUT&p_instance=0&p_request=APPLICATION_PROCESS=ccpublic&x01=PIVOTIMPL&x02=http%3A%2F%2Fwww%2Etheapexfreelancer%2Ecom%2Fc%2Fpivotimpl%2Esql
There is a newline between
Set-Cookie:WWV_PUBLIC_SESSION_708=27534212605702
and
Location: http://www.theapexfreelancer.com/c/pivotimpl.sql
therefore the page displays the Location: header in the page on the 1st request. It shows pivotimpl.sql after a refresh. APEX wants to set the cookie, because it does not yet exist when I click on the download link from http://blog.theapexfreelancer.com/2011/11/matrix-reports-in-apex/
I think that could be fixed by calling “htp.init;” at the beginning of your ccpublic process.
Regards,
Christian
Thanks Chris, I hadn’t come across that before so I will definitely take a closer look. Thanks for also pointing out the issue, I’ve just made the change so hopefully the issue is resolved.
Nice!
An updated version of the Pivot code:
CREATE OR REPLACE TYPE PivotImpl as object
(
ret_type anytype, — The return type of the table function
stmt varchar2(32767),
fmt varchar2(32767),
aggr_function varchar2(32767),
override_column_names varchar2(32767),
invalidate varchar2(1),
cur integer,
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number,
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number,
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number,
member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
return number,
member function ODCITableClose( self in PivotImpl )
return number
)
/
CREATE OR REPLACE TYPE BODY PivotImpl as
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number
is
atyp anytype;
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
rc sys_refcursor;
t_c2 varchar2(32767);
t_fmt varchar2(1000);
t_cnt pls_integer := 0;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse( cur, p_stmt, dbms_sql.native );
dbms_sql.describe_columns2( cur, numcols, desc_tab );
dbms_sql.close_cursor( cur );
–
anytype.begincreate( dbms_types.typecode_object, atyp );
for i in 1 .. numcols – 2
loop
atyp.addattr( desc_tab( i ).col_name
, case desc_tab( i ).col_type
when 1 then dbms_types.typecode_varchar2
when 2 then dbms_types.typecode_number
when 9 then dbms_types.typecode_varchar2
when 11 then dbms_types.typecode_varchar2 — show rowid as varchar2
when 12 then dbms_types.typecode_date
when 208 then dbms_types.typecode_varchar2 — show urowid as varchar2
when 96 then dbms_types.typecode_char
when 180 then dbms_types.typecode_timestamp
when 181 then dbms_types.typecode_timestamp_tz
when 231 then dbms_types.typecode_timestamp_ltz
when 182 then dbms_types.typecode_interval_ym
when 183 then dbms_types.typecode_interval_ds
end
, desc_tab( i ).col_precision
, desc_tab( i ).col_scale
, case desc_tab( i ).col_type
when 11 then 18 — for rowid col_max_len = 16, and 18 characters are shown
else desc_tab( i ).col_max_len
end
, desc_tab( i ).col_charsetid
, desc_tab( i ).col_charsetform
);
end loop;
if instr( p_fmt, ‘@p@’ ) > 0
then
t_fmt := p_fmt;
else
t_fmt := ‘@p@’;
end if;
open rc for replace( ‘select distinct ‘ || t_fmt || ‘
from( ‘ || p_stmt || ‘ )
order by ‘ || t_fmt
, ‘@p@’
, desc_tab( numcols – 1 ).col_name
);
loop
fetch rc into t_c2;
exit when rc%notfound;
if substr( upper( p_override_column_names ), 1, 1 ) = ‘Y’
then
t_cnt := t_cnt + 1;
atyp.addattr( ‘COL_’ || upper( p_aggr_function ) || ‘_’ || t_cnt
, dbms_types.typecode_varchar2
, null
, null
, 4000
, desc_tab( numcols ).col_charsetid
, desc_tab( numcols ).col_charsetform
);
else
atyp.addattr( t_c2
, case desc_tab( numcols ).col_type
when 1 then dbms_types.typecode_varchar2
when 2 then dbms_types.typecode_number
when 9 then dbms_types.typecode_varchar2
when 11 then dbms_types.typecode_varchar2 — show rowid as varchar2
when 12 then dbms_types.typecode_date
when 208 then dbms_types.typecode_urowid
when 96 then dbms_types.typecode_char
when 180 then dbms_types.typecode_timestamp
when 181 then dbms_types.typecode_timestamp_tz
when 231 then dbms_types.typecode_timestamp_ltz
when 182 then dbms_types.typecode_interval_ym
when 183 then dbms_types.typecode_interval_ds
end
, desc_tab( numcols ).col_precision
, desc_tab( numcols ).col_scale
, case desc_tab( numcols ).col_type
when 11 then 18 — for rowid col_max_len = 16, and 18 characters are shown
else desc_tab( numcols ).col_max_len
end
, desc_tab( numcols ).col_charsetid
, desc_tab( numcols ).col_charsetform
);
end if;
end loop;
close rc;
atyp.endcreate;
anytype.begincreate( dbms_types.typecode_table, rtype );
rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
rtype.endcreate();
return odciconst.success;
exception
when others
then
anytype.begincreate( dbms_types.typecode_object, atyp );
atyp.addattr( ‘ERROR’
, dbms_types.typecode_varchar2
, null
, null
, 4000
, null
, null
);
atyp.endcreate;
anytype.begincreate( dbms_types.typecode_table, rtype );
rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
rtype.endcreate();
return odciconst.success;
end;
–
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number
is
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
elem_typ anytype;
aname varchar2(30);
tc pls_integer;
begin
tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
–
if instr( p_fmt, ‘@p@’ ) > 0
then
sctx := PivotImpl( elem_typ, p_stmt, p_fmt, p_aggr_function, p_override_column_names, upper( substr( p_invalidate, 1, 1 ) ), null );
else
sctx := PivotImpl( elem_typ, p_stmt, ‘@p@’, p_aggr_function, p_override_column_names, upper( substr( p_invalidate, 1, 1 ) ), null );
end if;
return odciconst.success;
end;
–
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_aggr_function in varchar2 := ‘max’, p_override_column_names in varchar2 := ‘N’, p_fmt in varchar2 := ‘@p@’, p_invalidate varchar2 := ‘Y’ )
return number
is
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
t_stmt varchar2(32767);
t_stmt2 varchar2(32767);
type_code pls_integer;
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
schema_name varchar2(30);
type_name varchar2(30);
version varchar2(30);
attr_count pls_integer;
attr_type anytype;
attr_name varchar2(100);
dummy2 integer;
rc sys_refcursor;
t_c2 varchar2(32767);
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse( cur, p_stmt, dbms_sql.native );
dbms_sql.describe_columns2( cur, numcols, desc_tab );
dbms_sql.close_cursor( cur );
–
for i in 1 .. numcols – 2
loop
t_stmt := t_stmt || ‘, “‘ || desc_tab( i ).col_name || ‘”‘ || chr(10);
t_stmt2 := t_stmt2 || ‘, null’ || chr(10);
end loop;
–
type_code := sctx.ret_type.getinfo( prec
, scale
, len
, csid
, csfrm
, schema_name
, type_name
, version
, attr_count
);
if substr( upper( p_override_column_names ), 1, 1 ) = ‘Y’
then
open rc for replace( ‘select distinct ‘ || sctx.fmt || ‘
from( ‘ || p_stmt || ‘ )
order by ‘ || sctx.fmt
, ‘@p@’
, desc_tab( numcols – 1 ).col_name
);
loop
fetch rc into t_c2;
exit when rc%notfound;
t_stmt := t_stmt || replace( ‘, to_char( ‘ || sctx.aggr_function || ‘( decode( ‘ || sctx.fmt || ‘, ‘
, ‘@p@’
, ‘”‘ || desc_tab( numcols – 1 ).col_name || ‘”‘
);
t_stmt := t_stmt || ”” || t_c2 || ”’, “‘ || desc_tab( numcols ).col_name || ‘” ) ) )’ || chr(10);
t_stmt2 := t_stmt2 || ‘, ”’ || t_c2 || ”” || chr(10);
end loop;
else
for i in numcols – 1 .. attr_count
loop
type_code := sctx.ret_type.getattreleminfo( i
, prec
, scale
, len
, csid
, csfrm
, attr_type
, attr_name
);
t_stmt := t_stmt || replace( ‘, ‘ || sctx.aggr_function || ‘( decode( ‘ || sctx.fmt || ‘, ‘
, ‘@p@’
, ‘”‘ || desc_tab( numcols – 1 ).col_name || ‘”‘
);
t_stmt := t_stmt || ”” || attr_name || ”’, “‘ || desc_tab( numcols ).col_name || ‘” ) )’ || chr(10);
end loop;
end if;
t_stmt := ‘select ‘ || substr( t_stmt, 2 ) || ‘ from ( ‘ || sctx.stmt || ‘ )’;
t_stmt2 := ‘select ‘ || substr( t_stmt2, 2 ) || ‘ from dual union all’ || chr(10);
for i in 1 .. numcols – 2
loop
if i = 1
then
t_stmt := t_stmt || ‘ group by “‘ || desc_tab( i ).col_name || ‘”‘;
else
t_stmt := t_stmt || ‘, “‘ || desc_tab( i ).col_name || ‘”‘;
end if;
end loop;
t_stmt := t_stmt || ‘ order by 1 nulls first’;
if substr( upper( p_override_column_names ), 1, 1 ) = ‘Y’
then
t_stmt := t_stmt2 || t_stmt;
end if;
dbms_output.put_line( t_stmt );
–
sctx.cur := dbms_sql.open_cursor;
dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
for i in 1 .. attr_count
loop
type_code := sctx.ret_type.getattreleminfo( i
, prec
, scale
, len
, csid
, csfrm
, attr_type
, attr_name
);
case type_code
when dbms_types.typecode_char then dbms_sql.define_column( sctx.cur, i, ‘x’, 32767 );
when dbms_types.typecode_varchar2 then dbms_sql.define_column( sctx.cur, i, ‘x’, 32767 );
when dbms_types.typecode_number then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
when dbms_types.typecode_date then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
when dbms_types.typecode_urowid then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
when dbms_types.typecode_timestamp then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
when dbms_types.typecode_timestamp_tz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
when dbms_types.typecode_interval_ym then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
when dbms_types.typecode_interval_ds then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
end case;
end loop;
dummy2 := dbms_sql.execute( sctx.cur );
return odciconst.success;
exception
when others
then
t_stmt := q’~select ‘Oops, not a valid query?’ from dual~’;
sctx.cur := dbms_sql.open_cursor;
dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
dbms_sql.define_column( sctx.cur, 1, ‘x’, 32767 );
dummy2 := dbms_sql.execute( sctx.cur );
return odciconst.success;
end;
–
member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
return number
is
c1_col_type pls_integer;
type_code pls_integer;
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
schema_name varchar2(30);
type_name varchar2(30);
version varchar2(30);
attr_count pls_integer;
attr_type anytype;
attr_name varchar2(100);
v1 varchar2(32767);
n1 number;
d1 date;
ur1 urowid;
ids1 interval day to second;
iym1 interval year to month;
ts1 timestamp;
tstz1 timestamp with time zone;
tsltz1 timestamp with local time zone;
begin
outset := null;
if nrows < 1
then
– is this possible???
return odciconst.success;
end if;
–
if dbms_sql.fetch_rows( self.cur ) = 0
then
return odciconst.success;
end if;
–
type_code := self.ret_type.getinfo( prec
, scale
, len
, csid
, csfrm
, schema_name
, type_name
, version
, attr_count
);
anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
outset.addinstance;
outset.piecewise();
for i in 1 .. attr_count
loop
type_code := self.ret_type.getattreleminfo( i
, prec
, scale
, len
, csid
, csfrm
, attr_type
, attr_name
);
case type_code
when dbms_types.typecode_char then
dbms_sql.column_value( self.cur, i, v1 );
outset.setchar( v1 );
when dbms_types.typecode_varchar2 then
dbms_sql.column_value( self.cur, i, v1 );
outset.setvarchar2( v1 );
when dbms_types.typecode_number then
dbms_sql.column_value( self.cur, i, n1 );
outset.setnumber( n1 );
when dbms_types.typecode_date then
dbms_sql.column_value( self.cur, i, d1 );
outset.setdate( d1 );
when dbms_types.typecode_urowid then
dbms_sql.column_value( self.cur, i, ur1 );
outset.seturowid( ur1 );
when dbms_types.typecode_interval_ds then
dbms_sql.column_value( self.cur, i, ids1 );
outset.setintervalds( ids1 );
when dbms_types.typecode_interval_ym then
dbms_sql.column_value( self.cur, i, iym1 );
outset.setintervalym( iym1 );
when dbms_types.typecode_timestamp then
dbms_sql.column_value( self.cur, i, ts1 );
outset.settimestamp( ts1 );
when dbms_types.typecode_timestamp_tz then
dbms_sql.column_value( self.cur, i, tstz1 );
outset.settimestamptz( tstz1 );
when dbms_types.typecode_timestamp_ltz then
dbms_sql.column_value( self.cur, i, tsltz1 );
outset.settimestampltz( tsltz1 );
end case;
end loop;
outset.endcreate;
return odciconst.success;
end;
–
member function ODCITableClose( self in PivotImpl )
return number
is
c integer;
t_id number;
begin
c := self.cur;
dbms_sql.close_cursor( c );
if self.invalidate = 'Y'
then
select object_id
into t_id
from user_objects
where object_name = 'PIVOTIMPL' — name of your type!
and object_type = 'TYPE BODY';
— invalidating of the type body forces that ODCITableDescribe is executed for every call to the pivot function
— and we do need that to make sure that any new columns are picked up (= new values for the pivoting column)
dbms_utility.invalidate( t_id );
end if;
return odciconst.success;
end;
end;
/
create or replace
function pivot( p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@' )
return anydataset pipelined using PivotImpl;
/
select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'sum' ) )
select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'count' ) )
select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 )/7 y from dual connect by level <= 50~', 'sum', 'Y' ) )
select * from table( pivot( q'~select mod( level, 3 ) + 1 aa, sysdate + mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'count', 'n', 'to_char(@p@,''dd-mon-yyyy'')' ) )