Forum Stats

  • 3,770,575 Users
  • 2,253,135 Discussions
  • 7,875,498 Comments

Discussions

Apex IR "Maximum Rows to Process"

JessicaWilson
JessicaWilson Member Posts: 989 Bronze Trophy
edited Oct 27, 2021 11:52AM in APEX Discussions

Working in apex 20.1 - I have an IR built on a table that has 6 million records in it. Obviously a performance hit to pull all 6 million records. I tried using the "Maximum Rows to Process" setting and it definitely helped the performance, but the issue is that there are times when the users will need more records than what I set as the max (100,000).

Is there any way to make this setting dynamic? I'd love to add a button that removes the limit and adds a warning that they will have to wait for the results. Just not sure what my options are - I did find a hack where someone updated the back-end table with a page item variable but I'm not quite ready to go to that level of ugliness.

Answers

  • jariola
    jariola Member Posts: 10,460 Silver Crown
    edited Oct 27, 2021 7:50AM

    Why user needs 100,000 or more records?

    Quite many cases it's not actual user who need those record. Data is needed e.g. for download and then uploading to other system. And for that alternative options might be considered.

  • msammour
    msammour Member Posts: 60 Bronze Badge

    You can try this:

    1- Add select list [P1_NROWS] (or whatever you want) that holds number of rows (100000, 400000, .. etc).

    2- In your IR -> Attributes -> Maximum Rows Per Page -> &P1_NROWS.

    3- Add DA for P1_NROWS to submit the page.

    4- Add a process to work only when that DA fired, and should have this code (which will reset the IR):

    DECLARE
     L_REGION_ID APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
    BEGIN
     SELECT REGION_ID
     INTO   L_REGION_ID
     FROM   APEX_APPLICATION_PAGE_REGIONS
     WHERE  APPLICATION_ID = :APP_ID
     AND    PAGE_ID = :APP_PAGE_ID
     AND    STATIC_ID = 'ir-static-id' /*update this to your IR static id*/
     ;
     APEX_IR.RESET_REPORT(P_PAGE_ID => :APP_PAGE_ID,
                          P_REGION_ID => L_REGION_ID,
                          P_REPORT_ID => NULL);
    END;
    

    Please note that if you have multiple IRs you should specify the P_REPORT_ID (you can get it from APEX_APPLICATION_PAGE_IR_RPT), otherwise it will resets the last viewed report.

  • JessicaWilson
    JessicaWilson Member Posts: 989 Bronze Trophy

    Unfortunately, maximum rows per page will not accept characters so you cannot put &P1_NROWS. into the setting.

    And I agree, that they are using it for download, but it's not a download to another system. They just want to bring the data into excel to do further analysis.

  • jariola
    jariola Member Posts: 10,460 Silver Crown

    And I agree, that they are using it for download, but it's not a download to another system. They just want to bring the data into excel to do further analysis.

    Does interactive report lack some features they need or is't just old habit use Excel? E.g do they combine some other data to downloaded report?

    If it's lacking feature from IR, have you considered can you build it using APEX other components?

    And sorry, I don't have answer to your original question.

  • msammour
    msammour Member Posts: 60 Bronze Badge

    [Maximum Rows to Process] will not accept characters, but [Maximum Rows Per Page] will. (Tried in 20.2 and 21.2)

  • JessicaWilson
    JessicaWilson Member Posts: 989 Bronze Trophy

    Sorry, corrected the title. It is maximum rows to process that I'm trying make dynamic.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,258 Employee

    Hi Jessica,

    what you can always do is ...

    • Clear out the declarative Maximum Rows to Process setting
    • Add the restriction to your SQL query
    select column1,
           column2,
           :
      from table
     where ...
       and ...
       and rownum <= nvl( :PX_MAX_ROWS, 100000 )
    

    But note (as others have mentioned before). It's typically not recommended to pull too many rows to the browser. First, all these rows must be downloaded, then the browser has to build a HTML page out of this and the end users must be able to actually consume all this information ...

    Best regards

    -Carsten

  • JessicaWilson
    JessicaWilson Member Posts: 989 Bronze Trophy
    edited Oct 27, 2021 4:05PM

    Carsten,

    Thanks for taking time to answer, but unfortunately, setting the restriction in the where clause can exclude data that matches the IR filters since it's applied before the filtering.

    And you don't get that nice message at the top indicating your data has been restricted.

    Jessica