Set Interactive Report Filter with URL Parameters

Interactive Reports are very powerful, the end user can set filters, highlighting, add computed columns, do control breaks or groupings and much more.

As a Developer you might sometimes have the requirement to redirect from one page to another which displays an interactive report, but this report should be filtered by whatever value is selected on the first page.

Every User knows how to set a filter and every Developer knows how to use Page Items in the Report WHERE Clause, but most Developers don’t know how to call a Page and set a Filter for an IR with URL Parameters.

The APEX Application Builder Users Guide has a section where these URL Parameters are described, but when i search for that i won’t come up most of the time. Hence this blogposting (things i blog about always come up in search…).

If anyone wants to look it up in the Documentation it is in Chapter 9, Section Reports, Paragraph “Linking to Interactive Reports”.

For all others, here is a short summary:

  1. Read the basic APEX URL Syntax to know whats what
  2. use RIR in the ClearCache Section to reset to the default Interactive Report (clear all filters, formats, …) on the target Page
  3. use CIR to clear all report settings
  4. set Filters in the ParamName/ParamValue Section, using IR<operator>_<column_name> as ParamName where operator can be EQ,LT,GT,LTE,GTE,NEQ,LIKE,N,NN,C,NC
  5. you can omit the operator if you want to use “equal” as operator
  6. to set a Rowfilter use IR_ROWFILTER as ParamName
  7. all these settings target the default public report on the target page. if you want to set filters for any other public report use IR_REPORT_<alias> as Request

If you want to change Interactive Report Filters in a PL/SQL Process use the Package APEX_UTIL, it contains functions like IR_RESET, IR_FILTER and some other IR_* utility functions.



  1. AEstrada on July 16, 2012

    Great posting.
    It’s quick summary to work with clearing/set values for IR options.

  2. marcel on November 6, 2013

    Hi,

    I am trying to remove the option for end user to remove the ” Interactive Report Filters”,
    I could do this using javascript, but only when the page is loaded, after the end user choose the filter ( I have 2 filters ), the option to remove the filter is back.

    I saw in your post a lot of options to use for Interactive Report Filters, anyone I can use to implement this?
    thank you

  3. Peter Raganitsch on November 6, 2013

    Hi Marcel,

    if I understand you right then you could run your javascript code with an dynamic action “After Refresh” on the Interactive Report. That should cover your usecases.

  4. marcel on November 7, 2013

    I am trying, but it is not working with After refresh in dinamic action,

    this is my javascript :

    $(“img”).each(function() {if($(this).attr(“alt”) == “Remover Filtro”){$(this).remove();}});

    can you take a look ?

    http://apex.oracle.com/pls/apex/f?p=65753:64

    user : dev
    pass : dev321!

  5. Peter Raganitsch on November 7, 2013

    Marcel,

    In general, please ask technical questions like this on the APEX Forum on OTN.

    Did you make sure your Dynamic Action is executed after refresh?
    When you switch on Debug-Mode you can see all DA executions in the Javascript-Console (ie. Firebug in Firefox).
    In your case the DA doesn’t run, because your Region-Template doesn’t show the Region-Id (add #REGION_ID# to your region template).
    Peter

  6. marcel on November 7, 2013

    Hi Peter
    Thank you for you help,

    I did a question in OTN, but did not received an correct answer yet,
    I am an Oracle DBA and starting with development using javascript and apex,

    I did not understand when you said :
    “because your Region-Template doesn’t show the Region-Id (add #REGION_ID# to your region template).”

    How ( or where ) can I add the #REGION_ID# ) ?
    thanks.

  7. Nivetha on March 25, 2016

    Hi,
    Is there any way to pass “IN” as my operator in ParamName section, since I need to pass multiple values for single column

  8. Peter Raganitsch on July 20, 2016

    Nivetha, have a look at the current documentation, there you’ll see that you can use IN: https://docs.oracle.com/cd/E59726_01/doc.50/e39147/bldapp_rpt003.htm#HTMDB30108

  9. Etay G on August 11, 2016

    Thank you peter, this what I have been looking for, here is my syntax (for primary (saved report)) :

    to add a filter named pillar which will be equal to “Database” (please note it’s case sensitive)
    to add a filter named team which will be equal to “GoldenGate” (please note it’s case sensitive)

    Pillar : https://amydomain/pls/apex/f?p=17400:LISTBYPILLAR::IR_206737416:::IR_PIL_NAME:Database
    Team : https://mydomain/pls/apex/f?p=17400:LISTBYTEAM::IR_206740988:::IR_TEAM_NAME:GoldenGate


0