Hi all,
I have a multi-region APEX report page where each region is reporting/charting different parts of the same SQL query.
In my current configuration, each report/chart has it's own variant of the same SQL query.
Is there some way to have a master query for a page and then have each report/chart region just reference the result set from that master query?
Also, the query is a little complex (speed might be an issue later).
If using a master query is not possible or too cumbersome, how about using a page process to populate a local table with the results of the master query, then the page reports/charts can just reference the local table (instead of a giant query).
More specifics:
I'm running APEX 4.0 on an oracle 11g DB.
I have a DB link to an oracle 10g DB where most of the data actually resides.
My report page is a parametrized report: user picks a date range, a part#, and a machine#, then 4+ separate (but similar) queries run and the data is displayed in 4+ different regions.
The queries look functionally like this:
SELECT lot_id, part_id, machine_id, record_date, avgX, rangeX, mR_X, UCL1, TGT1, LCL1, ... FROM <...big messy multi-join> WHERE <parametrized where clause>;
1st chart uses: lot_id, avgX, TGT1, UCL1, LCL1
2nd chart uses: lot_id, mR_X, TGT2, UCL2, LCL2
3rd chart uses: lot_id, rangeX, TGT3, UCL3, LCL3
1st report uses: all columns except TGT#, UCL#, LCL#
2nd report uses: statistics on all columns in first report (avg, stddev, and various combinations of avg and stddev)
If I ever want to update this query, I have to go to 4+ different places in my apex page to update.
Is there a better way? either page-based master query idea? or page-process populates local table with master query results idea? or something else?
What are Apex "best practices" for this kind of scenario: an apex page with multiple regions all reporting/charting different parts of the same query.
Also, for reference, I am looking into using a page process to build the XML for my 3 different charts. I think the configuration will look like this:
1) page process uses PLSQL block to build the XML strings needed for all 3 charts.
2) page process saves the XML strings to separate hidden page items
3) charts use custom XML string -> replace #DATA# with a reference to the page item holding the appropriate XML string for that chart.
Thanks!