get along with oracle and apex

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.