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:
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:
AND CASE WHEN '%%param_name%%' <> ''
THEN field_name = '%%param_name%%' ELSE false END
Date-range picker
This filter type allows you to filter your data by a time period.
EXAMPLE: Use the following syntax:
AND 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 in (SELECT(UNNEST(string_to_array('%%param_name%%', ',')))))
ELSE true
END