Function returning PLSQL Pivot Report

Udit Nagpal

Hi All,

I have created a PLSQL function returning classic report which is a pivot report using below query:

DECLARE

  l_pivot_cols VARCHAR2(4000);

  l_pivot_qry VARCHAR2(4000);

BEGIN

  SELECT ''''||listagg(MONTH_YEAR, ''',''') within group(order by MONTH_YEAR)||''''

    INTO l_pivot_cols from

    (

SELECT TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY') MONTH_YEAR

FROM dc_cats_data_2019

WHERE DC_NETWORK= 'BC000248P01E'--:P8_PROJECT_CODE

GROUP BY TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY')

);

  l_pivot_qry :=

        'select * from ( '

     || 'select

     SUM(DC_HOURS) DC_HOURS,

            DC_ACTIVITY,TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY'') MON_YEAR

            FROM dc_cats_data_2019'

          ||'   WHERE DC_NETWORK ='''

          || :P8_PROJECT_CODE

          || ''''

||'GROUP BY TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY''),DC_ACTIVITY'

     || ') '

     || 'pivot ( SUM(nvl(DC_HOURS,0)) for MON_YEAR in ('||l_pivot_cols||') )' ;

--EXECUTE IMMEDIATE l_pivot_qry;

  RETURN l_pivot_qry;

END;

It works perfect when I hard code value of  project code in the bold part of query however when I try to make the it dynamic by passing a item it is not showing any pivot columns.

Any suggestions would be really appreciated.

Best Regards.

fac586

Udit Nagpal wrote:I have created a PLSQL function returning classic report which is a pivot report using below query:

DECLAREl_pivot_cols VARCHAR2(4000);l_pivot_qry VARCHAR2(4000);BEGINSELECT ''''||listagg(MONTH_YEAR, ''',''') within group(order by MONTH_YEAR)||''''INTO l_pivot_cols from(SELECT TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY') MONTH_YEARFROM dc_cats_data_2019WHERE DC_NETWORK= 'BC000248P01E'--:P8_PROJECT_CODEGROUP BY TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY'));l_pivot_qry :='select * from ( '|| 'selectSUM(DC_HOURS) DC_HOURS,DC_ACTIVITY,TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY'') MON_YEARFROM dc_cats_data_2019'||' WHERE DC_NETWORK ='''|| :P8_PROJECT_CODE|| ''''||'GROUP BY TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY''),DC_ACTIVITY'|| ') '|| 'pivot ( SUM(nvl(DC_HOURS,0)) for MON_YEAR in ('||l_pivot_cols||') )' ;--EXECUTE IMMEDIATE l_pivot_qry;RETURN l_pivot_qry;END;

It works perfect when I hard code value of project code in the bold part of query however when I try to make the it dynamic by passing a item it is not showing any pivot columns.Request you to please suggest on this.Any suggestions would be really appreciated.

There are a few issues here:

• The page item reference does not work because item default values are not stored in persistent session state during page rendering. Use a computation to explicitly set the initial value before the report is rendered.
• To support partial page refresh when the project code is changed at runtime, the parameter item name must be included in the Page Items to Submit report region source property.
• To support a dynamic number of report columns, the Use Generic Column Names report region property should be set to Yes, with a suitable value specified for the Generic Column Count attribute.
• Do not reference page item values as literals in the report source (line 22 above). This exposes both a SQL injection vulnerability and potential performance problems due to increased hard parsing and flooding the shared pool. Always access page item values using bind variable references.
• Due to premature type conversion (line 8 above), the pivot columns are ordered as character strings rather than dates (line 5). This results in the columns being in the wrong order (APR-2018 is followed by APR-2019 instead of MAY-2018). Always treat dates as dates, numbers as numbers, and strings as strings, only performing conversions when it is really necessary to do so, preferably only in the last stage of processing.
• Providing explicit column aliases on the pivot columns recommended.
• Always generate dynamic SQL using a query template, the alternative quoting mechanism, and substitution rather than concatenation. This is far clearer and much easier to test and maintain.

