Getting the user’s current Interactive Report WHERE

Update: April 16, 2009 – I found and fixed a bug in how it handled the contains clause in the filters.  Stupid me, I’d used contains in other flavors of SQL so never bothered to confirm that Oracle SQL supports it!

Sorry about that!!!

The purpose of this package is to grab the user’s current Filters and Searches and recreate them in a WHERE clause, so I could do some external reporting.  A couple other people have shown me their solutions to this but this is my original work (with a hint from Anton and Shunt).

The little bit extra mine adds that I haven’t seen elsewhere is that it supports User-Saved Reports.  So if a user clicks the report tab for one of their saved reports and possibly customizes it, my routine can figure that out and recreate their current query.

It also supports the user typing a value into the Search box, which Apex matches against all text and numeric fields currently displayed.  That was an amusing trick!  If you care, you can view the query that IR generates by using Debug mode.

What this code does not support is a sort order, since my external report tool has a fixed sort.  Shunt did something that works with sort orders that you should check out here:

http://simonhunt.blogspot.com/2009/02/next-and-previous-from-interactive.html

If you compare how others handled the various filter options (IN, CONTAINS, etc) versus how I did it, I think you’ll prefer their method.  I know I did, and wished I could have figured it out when I was writing this!  :-/

From the IR report page (e.g. 2), you add the following Javascript to the Page HTML Header:

<script language="JavaScript" type="text/javascript">
function SaveAndRunReport() {
     popUp2('f?p=&APP_ID.:7:&SESSION.:new_request:NO::'+
            'P7_IR_REPORT_ID:'+$v('apexir_report_id'), 1024, 768);
}
</script>

You can certainly make a simpler call, mine just happens to use a pop-up page.

In the target page,  (e.g. 7), you can display the WHERE clause by creating an item computed with the following SQL query:

SELECT apex_ir_query.ir_query_where(:APP_ID,
           2,
           :SESSION,
            7_IR_REPORT_ID) FROM DUAL;

Here’s the package.  It’s hosted on MediaFire, so you’ll have to live with the ads.

Let me know if you find it useful or have suggestions to offer.

- Stew