Using A Case/When Clause With Default Filter Values

[intapiuser]

Sometimes when using direct replacement filters, you may want to filter or manipulate your data differently depending on the value or values supplied by the filter. 

Consider the following example:

select
  *
from
  my_table
where
  case
    when '[my_filter|Default]' = 'Default'
      then true
    else [my_column=my_filter]
  end

In this example, in the case when the user hasn't selected any filter values at all, the code '[my_filter|Default]' will evaluate to the string 'Default', and the boolean expression in the case/when will return true and then the where clause will also return true.

Else, the where clause will evaluate to true or false depending on whether the records in my_column match the values supplied in my_filter.

The concepts shown here can be used for much more powerful exercises, such as creating a custom date aggregation filter, as explained here.