This discussion is archived
8 Replies Latest reply: Aug 2, 2012 4:40 AM by Gus C RSS

Number of Rows Returned For A Report

Gus C Journeyer
Currently Being Moderated
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.

Cheers

Gus

Edited by: Gus C on Aug 2, 2012 12:31 AM
  • 1. Re: Number of Rows Returned For A Report
    xarg-Xrc Explorer
    Currently Being Moderated
    I know there is #MAX_ROW_COUNT#...Try with #ROW_COUNT#
  • 2. Re: Number of Rows Returned For A Report
    fac586 Guru
    Currently Being Moderated
    Gus C wrote:
    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.
    Think <tt>#TOTAL_ROWS#</tt> is it. You could include this in some JavaScript in the report footer that sets the page item value. (I'm wondering about using "+a+ page item" for this: what if you have to perform loggin for multiple reports on a page?
  • 3. Re: Number of Rows Returned For A Report
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Gus,

    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:
        #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"
    It didn't work properly on the interactive report (at least version 4.0.2), the total number of rows was wrong.

    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?

    Cheers,
    ~Dietmar.
  • 4. Re: Number of Rows Returned For A Report
    Gus C Journeyer
    Currently Being Moderated
    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

    Gus
  • 5. Re: Number of Rows Returned For A Report
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Gus,

    you didn't tell us about your purpose.

    You could do it for security auditing, performance optimization, general usage of the system, .... why are you doing it?

    Cheers,
    ~Dietmar.
  • 6. Re: Number of Rows Returned For A Report
    Gus C Journeyer
    Currently Being Moderated
    My bosses want to by able to follow what people are doing inside the application.
    So, if there is a problem they want to be able to follow the users exact actions
    For example
    User 1 logged on
    Went to page 2
    Entered certain parameters
    Clicked Run Report
    The report returned 15 rows
    etc, etc

    Gus
  • 7. Re: Number of Rows Returned For A Report
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Gus,

    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,
    ~Dietmar.
  • 8. Re: Number of Rows Returned For A Report
    Gus C Journeyer
    Currently Being Moderated
    Thank you very much for your input.
    I think I will ignore the rows returned.

    Gus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points