With that we can actually build really nice plugins enhancing the functionality.
However one thing you have to be aware of is that it will always give you the query of the "Report" view, so if you want to get the query behind the "Group By" view it's back to rebuilding the query manually.
Talking to Patrick Wolf during DOAG 2014 he asked me to write a blog post about it and showcase the behavior.
Therefore I have created a small demo on apexea.oracle.com using an interactive report and a dynamic action to see if the same behavior is also still in APEX 5.
The dynamic action gets the IR query using APEX_IR.GET_REPORT and also sets a read-only field to SYSDATE in order to show it actually fires on event "After Refresh".
You can check out the current behavior here.
So here's my wishlist for the APEX development team:
- Please give us the query also for the other view-types available for an interactive report.
- And if you want to supercharge the function give us an additional parameter for choosing whether we want the query for the currently displayed view-type or a specific one.
I imagine a possible function declaration like below:
FUNCTION get_report( p_page_id IN NUMBER
, p_region_id IN NUMBER
, p_report_id IN NUMBER DEFAULT NULL
, p_view_mode_in IN VARCHAR2 DEFAULT NULL
)
RETURN t_report;
So if you do not specify the fourth parameter you get the query for currently displayed view mode, similar to the third already available parameter.
I have tried using the Oracle provided apex_ir.Get_report function, but due to the issues I have documented earlier (aggregates are built into new columns in the report), I am currently using Tom Petrus' apex_ir_pkg (download link: https://github.com/tompetrus/oracle-apex-ir-pkg.).
ReplyDeleteIt would be nice if Oracle could fix this function to actually return the accurate query that the user ran, versus dorking it up with new columns.....
Thank you,
Tony Miller
LuvMuffin Software
Ruckersville, VA
Regarding the aggregates becoming additional columns that's natural if the user has aggregates defined on his report. For me it's actually the way you calculate aggregate values in Oracle when not using GROUP BY.
Deleteif you are talking about the COUNT(*) OVER () column that is added there is also a way to remove that one. Peter Raganitsch explained that one last week during DOAG 2014.
If you delete what's in "Maximum Row Count" in the interactive report settings the count(*) goes away.
however the trade-off is that there is no record limit on the IR anymore.
I think we are looking at this from two directions.. Your direction is to represent the report EXACTLY as it's presented on the screen, while the avenue I have to go is a download of data from the report to an XLSX file so that other systems can process that data or even a operator who wants to work with the data outside the application..
DeleteIn my case the aggregates should be removed, since they are NOT needed in a data export..
Thank you,
Tony Miller
LuvMuffin Software
Ruckersville, VA
What's the correct way to get the count(*) of the interactive report at the current page. Is it necessary to get the report with get_report and and then the count or is there a other way to do this.
ReplyDeleteWhich is the best practive to geht the count (Z) of X of Y from Z ..
BR Florens
If you are using the standard navigation provided by APEX you just need to change a setting.
DeleteUnder Report Attributes -> Pagination set Pagination type to "Row Ranges X to Y of Z".
However I don't know how this is implemented.
If you want to get total count manually you could retrieve the IR query and do "SELECT COUNT(*) FROM ($ir_query)".
Be warned it will have some performance impact.