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

Thursday, 10 July 2008

New job...

Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.

Anthony.