Next and Previous from an Interactive Report


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.

Forum Post
String_agg function
SHUNTs solution

Comments

Stew said…
Very nice. If you'd done this a month ago you'd have saved me 2 days of work! :-)

I just finished something very similar to this, though my purpose was to be able to recreate the original query for a separate report procedure. I didn't care about the user's selected sort order, which is critical to what you're trying to do.

I really like your use of the lag and lead analytic functions for this. I haven't had cause to use them before and they make my head hurt just thinking about them. :-)

Thanks for the tip about some type of sort needed to make the order match with what the IR displays. That'd certainly be a crucial point to include!

The part that I managed so suss out which I don't see your implementation supporting is the free-text searches. That is, just typing in a few characters in the search box.

I also don't notice any support for User-Saved report tabs. Is that in here and I'm just being dense? I had to get a little help from a friend to work out the (simple it turns out) Javascript to identify the user's current report tab.

I could put this to use in my application, if you wouldn't mind? I'd be happy to share my implementation of getting the entire WHERE clause including the Searches.

Thanks,

Stew
shunt said…
Hi Stew, thanks for your comments. You are right about the free text search and the saved workbooks, I haven't catered for them. You are more than welcome to improve the Procedure and use it in your own system. Let me know how you get on.
Cheers
Shunt
Stew said…
Many thanks.

See you in the Apex User Forum!

- Stew
Steve said…
Simon,
I can't find the code in the link - just a user-run-time app - that works! Looking for your code because the next/prev is exactly what I am working on.

Thanks,
Steve
shunt said…
Hi Steve, checkout one of my newer posts on this subject at
http://simonhunt.blogspot.com/2009/12/next-and-prev-from-interactive-report-2.html
If you need any more expatiation then let me know. I've not used this in anger yet, so please keep me informed with how you get on. Cheers
Simon
shunt said…
I found a couple of bugs this weekend which I have fixed. The first was getting the row count correct when a sort column has values the same. you will see that the generated sql now includes a rownum at the end of each sort. The second change was to see if a filter was enabled or not, ie if the checkbox had been unchecked for a filter. If I get time I will add a header with a version number to the functions. Please let me know if you find any bugs or make any improvements. Simon
dare to win said…
Gud job Simon..

I had one confusion..
I want to create a report with a search enabled facility,but my search is only working for the first column when i enter some data in search box that is not present in 1st column but present in 2nd column..it is returning me 0 rows..I want to enable my search on all columns..

How i can achieve this?

Please help on this..