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:
- Read the basic APEX URL Syntax to know whats what
- use RIR in the ClearCache Section to reset to the default Interactive Report (clear all filters, formats, …) on the target Page
- use CIR to clear all report settings
- 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
- you can omit the operator if you want to use “equal” as operator
- to set a Rowfilter use IR_ROWFILTER as ParamName
- 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.
Great posting.
It’s quick summary to work with clearing/set values for IR options.
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
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.
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!
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
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.
Hi,
Is there any way to pass “IN” as my operator in ParamName section, since I need to pass multiple values for single column
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
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
Pingback: Oracle APEX 系列文章10：Oracle APEX Evangelion（EVA 補完計劃） - 程序員的後花園
Hello,
Is it possible to use ROWFILTER like this:
col_a like ‘%my_search_string%’ OR col_b = my_search_id
To filter ether for name like .. or filter for specific id?