Lessons in Filters and the ‘is prompted’ operator

It has been a long time, couple of months, since I last posted.  But busy beaver that I am, took me away from blogging.  Anyways, I got a breather and will add-on.

Filters and the ‘is prompted’ operator.  Many might think they know how this works and get shocked when things just don’t quite work out the way they do.  For instance lets say for some reason, you created an answers request which has ‘QTR’ filtered for a static value, lets say ’2007 Q4′.  This report then is pretty static right. It is a report for only that given quarter.  Add this to a dashboard page which has a prompt set for same ‘qtr’ field.  And play around with selecting values and submitting it.  What, the report changes!!! “How can this be, I did not set that field to ‘is prompted’ operator!”  As you scratch your head and ponder what has just happened.  Well it appears that adding static filters to a report and adding to a dashboard with same field filtered does the following.  If no values are selected, the report will always be for ’2007 Q4′.  But when the user selects different quarters, the static (default) value will be overwritten with the users selections.  So the prompted values over rule any static filters added.  Unless of course, how Nag commented below and reminded me,  the criteria filter protection option allows the user/developer to protect the filter from this overwriting rule from dash prompts.

Now lets say we add two filters to same said report for same field ‘qtr’.  One is for static value ’2007 Q4′ and the other is for ‘is prompted’.  You might think that the report will now correct itself, that is in your mind, and filter by the static value ‘AND’ the selected prompted values.  WRONG!  The prompted values will now be in both filter criteria as shown below with the filter view.

Changing the filter criteria logic to ‘OR’ does not help either.  As seen here:

Now lets see if setting the filter criteria to a variable, lets say a repository variable.  What happens now?  Same thing!  The filter’s bound values will be overwritten by selected value(s) from the prompt.

Now how to get around this.  Well, prompts replace the bound values by the fields they are representing.  For our example qtr field.  We can fake this or alter the behavior either by changing the prompt expression or filter expression as easily as:

case when 1=1 then qtr end  = ’2007 Q4′ and keeping the other filter is prompted.  Since the expression for the filter is just not ‘qtr’ anymore it will not be overwritten.  As follows:

This was done using ‘AND’ logic.  We just change the logic to ‘OR’ and we will get the static value and the selected values.

So remember or think about this when you have a filter not working quite like you expect it to.  This will give you more options in your filtering and prompting.  But please keep in mind whatever you use in the filter, like the case statement I used as example goes to the DBMS in the predicate and if that field is indexed, the index will no longer be used for that predicate.