Dynamic Region Source for Interactive Reports
Before Interactive Reports existed I used to use classic reports based on PL/SQL source to allow for a dynamic report source. I now have an application where I want users to be able to select the source data of the report, but still have all the functionality of the Interactive Report.
Luckily, in my case the data sources that the end user can choose from for each report all have the same structure. Each report is based on a single view and the alternative data sources are simply snapshots of the data taken at different points in time – right now, start of the day, start of the week, start of the month etc.. All the column names and data types are the same no matter which data source is chosen. This means that the report definition does not change, and the region source can stay the same – so I can use an interactive report region.
On each APEX interactive report page I include a select list page item (Px_DATASOURCE) so that the end user can make their choice of data source.
The value of Px_DATASOURCE is used in the region source along with a pipelined function so that the chosen data is shown in the report.
For example, say page 222 has an interactive report region based on a view VW_THIS_REPORT. There are also 2 snapshots available for this view – a daily snapshot DY_THIS_REPORT and a monthly snapshot ME_THIS_REPORT. The report source is simply:
select a.* from table(PF_THIS_REPORT(:P222_DATASOURCE)) a
The pipelined function looks something like this :
FUNCTION PF_THIS_REPORT (p_source_type varchar2) -- value will come from select list item RETURN TT_THIS_REPORT pipelined IS CURSOR monthly IS SELECT * from ME_THIS_REPORT; -- monthly snapshot CURSOR daily IS SELECT * from DY_THIS_REPORT; -- daily snapshot CURSOR live IS SELECT * from VW_THIS_REPORT; -- view BEGIN case p_source_type when 'MONTHLY' then FOR rec IN monthly LOOP pipe row ( TR_THIS_REPORT <<record_column_list>> ) ); END LOOP; when 'DAILY' then FOR rec IN snapshot LOOP pipe row ( TR_THIS_REPORT <<record_column_list>> ) ); END LOOP; when 'LIVE' then FOR rec IN snapshot LOOP pipe row ( TR_THIS_REPORT <<record_column_list>> ) ); END LOOP; else null; end case; RETURN;
In the above example <<record_column_list>> is a comma separated list of columns. As this report is part of my automatically generated application, the pipelined function is also automatically generated from metadata when the page is generated. I can therefore generate the <<record_column_list>> from user_tab_columns for the relevant view (VW_THIS_REPORT) when the function is created.
TR_THIS_REPORT is the type object for the row and TT_THIS_REPORT is the type object for the table of rows. These types are also generated automatically when the report page is generated.