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:
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.
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.
Matrix Report PIVOT TABLE report - Package detail .
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| 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 ); |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| 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'; |
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.
Matrix Report PIVOT TABLE report - Package detail .
create or replace
TYPE "PIVOTIMPL" as object
(
ret_type anytype, -- The return type of the table function
stmt varchar2(32767),
fmt varchar2(32767),
cur integer,
static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in date := null )
return number,
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in date := null )
return number,
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in date := null )
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_fmt in varchar2 := 'upper(@p@)', dummy in date )
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);
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;
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 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
return odciconst.error;
end;
--
static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in date )
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, null );
else
sctx := PivotImpl( elem_typ, p_stmt, '@p@', null );
end if;
return odciconst.success;
end;
--
static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_fmt in varchar2 := 'upper(@p@)', dummy in date )
return number
is
cur integer;
numcols number;
desc_tab dbms_sql.desc_tab2;
t_stmt 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;
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 || '"';
end loop;
--
type_code := sctx.ret_type.getinfo( prec
, scale
, len
, csid
, csfrm
, schema_name
, type_name
, version
, attr_count
);
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( ', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab( numcols ).col_name || ' ) )'
, '@p@'
, desc_tab( numcols - 1 ).col_name
);
end loop;
t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
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;
--
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;
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;
--
dbms_output.put_line( 'fetch' );
if dbms_sql.fetch_rows( self.cur ) = 0
then
return odciconst.success;
end if;
--
dbms_output.put_line( 'done' );
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
);
dbms_output.put_line( 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;
begin
c := self.cur;
dbms_sql.close_cursor( c );
return odciconst.success;
end;
end;
/
create or replace
PACKAGE "AF_MATRIX_REPORTS"
is
-- --------------------------------------------------------------------------------------------------------------------------
-- -
-- - Procedure : generate_matrix_query
-- - Package Body : af_matrix_reports
-- - Author : Matt Nolan
-- - Private/Public : Public
-- -
-- - Description : Wrapper for generating the SQL query
-- - Change
-- - Matt Nolan - 10-09-2008 - Added in Query Parsing to easily identify the
-- - problem with the query if there's a typo etc.
-- - ------------------------------------------------------------------------------------------------------------------------
FUNCTION generate_matrix_query
( p_query_stmt IN VARCHAR2
, p_parse_query IN BOOLEAN DEFAULT FALSE
) RETURN VARCHAR2;
-- --------------------------------------------------------------------------------------------------------------------------
-- -
-- - Procedure : pivot_sql
-- - Package Body : af_matrix_reports
-- - Author : Matt Nolan
-- - Date : 10-SEP-2008
-- - Private/Public : Public
-- -
-- - Description
-- - performs any substitutions and bind variable repalcements with V function
-- - ------------------------------------------------------------------------------------------------------------------------
FUNCTION pivot_sql
( p_stmt in varchar2
) RETURN VARCHAR2;
-- --------------------------------------------------------------------------------------------------------------------------
-- -
-- - Procedure : pivot
-- - Package Body : af_matrix_reports
-- - Author : Matt Nolan
-- - Date : 10-SEP-2008
-- - Private/Public : Public
-- -
-- - Description
-- - ------------------------------------------------------------------------------------------------------------------------
FUNCTION pivot
( p_stmt in varchar2
, p_fmt in varchar2 := 'upper(@p@)'
, dummy in date := null
) RETURN ANYDATASET PIPELINED USING PivotImpl;
END;
/
create or replace
PACKAGE BODY "AF_MATRIX_REPORTS"
is
-- --------------------------------------------------------------------------------------------------------------------------
-- -
-- - Procedure : pivot_sql
-- - Package Body : af_matrix_reports
-- - Author : Matt Nolan
-- - Date : 10-SEP-2008
-- - Private/Public : Public
-- -
-- - Description
-- - performs any substitutions and bind variable repalcements with V function
-- - ------------------------------------------------------------------------------------------------------------------------
FUNCTION pivot_sql
( p_stmt VARCHAR2
) RETURN VARCHAR2
AS
l_stmt VARCHAR2(32767) := p_stmt;
BEGIN
--
-- Replace any APEX substitutions
--
l_stmt := apex_application.do_substitutions(p_stmt);
--
-- Longer term using actual binds will be better for performance than using
-- the V function
--
l_stmt := regexp_replace(l_stmt,':([a-zA-Z0-9_]*)','v(''\1'')');
RETURN l_stmt;
END pivot_sql;
-- --------------------------------------------------------------------------------------------------------------------------
-- -
-- - Procedure : generate_matrix_query
-- - Package Body : af_matrix_reports
-- - Author : Matt Nolan
-- - Private/Public : Public
-- -
-- - Description : Wrapper for generating the SQL query
-- - Change
-- - Matt Nolan - 10-09-2008 - Added in Query Parsing to easily identify the
-- - problem with the query if there's a typo etc.
-- - ------------------------------------------------------------------------------------------------------------------------
FUNCTION generate_matrix_query
( p_query_stmt IN VARCHAR2
, p_parse_query IN BOOLEAN DEFAULT FALSE
) RETURN VARCHAR2
is
-- tag sysdate to the query to ensure it gets hard parsed at run time
-- as we noticed inconsistent data results in APEX reports on 10g
l_sysdate VARCHAR2(30) := TO_CHAR(sysdate, 'dd-mm-yy hh24:mi:ss');
l_query VARCHAR2(32767);
l_cursor PLS_INTEGER;
l_result PLS_INTEGER;
BEGIN
l_query := pivot_sql(p_query_stmt);
l_query := regexp_replace(l_query, '''','''''');
l_query := 'select * from table(af_matrix_reports.pivot('''||l_query||''')) v where '''||l_sysdate||''' = '''||l_sysdate||'''';
IF p_parse_query THEN
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse
( c => l_cursor
, statement => l_query
, language_flag => dbms_sql.native
);
dbms_sql.close_cursor(l_cursor);
END IF;
RETURN l_query;
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(l_cursor) THEN
dbms_sql.close_cursor(l_cursor);
END IF;
RAISE;
END generate_matrix_query;
END;
/
Thank you very much for this post. I have installed the types and package as mentioned. How should I proceed further? I am not clear. Please can you help?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks to Oracle corporation for providing alternate of this procedure in the Action button, Named Pivot Report (APEX 5.0 to own wards)
ReplyDeleteRegards
Rahman Shahid
Magnificent blog I visit this blog it's extremely wonderful. Interestingly, in this blog content composed plainly and reasonable. The substance of data is useful.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
oracle fusion financials classroom training
Oracle Fusion HCM Classroom Training
oracle cpq online training / Oracle CPQ Class Room Training
Oracle Taleo Online Training