Interactivity & Parameters

Custom Filters

Custom filters are user-defined filters associated with configurable UI controls. A custom filter can be introduced in an ad-hoc manner by using a query parameter in the SQL editor with the general syntax

{<expr>=<filter_name>},

e.g.

{origin=origin_airports}

In this example "origin_airports" is a custom filter that applies to values of the column "origin" to any dashboard including a report with this filter in a query. A simple query may look as follows:

SELECT * FROM flights WHERE {origin=origin_airports}

The associated default UI control on a dashboard for a custom filter is an input field:

The input field accepts multiple (comma-separated) values representing alternatives. At this point, the filter is local to the dashboard and uses a default configuration. To make a new filter reusable and further configurable, it can be created as a filter for the organization. To that end, the name of a filter above the filter control (e.g. input field) is a link which leads to a dialog for either creating the filter (if it is a new) or for editing it (if it exists):

The following can be configured on a filter:

Name: A viewer-friendly name of the filter, displayed in the filter menu bar, e.g. "Origin Airports".

Control Type: The type of UI control to show in the filter menu bar of a dashboard, which determines how the input values are interpreted:

  • Text Input (multiple values, default): An input field for arbitrary text that interprets the entered text as multiple, comma-separated values.
  • Text Input: An input field to enter arbitrary text that interprets the value as a single string, e.g. for an experience resembling that of full-text searching.
  • Yes/No: A Yes/No/All control, i.e. a 3-state checkbox.
  • Drop-down: A drop-down menu offering a single selection from a predefined set of values obtained as a result of a custom SQL query.
  • Drop-down (multiple values): Like the drop-down control, but allows the user to select multiple values.
For text input controls, the text entered is either quoted in the actual SQL query to prevent SQL-injection, or the value is checked to be a number or a boolean value according to the column type.

For drop-down controls, a data source and SQL query must be configured, to specify the source of the values to display. The values are taken from the first column of the result set of the query, optionally with labels taken from the second column, if it exists. Any additional columns are ignored but may be used e.g. as a column to sort by. If the possible values are static, a query can be used that produces a static set of data in 1 or 2 columns. A typical query for the drop-down control of a filter may look as follows:

SELECT code FROM airports ORDER BY name;

Furthermore, the Refresh Values option for drop-down controls allows to schedule an automatic refresh of the values by running the query automatically. It can be set to Manual if the values never change, or if updates are preferred to be done manually. The following screenshot shows a sample configuration for a custom filter using a drop-down control:

custom-filter-drop-down.png

Finally, the Parent Filter option that is left unused in this example, allows creating chains of dependent filters with drop-down controls. See Parent-Child Filters for more details on how to use this option.

Executed SQL

By default, the resulting SQL of a custom filter matches a value expression (e.g. a column name) against the filter values selected in the UI, taking into account the number of selected values. For example, the query

SELECT * FROM flights WHERE {origin=origin_airports};

could result in any of the following SQL to be executed, depending on what is selected in the associated UI control on a dashboard:

SELECT * FROM flights WHERE 1=1; // no selection
SELECT * FROM flights WHERE origin='LAX'; // single value selected
SELECT * FROM flights WHERE origin IN ('LAX', 'SFO); // two values selected

 

Basic Parameters

A custom filter automatically comes with some associated basic parameters, whose availability depends on the type of the configured UI control:

  • {my_filter.min}: The minimum of the selected values or NULL if no value is selected.
  • {my_filter.max}: The maximum of the selected values or NULL if no value is selected.
  • {my_filter.count}: The total number of selected values.
  • {my_filter.selected}: Whether the filter has a value selected (TRUE or FALSE).
  • {my_filter.value}: The singular selected value or NULL in case no value or more than one value is selected. This is useful e.g. when using the text entry of a filter in a LIKE condition.
  • {my_filter.values}: The list of selected values in parenthesis, e.g. '(1,2,3)'.  An empty list if no values are selected. This is useful e.g. for use with 'WHERE ... IN' clauses in a SQL query.
  • {my_filter.values_array}: The list of selected values in square brackets, e.g. '[1,2,3]'. An empty list if no values are selected. This is useful for databases that allow to work with array literals (e.g. Google BigQuery).
  • {my_filter.values_concat}: A string concatenation of all the selected values, separated by commas (','). The empty string in case no values are selected.
  • {my_filter.value_raw}: The selected value of the filter as is, e.g. to dynamically specify the column to choose in a SQL query.

An example of using such parameters to implement a simple search functionality may look like

SELECT * FROM my_table WHERE my_col LIKE '%' || {my_filter.value} || '%'

or even conditioned on the presence of a selected value, as in

SELECT * FROM my_table WHERE
  CASE WHEN {my_filter.selected}
       THEN my_col LIKE '%' || {my_filter.value} || '%'
       ELSE 1=1
  END

Note: Make sure to use the DB-specific String concatenation:

  • '%' || {my_filter.value} || '%' (Postgres, Redshift, Oracle)
  • CONCAT(string, string, string) (MySQL, MS SQL)
  • etc.

Related articles