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.
- 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)
- Create a “Hidden” page item (eg. P3_REGION_ID) which is assigned to the region of the master report
- As “Source Type” set “SQL Query”
- 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'
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.
- In the master report, open the “Column Link” properties of you link column and insert &P3_REGION_ID. into the “Request” property.
- 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!