One of the tips I got out of the Expert Oracle Application Express book (Kindle version available here) is that you can build a link to an interactive report which automatically applies a filter. The book suggests this for drilling down from one interactive report to another; it can also be used to force a user to select a value for a primary key column–this can be a major performance booster, especially for interactive reports based on views. You can do the same thing by referencing a page item in your where clause, but then the user doesn’t have visibility to the fact that their data has been filtered. Depends on what you want. But one thing the chapter doesn’t mention (or, if it does, I missed it) is how to apply a full-row filter in a link, as if the user had typed something into the search bar. Google failed me, so I ended up having to go back to the source (thanks, Sharon!)
To apply a filter to a column, just add “IR_” before the column name, and use that as the name of the variable you’re setting in the link. You can optionally include an operator, allowing you to do other types of comparisons; here’s the list:
- EQ – equals (this is the default, so you don’t really need to use it)
- NEQ – not equals
- LT – less than
- LTE – less than or equal to
- GT – greater than
- GTE – greater than or equal to
- LIKE – the normal SQL like operator
- N – is null
- NN – is not null
- C – contains
- NC – does not contain
- IN – the SQL ‘in’ operator (note: to use commas in your value, you must put a backslash before and after it: \1,2,3\)
- NIN – the SQL ‘not in’ operator–see the above note.
So, for example, if you want to link to an interactive report and filter it down to records where the column “DUMMY” has a value less than 10, you’d use “IRLT_DUMMY” as the name and 10 as the value in your link.
If you want to apply a full-row filter, use IR_ROWFILTER for the name; this will return only rows that have the value you pass somewhere in the result set (searching across all columns). This is useful, for instance, if you’re building a search page and want the results displayed as an interactive report with the search term still visible.
Oh, and you may (er, probably will) want to clear other filters that the user may have applied to the interactive report and reset its pagination; to do this, add “CIR,RIR” to the clear cache parameter of the link. CIR clears the other filters, which RIR resets the pagination. Very useful tools.
Like I said, most of this is covered in the book; if you don’t already have a copy, I highly recommend going and getting one. All of the profits from it go to a very good cause. And, of course, it’s packed with useful information.