Introduction
The purpose of this post is to find the filtered columns and their corresponding values, conditions, operators are used for the condition in the Interactive Report.
Steps to Follow
So if the user selects multiple filters in IR and we need to retrieve the values of filter columns and their corresponding values, conditions, operators used for the conditions, etc. we can retrieve using the below code.
Query:
SELECT ircond.condition_name,
ircond.condition_type,
ircol.report_label,
ircond.condition_column_name,
ircond.condition_operator,
ircond.condition_expr_type,
ircond.condition_expression,
ircond.condition_expression2,
ircond.condition_sql,
ircond.condition_display,
ircond.condition_enabled
FROM apex_application_page_ir ir
JOIN
apex_application_page_ir_rpt irr
ON ir.interactive_report_id = irr.interactive_report_id
JOIN
apex_application_page_ir_cond ircond
ON irr.report_id = ircond.report_id
JOIN
apex_application_page_ir_col ircol
ON ir.interactive_report_id = ircol.interactive_report_id
AND ircond.condition_column_name = ircol.column_alias
WHERE ir.application_id = TO_NUMBER (:app_id)
AND ir.page_id = TO_NUMBER (:app_page_id)
AND ir.region_name = ‘Interactive Report’
AND ircond.condition_type = ‘Filter’
AND irr.session_id = :app_session
AND irr.base_report_id =
(SELECT apex_ir.get_last_viewed_report_id (
TO_NUMBER (:app_page_id),
ir.region_id
)
FROM DUAL);
Use a condition to identify the specific interactive report using either the region name or a static region ID.
Note: Can filter using Actions Menu and also using Header column filter.
Example:
The below filtered columns and corresponding values can be retrieved using the
above code.
Call To Action:
For Oracle apex development and customization please do visit our company website https://doyensys.com/
Conclusion
This method would be handy to find the filters which are applied in an interactive report.
