Interactive report Auto filter using APEX_IR package | ORACLE APEX (2\2)

APEX_IR

The APEX_IR package provides utilities you can use when programming in the Oracle Application Express environment related to interactive reports. You can use the APEX_IR package to get an interactive report runtime query, add filters, reset or clear report settings, delete saved reports and manage subscriptions.

To implement the APEX_IR package we need a Interactive report.

Interactive Report

Now create one page process to execute APEX_IR.

Page Process

Source code

DECLARE
   l_region_id   apex_application_page_regions.region_id%TYPE;
BEGIN
   SELECT aapr.region_id
     INTO l_region_id
     FROM apex_application_page_regions aapr
    WHERE     aapr.application_id = :app_id
          AND aapr.page_id = :app_page_id
          AND aapr.static_id = 'REPORT_1';
   apex_ir.reset_report (p_page_id     => 3,
                         p_region_id   => l_region_id,
                         p_report_id   => NULL);
   -- Filter on CUST_FIRST_NAME
   apex_ir.add_filter (p_page_id         => :app_page_id,
                       p_region_id       => l_region_id,
                       p_report_column   => 'CUST_FIRST_NAME',
                       p_filter_value    => 'Albert',
                       p_operator_abbr   => 'EQ',
                       p_report_id       => NULL);
   -- Filter on CREDIT_LIMIT
   apex_ir.add_filter (p_page_id         => :app_page_id,
                       p_region_id       => l_region_id,
                       p_report_column   => 'CREDIT_LIMIT',
                       p_filter_value    => '1000',
                       p_operator_abbr   => 'EQ',
                       p_report_id       => NULL);
END;

Background

There are couple of procedures listed down in APEX_IR package.

• RESET_REPORT : It helps to reset the report. And it can works with page submits. There are two ways of using it

• Using Report ID: As above in the Source code
• Using Report Alias

BEGIN   
    APEX_IR.RESET_REPORT(
        p_page_id      => <Page_ID>,
        p_region_id    => <Region_ID>,
        p_report_alias => <REPORT_ALIAS>);
END;

• ADD_FILTER : It helps to adding filters to Interactive report. And it can only works with page submits. There are two ways of using it.
• Using Report ID: As above Source code
• Using Report Alias

BEGIN
    APEX_IR.ADD_FILTER(
        p_page_id       => <Page_ID>,
        p_region_id     => <Region_ID>,
        p_report_column => <COLUMN_NAME,
        p_filter_value  => <COLUMN_NAME>,
        p_operator_abbr => <OPERATOR>,
        p_report_alias  => <REPORT_ALIAS>);
END;

Interactive Report filters using URLs  Interactive report Auto filter via URL | ORACLE APEX (1\2)