Using A Case/When Clause With Default Filter Values

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.

Updated 02-21-2024

CDT