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