Classic report, dynamic display with PL / SQL content

Question

In APEX I created a page and here a region. The region reports a classic report. I have selected "PL/SQL function body that returns an SQL query" as the source. Here I have also deposited the following code

declare
    l_date_string varchar2(32000);
    l_date_diff number(4);
    x NUMBER := 0;
    l_script varchar2(32000);
    l_script_pivot varchar2(32000);
    new_date varchar(256);
begin
   
   l_date_diff:=TO_DATE(:P2066_DATE_UNTIL, 'dd.mm.yyyy') - TO_DATE(:P2066_DATE_FROM, 'dd.mm.yyyy') ;
   
    While X < l_date_diff+1 Loop
       new_date := to_char(TO_DATE(:P2066_DATE_FROM, 'dd.mm.yyyy')+X,'dd.mm.yyyy');
       l_date_string := l_date_string || ',''' || new_date || '''';
        X := X + 1;
   End Loop;
   
   l_date_string := substr(l_date_string,2);

   
   l_script := 'Select * from
                    (Select 
                        pkey, 
                        to_char(createdformat,''dd.mm.yyyy'') business_date, 
                        regexp_substr(statistics, ''business_\w*'') business_statistics 
                    from 
                        gss.business_data 
                    where 
                        statistics like ''%business_%'' 
                        and createdformat between :P2066_DATE_FROM and :P2066_DATE_UNTIL
                     ) ';
   
   
   l_script_pivot := l_script || ' pivot(
                    count(pkey) 
                    for business_date 
                    in ('||l_date_string||'))';
   
   
    
    sys.htp.p('<li>' || l_script_pivot || ' </li>' );
    
   
    return l_script_pivot;
end;

The first column, Business_Statistis, is always displayed, the date in the subsequent columns should be displayed dynamically - depending on the selection of the period.

I also spent the respective code according to the time period selection and knew it successfully as a classic report with an SQL query. That's working.

How can I now dynamically update the classic report with a PL / SQL action. That means that depending on the result, the Classic Report is always displayed?

I once selected to use Generic Number of Columns in parallel, with a number of 365. Then he shows me the columns, but the column heading is not the date but Col2, Col3, Col4 and so on

Ben Wilton Ben Wilton · Accepted Answer · 2020-06-26T02:30:52

There are a few ways to solve this issue.

Option 1: If you're happy to use "Generic Number of Columns", then select PL/SQL as your "Headings Type". Then define your headings with PL/SQL e.g.

DECLARE
    v_heading varchar2(2000);
    l_date_diff number(4);
BEGIN
v_headings := 'business_statistics';

   l_date_diff:=TO_DATE(:P2066_DATE_UNTIL, 'dd.mm.yyyy') - TO_DATE(:P2066_DATE_FROM, 'dd.mm.yyyy') ;
   
    WHILE X < l_date_diff+1 LOOP
       new_date := to_char(TO_DATE(:P2066_DATE_FROM, 'dd.mm.yyyy')+X,'dd.mm.yyyy');
       v_headings :=  v_headings ||':' || new_date ;
        X := X + 1;
   END LOOP;
END;

Option 2: I assume you are using "PL/SQL function body that returns an SQL query" to get around the "in clause" of the pivot? Another way to fix this is to define your pivot clause as a page item. E.g. P2066_PIVOT_CLAUSE. You can then set the pivot clause with a before header process. Then you can use standard SQL in your report. i.e.

SELECT * FROM
(SELECT pkey, 
to_char(createdformat,'dd.mm.yyyy') business_date, 
regexp_substr(statistics, 'business_\w*') business_statistics 
FROM
gss.business_data 
WHERE
statistics like '%business_%'
AND createdformat BETWEEN :P2066_DATE_FROM AND :P2066_DATE_UNTIL) 
PIVOT (COUNT(pkey)FOR business_date in (&P2066_PIVOT_CLAUSE.))

Classic report, dynamic display with PL / SQL content

1 Answers