Getting the user’s current Interactive Report WHERE

February 19, 2009

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:

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() {
            'P7_IR_REPORT_ID:'+$v('apexir_report_id'), 1024, 768);

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,
           :P7_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


9 Responses to “Getting the user’s current Interactive Report WHERE”

  1. Greetings,

    That is a nice example, i have seen the post in oracle forum and came here to check it out.

    I intend to use the WHERE results do build a query with them, i’ll post my results in the forum when i get to it.

    More about me at:

  2. Janel Hansen Says:

    Hey Stew,
    I’ve been trying to d/l this now for over half hour and mediafire is still not loading…is it possible to get a copy emailed to me?

    Much appreciated!

  3. Shunt Says:

    Hi Stew, I have found a bit of time to have another look at work we did on getting the SQL from Interactive Reports. checkout the latest post on my blog and see what you think. I don’t know if you had noticed but the global search function does not search on Date columns; that took a while to work out;) Simon

  4. Hello,

    Very useful, thanks for sharing!

    best regards

  5. Johanna Says:

    I can’t make it work on firefox and google chrome, any suggestion?

  6. iulian Says:

    How can i print filtered interactive reports in apex?

  7. Alex Says:

    Very useful functionality, thank you very much for posting this.
    I think I found (minor) bug. When you filter from the actions-menu and use type ROW, the generated predicate doesn’t compile. Easy to remedy though,… Select these columns in the filters-query: condition_expr_type and condition_sql, and add a CASE when you construct the clause-variable:
    case filters.condition_expr_type
    when ‘ROW’
    clause := filters.condition_sql;

    end case;

  8. mihael Says:

    nice I will try next week

  9. Vjekoslav Says:

    How can I get the where clause for an Interactiv Grid under Apex 5

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: