Resetting pagination of a Master-Detail Report in Oracle APEX

After the “Oracle APEX round table talk with Mike Hichwa” I was asked an interesting question by another attending developer.

Is there a possibility to reset only the pagination of the detail report, when you have a master- and a detail report on the same page in an Oracle Application Express (APEX) application?

What is actually the problem with that? Why should the pagination of the detail report being reseted when a new master is selected?

Try out my example of a master-detail report and follow the steps in the instruction.

You will get an


“Invalid set of rows requested, the source data of the report has been modified.” error when you do that and the reason is that Oracle APEX is not able to show a second page if there are not enough records for that new master selection. Independent from that it’s always nicer, if the list starts from the beginning if a new master has been selected! :-)

I thought for sure it shouldn’t be to hard to reset just a particular region. The “Reset pagination” which you can specify for a link/branch isn’t really an option, because it resets pagination for all report regions – also for the master report, which isn’t so cool if you end up at the first record set, when you select a new master…

So what to do? The Oracle APEX OTN forum is always a good source for such a problem, after a quick search I found an old thread from Vikas asking the same question. But as it looks like, no solution was found. I had to come up with my own solution.

So I looked what Oracle APEX is doing to paginate to a new record set, examined how the URL looks like and came up with the following solution.

When synchronizing the detail report, we have to “simulate” a pagination of the detail report to the first record set. We can do that by sending the region id of the report as REQUEST value and by attaching &pg_min_row=1 to the end of the URL.

Try out the working example.

These are the steps to include it into your own application.

  1. Set the “Static ID” property for the detail report region to some unique value. (Skip that if you don’t have Oracle APEX 3.0 yet)
  2. Create a “Hidden” page item (eg. P3_REGION_ID) which is assigned to the region of the master report
  3. As “Source Type” set “SQL Query”
  4. As “Source value or expression” use the following query for Oracle APEX 3.0
    SELECT 'pg_R_'||TO_CHAR(REGION_ID)
      FROM APEX_APPLICATION_PAGE_REGIONS
     WHERE APPLICATION_ID = :APP_ID
       AND PAGE_ID        = :APP_PAGE_ID
       AND STATIC_ID      = 'Value which you have set for the detail report'

    or

    SELECT 'pg_R_'||TO_CHAR(REGION_ID)
      FROM APEX_APPLICATION_PAGE_REGIONS
     WHERE APPLICATION_ID = :APP_ID
       AND PAGE_ID        = :APP_PAGE_ID
       AND REGION_NAME    = 'Employees for department '||CHR(38)||'P3_DNAME.'

    for pre-Oracle APEX 3.0 installations. “Employees for …” has to be replaced by the exact name of you report region.

  5. In the master report, open the “Column Link” properties of you link column and insert &P3_REGION_ID. into the “Request” property.
  6. For the last “Value” assignment, attach &pg_min_row=1 at the end (eg. #DNAME#&pg_min_row=1).

Why are we not just hard coding the pg_R_6721068803959845958 into the “Request” property? The reason is that this number can/will change if you have to import your application, that’s why it’s better so select the current ID from the Oracle APEX repository.

That’s it! Have fun building master-detail reports!

14 thoughts on “Resetting pagination of a Master-Detail Report in Oracle APEX

  1. Patrik,

    What a great idea. I tried to do it with iframe but of course your suggestion is much better.
    The only problem is what if in the next version paging will be implemented differently?
    I think that ability to reset pagination on one of the regions could be a very good enhancement.

    Lev

  2. Hi Patrick,

    What must I do if I do not have a master report but rather a Select List populated by an LOV with a Go button and a where condition on the “Detail” report at the bottom which is using the Select List item?

    I have a user that is complaining of the “Reset Pagination” issue.

    Thanks,
    Samantha

  3. Hi Samantha,

    do you have a “Reset Pagination” process on your page which fires when the Go button has been pressed?

    I think that should solve your problem.

    Regards
    Patrick

  4. Patrick, in APEX how do you set the value of an item within a Page without Repagination? Searched and tried many examples. However I am still unable to achieve this.
    I want to set the values of a couple of items on a Page, based on what the user selects for another item on the same page.

    Can this be done within the Page’s HTML Header section? Do I need to create an “On Demand” Application Process?? Please advise. Thanks for your time.

    Russell

  5. Hi Russell, what do you exactly mean with repagination? What happens if you have a link which sets a page item? It will just redraw the page, but the report should display the same data as long as you don’t set the “reset pagination”.

    Patrick

  6. Patrick,
    I am pretty new to Apex.
    Can we have region inside another region? Also, I am having trouble show/hide field based on another field (selection list). I set the selection list to “Select List With Redirect”. And it works fine for that item. Since I need to do the same for another for the same region, I can’t as it actually reset the first selection list as well and I lost the data that I entered. Any advise? Thanks.
    Hrudhayah

  7. Hi Hrudhayah,

    there is no “region inside another region” available. About your second question, have you already tried “Select List with Submit”? That should save the value.

    Regards
    Patrick

  8. Thanks Patrick. I tried but it even switch the page to different page :(. Any advise on what else I showld be looking into.

  9. Patrick
    I have couple of LOV s on my report. I select a value from each of these LOV s and execute the query. But these LOV s either reset to a default value or deselect the value before those values can be passed to query as parameters. Eventually either I don’t have any output “NO Data Found” or
    wrong set displayed.
    Now and then starts behaving normal on its own. This is greatest annoyance as an Apex developer.
    How to prvent these LOV s not to discard my selections until the query is executed and a new value is selected.

    Your reply is highly appreciated.
    Many thanks.
    Rao

  10. Hey Patrick,

    Is there any APEX API (which can be used in PL/SQL) with which we can “reset Pagination” for a report?

    Regards,
    Hari

  11. Hi Patrick,

    I know this post is quite old, but it’s the best example I’ve found for what I’m trying to do. Could you provide any more instruction (or a link to instructions) for how to create a master-detail form like this on a single page? The information on how to force repagination is great, I’m just looking for the steps previous to this!

    Thanks!

  12. Hi Patrick,

    I think I have another kind of pagination issue.
    I have a page with a report region with customers.
    Above this region I have text-box to search for a specific customer name.
    I use pagination 1-15 etc.
    When I am on the second set of rows (viewing the records from 16 to 22) and I enter a search criterium to search a customer, then the pagination error occurs. How can this be solved ??

  13. Hi richard,
    I had the same issue. Solution: add (or edit) your branch and tick ‘reset pagination for this page’.

    branch point: On submit (after processing (after computation, validation and processing)
    page: /NUMBER of the same page, where you are/

Comments are closed.