Oracle APEX Date Filters Applied with apex_util.ir_filter

Question

I am using a dynamic action in my Oracle APEX application to apply date filters to my interactive report. The dynamic action is triggered by a change occurring in either my "Date From" (P4027_DATE_FROM) or "Date To"(P4027_DATE_TO) date boxes. The dynamic action submits both page items and contains one PLSQL process and one javascript process.

The PLSQL code executes first. It uses the apex_util.ir_filter to apply a LTE filter to the "Date To" and a GTE filter to "Date From". In other words, I was trying to create my own "BETWEEN" function. I also included some logic in the PLSQL code to only execute one or the other if one date was blank.

This runs fine the first few times that I use these filters on a new report. However, if I use a filter that I applied in the past (using the same "Date From" for a second time), weird things start to happen. For example, changes to the "Date From" after that will add the new date to the filter list, but it will not check (apply) the filter. A change to "Date To" will check the appropriate filter but also check all previously used filters that use the >= operator on the date column.

This is the PLSQL code:

BEGIN
--clear out functions prior to execution
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'EQ',p_filter_value=>'');

IF (:P4027_DATE_FROM IS NOT NULL AND :P4027_DATE_TO IS NULL) THEN
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'GTE',p_filter_value=>:P4027_DATE_FROM);
ELSIF (:P4027_DATE_FROM IS NOT NULL AND :P4027_DATE_TO IS NOT NULL) THEN
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'GTE',p_filter_value=> :P4027_DATE_FROM);
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'LTE',p_filter_value=> :P4027_DATE_TO);
ELSIF (:P4027_DATE_FROM IS NULL AND :P4027_DATE_TO IS NOT NULL) THEN
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'LTE',p_filter_value=> :P4027_DATE_TO);
ELSE
apex_util.ir_filter(p_page_id=>4027,p_report_column=>'DATE_COL',p_operator_abbr=>'EQ',p_filter_value=>'');
END IF;

END;

The javascript process refreshes the page after the PLSQL process and seems to be working fine

Tom Tom · Accepted Answer · 2015-04-24T07:02:49

Have you tried to use apex_ir.add_filter instead? I'm quite sure apex_util.ir_filter delegates to apex_ir, but still.

Secondly I'm afraid there is next to nothing you can do. IRs have a terrible API which has been pretty non-existent so far. Ideally you'd be able to target certain filters etc, but alas. There is nothing you can do to steer behaviour. I'd only ever add a filter if I knew the report was cleared or reset, or during a new session.
So why not just apply the filter once, and then let users use the IR functionality to change the filter instead of rewriting said functionality?

If you really, really must have items to steer this, I'd simply advise to not use the built-in filters of IRs, because as you can see the behaviour is not what you want, and instead filter the results by adding the filter in your source query. Something like:

AND (:P4027_DATE_TO IS NOT NULL AND from_dt >= :P4027_DATE_TO)
AND (:P4027_DATE_FROM IS NOT NULL AND to_dt <= :P4027_DATE_FROM)

Oracle APEX Date Filters Applied with apex_util.ir_filter

1 Answers