The new feature of Interactive Reports (IR) in Apex provides the user with the ability to search, sort and filter reports easily, create their own versions of reports and save them for future use. One limitation with IRs is that I haven’t been able to find a satisfactory way of producing Next and Previous buttons on a custom details screen. The issue being how to best capture the Filters and Sorts that have been set in the IR. I have seen some very slick solutions using JS and AJAX techniques whereby the IDs of the records displayed in the IR are saved to an array and then referenced from the detail screen. Ajax is then used to re-query the main IR to get the next pagination set if required. The only issue I have with these techniques is that they are a bit involved to incorporate and require hidden items to be concatenated to existing fields. If those fields are then removed from the report by the user then that breaks Next Previous function.
I have written a procedure that reconstructs the query being run in the IR from the APEX views and then uses Analytics to return the Next, Previous, First, Last and Count values. It works pretty well and should be quick and easy to implement. The only failing I have found is when no sort is applied to the IR and the base query has no order by; in this case the order of the next and previous can be different to the order on the IR.
The complete solution can be viewed from the link below, which includes a demonstration and a copy of the procedure. The procedure references the string_agg function, a must for any Apex developer. I have added a link to a Blog which offers a good explanation of the function and downloads of the code.
Implementation should be self explanatory, but drop me a line if you need anymore help. I do not profess to being a PLSQL expert so I would be very interested in any ideas of a better way to do it.