Within the APEX APEX_UTIL package there are a couple of useful procedures for setting and resetting Interactive Report filters. This allows you to manipulate IR filters programmatically within PLSQL.

For some reason these packages are not included in the official APEX API documentation so I have included some details below :

The first procedure is ir_filter which lets you set filters on particular columns. You can even set multiple filters against the same database column.

apex_util.ir_filter(

    p_page_id in number,                      -- Page that contains an Interactive Report

    p_report_column in varchar2,              -- Name of the report SQL column to be filtered

    p_operator_abbr in varchar2 default null, -- Filter type

    p_filter_value in varchar2);              -- Value of filter, not used for N and NN

Valid options for the p_operator_abbr parameter are :

  - EQ = Equals

  - LT = Less than

  - LTE = Less then or equal to

  - GT = Greater Than

  - GTE = Greater than or equal to

  - LIKE = SQL Like operator

  - N = Null

  - NN = Not Null

Example :

apex_util.ir_filter(p_page_id=>1,

                    p_report_column=>'DEPTNO',

                    p_operator_abbr=>'EQ',

                    p_filter_value=>'10');

The next procedure is ir_reset. This resets the interactive report on a given page for the current user session and performs the same function as selecting Reset from the pull down menu.

Example :

apex_util.ir_reset(p_page_id => 1);

The last procedure is ir_clear. This clears the interactive report filter on a given page for the current user session.

apex_util.ir_clear(p_page_id => 1);