Sample filter syntax

Overview

This article contains sample SQL syntax for the most common filter types.

In the following samples:

• The field_name is the field that you want to filter by.

• The param_key is the key of the predefined filter.
  For more information about the key of a parameter, see Create insightboard filters.

• To get the value of the filter parameter, you use %%param_key%%

EXAMPLE: If you want to filter the OKRs by their owner, then the field_name is the owner, the param_key is the key of the owner filter, and %%param_value%% is the actual owner that is passed to the SQL query.

For more information, see:

• Filter the output of insights

• Example: Create an insight with a List filter

Free text field

This filter type creates an input field, where you can enter the filter criterion manually

EXAMPLE: Use the following syntax:

CASE WHEN '%%param_name%%' <> ''
    THEN field_name like '%%param_name%%'
    ELSE false 
END

NOTE: In this case, if nothing is selected, no values will be displayed. You can change this behavior by setting the condition after ELSE to true. Then all values will be displayed.

Date picker

This filter type allows you to filter your data by a specific date.

EXAMPLE: Use the following syntax:

CASE WHEN '%%param_name%%' <> ''
    THEN field_name < TO_DATE(SUBSTRING('%%param_name%%',1,10), 'YYYY-MM-DD')
    ELSE false 
END

Note that this example does not account for timestamps

Date-range picker

This filter type allows you to filter your data by a time period.

EXAMPLE: Use the following syntax:

CASE WHEN '%%param_name%%' <> ''
    THEN field_name @@ '%%param_name%%' 
    ELSE false
END

List

This filter type allows you to filter by selecting one value from a predefined list.

EXAMPLE: Use the following syntax:

CASE WHEN '%%param_name%%' <> ''
    THEN field_name = '%%param_name%%'
    ELSE true 
END

Multi-select dropdown

This filter type allows you to filter by selecting multiple values from a predefined list.

EXAMPLE: Use the following syntax:

CASE WHEN '%%param_name%%' <> ''
    THEN field_name = ANY(string_to_array('%%param_name%%',','))
    ELSE true
END