MattsBits
MattsBits

MattsBits

Set And Reset Interactive Report Filters In PLSQL  

by Matt Hawkins, 24/09/2009
Categories : APEX

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);

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017