Overview
This article contains sample SQL syntax for the most common filter types.
In the following samples:
The
field_nameis the field that you want to filter by.The
param_keyis 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:
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