Custom filters are something that with a very little effort transform the dashboards into a powerful tool where anyone can narrow down the data to a specific subset, such as seeing a revenue for a specific business unit or narrow down details to one of several marketing channels.
It all starts by adding a very simple expression to a WHERE clause:
SELECT * from flights WHERE {origin=origin_airports}
By adding the {origin=origin_airports} expression to the query and saving the report, you just added a new Filter to the dashboard named 'origin_airports' which allows to filter down the results to a specific airport (or a set of airports).
By default, the filter on the dashboard displays an input field, where you can enter multiple (comma-separated) values.
Note that the name of the filter is a link, which allows you to create (or Edit) the filter to make it much more powerful.
On a filter, you can define several things that improve the user experience of using the filter:
Name - you can give a human name to the filter, to be displayed in the filter bar - so e.g. name it "Origin Airports"
Control type - this defines what kind of UI element is displayed in the filter bar and how the values are interpreted:
- Text input - displays an input field to enter arbitrary text, useful for experience resembling full-text searching.
- Text input (multiple values) - the default used if a filter is not defined, displays an input field and interprets the entered text as several comma-separated values
- Yes/No - displays a Yes/No/All control (like a 3-state checkbox)
- Dropdown - displays a dropdown offering to select from a pre-set set of values (result of SQL query entered below)
- Dropdown (multiple values) - like Dropdown, but allows the user to select more than 1 value
For text input filter types, the text entered is either quoted in the actual query run to prevent SQL-injection, or the value is checked to be number or boolean value if the column type is number/boolean.
Datasource and SQL Query - in case Dropdown-type control is used, this is the source of the values to display. This is a very powerful way to allow selecting from the actual possible values. The values are taken from the first column of the result, with optionally value labels taken from a second column. Any additional columns are ignored (but might be useful for instance if you need to have a column to sort by). If the possible values are always the same (and do not need to be fetched from the database), just enter a query that would produce the static set of data in 1 or 2 columns. Usually very simple queries like this:
SELECT DISTINCT code, name FROM airports ORDER BY name;
Refresh Values - this allows to set automatic refresh of the filter possible values by running the query automatically to always keep the possible values up-to-date. Set it to Manual if the values never change, or to update it only when needed manually.
For some more ways to use Custom filters, see Custom Filters - Advanced.