Wednesday, 16 July 2008

Dynamic Date Filtering in Interactive Reports

Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Introduction...
Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...


But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

How...
Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).


This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.



Method 2

It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual
2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless
3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#
Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:


Of particular interest here are...
  • Clear Cache = '2,CIR'
    This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

  • Item Names = 'IRLT_REVIEW_DATE'
    This can be broken down as follows:
    • 'IR': This is used to set interactive report filters dynamically over the URL.

    • 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.

    • 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.


This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.


Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
(case 
when review_date < sysdate then 'Yes'
else
'No'
end) review_overdue
Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:


This could then be saved by the user as a 'Named Report' for future use.


Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.


Conclusion...
So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).


A couple of questions for the community...
  • Would you find it useful to be able to filter by SYSDATE?

  • Would you find it useful to be able to filter not just by a value, but by another column value?

  • What else would you like to see incorporated into Interactive Reports to make them even better?


Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

6 comments:

Patrick Wolf said...

Hi Anthony,

the only drawback about solution 3 is that it doesn't use an index for the date unless you create a function based index which contains your case statement.

About your questions:

I think it would be useful to have a placeholder for SYSDATE, eg. $SYSDATE$ or $TODAY$ or something like that. And I think sometimes it could also be useful to compare against another column, eg. to find out where I have sold more than I have on stock, ...

Looks like you are going to work on IRR :-)
Patrick

Patrick Wolf said...

BTW, my manager just complained that she is not able to set a highlighting for a cell which is based on the value of another column. Eg. she has a hidden column where she does some calculations and based on that she wants to highlight the status column.

Patrick

Anthony Rayner said...

Patrick,

Thanks for the comments and feedback, I will feed this back.

I've updated the 'Conclusion' part of my post with the additional indexing consideration.

Anthony.

Gary Myers said...

As an addendum to Patrick's point, you also can't have a Function based index using SYSDATE.

Patrick Wolf said...

Hi Gary,

I don't think so and I also have to revert my previous posting about the function based index. Using SYSDATE in an function based index would be a non-deterministic result, that's why it will not work.

Patrick

Claude said...

Hi Guys
So is there any workaround concerning the SYSDATE in a Function (FC_1).
Because I have my query saying :
WHERE FC_1(id) < 10 and inside the FC_1 I have a check on the SYSDATE.

How to create the Function based Index on FC_1(id) in this case?

Thank you
Claude