This content has been marked as final. Show 8 replies
I am using Apex 3.2
I need to get the number of rows returned for a report into a page item, so I can use it in my logging. It could be classic or interactive.
I don't really want to just do a select count(*) on my report.
I have seen #TOTAL_ROWS# used, but only in a report footer.
How do I get this in to a page item.
Or is there a built in substitution string like (APP_ID, APP_USER) that I don't know about.
yes, the total number of rows for a classic report can be displayed using the #TOTAL_ROWS# placeholder in the region footer: http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/ui_region.htm#HTMDB25572.
But it will only show you the correct result if you choose a pagination scheme that includes the total number of rows (e.g. Row Ranges X to Y of Z). Using other pagination schemes will not even retrieve the total number of rows correctly. And this makes perfect sense from a performance point of view. APEX uses very efficient sql statements to retrieve only the rows for the currently selected "page" in your pagination. Sometimes with large tables I don't even display the total number.
When I put that into the region footer I can get the information:
It didn't work properly on the interactive report (at least version 4.0.2), the total number of rows was wrong.
#ROWS_FETCHED# -- Shows the number of rows fetched by the reporting engine (the page size). Use these substitution strings to display customized messages to the user. #TOTAL_ROWS# -- Displays the total number of rows that satisfy a SQL query used for a report. For example, "X" in "rows X - Y of Z" #FIRST_ROW_FETCHED# -- The "X" in "rows X - Y of Z" #LAST_ROW_FETCHED# -- The "Y" in "rows X - Y of Z"
Another problem is also tricky. This information in the region footer is static and will not be updated when you use "enable partial page refresh" on a classic report. On an interactive report it will not work at all since it uses AJAX all the time to retrieve the rows. Thus if you filter dynamically in an interactive report, the number of rows retrieved (#TOTAL_ROWS#) will not change. It will only be display when the page is rendered initially.
Thus I would definitely recommend against that.
What are you trying to achieve anyway and for what purpose?
I am trying to write a logging process.
For example, when somebody runs a report, I need to place in the logging table
Application Id, Page No, Current User, Report Paramater Names, Report Parameter Values, Button Clicked and Total No of Rows Returned.
I can get most of it using substitiution strings, eg APP_ID, PAGE_ID, APP_USER etc
I don't know how to get the Total No of Rows Returned.
I know about copying the apex_workspace_activity_log over night, but I need this to happen staight away
in order to troubleshoot user problems my best bet has always been to "take a look" using Teamviewer or Microsoft Netmeeting. This tells you the full story, your users might go back using the browser back button, etc. The logs wouldn't tell you that.
I know you are running 3.2, but lets cover a few options starting with 4.0:
Starting with APEX 4.0 (I believe, for sure with APEX 4.1) you can attach yourself in the debugging to a remote user session (using the APEX Builder), then you can even see the timings and debug output of the user running the pages.
Starting with APEX 4.0 you can also enable user feedback. Here the user can give you feedback on pages or report bugs, it will even dump the session state of your application.
There are other options as well (in all versions of APEX):
*) I have seen people implementing a mechanism where a support user could switch the user identity to a different end user thus being able to run everything as the original user.
*) I have implemented myself a secure backdoor, where I could log in to the system without a password as an arbitrary user. This was possible after me authenticating against the APEX workspace at least as a developer successfully.
I understand your need to make your boss happy. But you cannot get this information easily or reliably. Also, the number of total rows is very often of no use. The more important number of rows is the one of the records displayed.
The bottom line ... I wouldn't bother to get that information. I am developing and supporting APEX applications for 6 years now and I never needed that information (number of rows retrieved).
Just my 2cents,