Interactive report Auto filter via URL ORACLE APEX Part 2

March 18, 2020Posted inAPEX

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. 

 Now create one page process to execute APEX_IR. 

Source code

Copy 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 below in the Source code
    • Using Report Alias
Copy Code
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 below in the Source code
    • Using Report Alias
Copy Code
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;
Tags: