Get Filtered Columns and Values in APEX Interactive Report
In order to get the filtered columns and their corresponding values in APEX Interactive Report, we can use this code. 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: 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.
Read More