declare  l_pivot_cols varchar2(4000);  l_pivot_qry  varchar2(4000) := q'{select    *from    (select         sum(dc_hours) dc_hours      , dc_activity      , to_char(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MON-YYYY') mon_year     from        dc_cats_data_2019    where        dc_network = :p586_project_code     group by        to_char(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MON-YYYY')      , dc_activity)pivot (    sum(dc_hours) for mon_year in (%s))}';begin  select      listagg(apex_string.format('''%s'' "%s"', to_char(month_year, 'MON-YYYY'), to_char(month_year, 'MON-YYYY')), ', ') within group (order by month_year)  into      l_pivot_cols  from      (select          trunc(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MM') month_year      from          dc_cats_data_2019      where          dc_network= :p586_project_code      group by          trunc(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MM'));  return apex_string.format(l_pivot_qry, l_pivot_cols);end;

See example on page 586 of your demo application.

fac586

Udit Nagpal wrote:I have created a PLSQL function returning classic report which is a pivot report using below query:

DECLAREl_pivot_cols VARCHAR2(4000);l_pivot_qry VARCHAR2(4000);BEGINSELECT ''''||listagg(MONTH_YEAR, ''',''') within group(order by MONTH_YEAR)||''''INTO l_pivot_cols from(SELECT TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY') MONTH_YEARFROM dc_cats_data_2019WHERE DC_NETWORK= 'BC000248P01E'--:P8_PROJECT_CODEGROUP BY TO_CHAR(TO_DATE(DC_DATE, 'DD-MM-YYYY HH24:MI:SS'),'MON-YYYY'));l_pivot_qry :='select * from ( '|| 'selectSUM(DC_HOURS) DC_HOURS,DC_ACTIVITY,TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY'') MON_YEARFROM dc_cats_data_2019'||' WHERE DC_NETWORK ='''|| :P8_PROJECT_CODE|| ''''||'GROUP BY TO_CHAR(TO_DATE(DC_DATE,''DD-MM-YYYY HH24:MI:SS''),''MON-YYYY''),DC_ACTIVITY'|| ') '|| 'pivot ( SUM(nvl(DC_HOURS,0)) for MON_YEAR in ('||l_pivot_cols||') )' ;--EXECUTE IMMEDIATE l_pivot_qry;RETURN l_pivot_qry;END;

It works perfect when I hard code value of project code in the bold part of query however when I try to make the it dynamic by passing a item it is not showing any pivot columns.Request you to please suggest on this.Any suggestions would be really appreciated.

There are a few issues here:

• The page item reference does not work because item default values are not stored in persistent session state during page rendering. Use a computation to explicitly set the initial value before the report is rendered.
• To support partial page refresh when the project code is changed at runtime, the parameter item name must be included in the Page Items to Submit report region source property.
• To support a dynamic number of report columns, the Use Generic Column Names report region property should be set to Yes, with a suitable value specified for the Generic Column Count attribute.
• Do not reference page item values as literals in the report source (line 22 above). This exposes both a SQL injection vulnerability and potential performance problems due to increased hard parsing and flooding the shared pool. Always access page item values using bind variable references.
• Due to premature type conversion (line 8 above), the pivot columns are ordered as character strings rather than dates (line 5). This results in the columns being in the wrong order (APR-2018 is followed by APR-2019 instead of MAY-2018). Always treat dates as dates, numbers as numbers, and strings as strings, only performing conversions when it is really necessary to do so, preferably only in the last stage of processing.
• Providing explicit column aliases on the pivot columns recommended.
• Always generate dynamic SQL using a query template, the alternative quoting mechanism, and substitution rather than concatenation. This is far clearer and much easier to test and maintain.

declare  l_pivot_cols varchar2(4000);  l_pivot_qry  varchar2(4000) := q'{select    *from    (select         sum(dc_hours) dc_hours      , dc_activity      , to_char(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MON-YYYY') mon_year     from        dc_cats_data_2019    where        dc_network = :p586_project_code     group by        to_char(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MON-YYYY')      , dc_activity)pivot (    sum(dc_hours) for mon_year in (%s))}';begin  select      listagg(apex_string.format('''%s'' "%s"', to_char(month_year, 'MON-YYYY'), to_char(month_year, 'MON-YYYY')), ', ') within group (order by month_year)  into      l_pivot_cols  from      (select          trunc(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MM') month_year      from          dc_cats_data_2019      where          dc_network= :p586_project_code      group by          trunc(to_date(dc_date, 'DD-MM-YYYY HH24:MI:SS'), 'MM'));  return apex_string.format(l_pivot_qry, l_pivot_cols);end;

See example on page 586 of your demo application.

Udit Nagpal

Hi,

Thank you so much @fac586 for explaining it so briefly and beautifully. You have cleared my doubts and concept and it works perfectly now.

Surely I'll remember this concept in future.

Thank you so much for guiding me. It works like a charm. Cheers.

Best Regards.