Drop-down

Usage

A drop-down filter allows the selection of values from a list of options which are either defined manually or produced by a SQL query. With Multi-select enabled, several options can be selected at once, as in the Country filter on the left; otherwise one, as in the State filter on the right.

The Country filter's options come from this query. The first column is the value of each option, and the second its label, here the country name with the number of airports in it:

SELECT country, country || ' (' || COUNT(*) || ')'
FROM airports
GROUP BY country
ORDER BY COUNT(*) DESC

The filter is used in a condition on a SQL expression.

Syntax

{[<expr>]=<filter_variable_name>}

The required type of <expr> depends on the configured Value Type for the filter.

A report that counts the airports in the selected countries, where the filter's variable name is airport_country:

SELECT country, COUNT(*) AS airports
FROM airports
WHERE {country=airport_country}
GROUP BY country

The condition compares the column with the selected option values, France and Germany, not with their labels. While nothing is selected, it has no effect and the report counts the airports in every country.

Configuration

To configure a drop-down filter, select Dropdown as the UI control type in the filter definition dialog.

The Value Type determines how the input values are validated by Cluvio and represented in SQL.

Drop-down Options

Every drop-down option has a value and optionally a label. The value of a selected option is what is used in the SQL queries, whereas the optional labels are what is shown in the drop-down UI. The available options of a drop-down filter can be defined in one of three ways:

1. Manually: Specify the values and labels directly in Cluvio.

   

2. Dynamic SQL: Use a SQL query to produce the values and labels, optionally making use of Cluvio filters syntax for dynamic dependencies on other filters.

   

3. Static SQL: Use a SQL query to produce the values and labels, without the ability to use Cluvio filters syntax, i.e. only use plain SQL.

   Unicode NUL

   The query results of static SQL queries must not contain Unicode NUL characters. If a NUL character is detected in a query result, Cluvio will not update the filter options and show an indicator of the problem in the filter's drop-down menu and in the filter edit dialog. If you require Unicode NUL characters in drop-down values or labels, use dynamic SQL instead, which does not have this limitation.

   tip

   Dynamic SQL is generally preferred over static SQL. There are no downsides to using dynamic SQL over static SQL and only dynamic SQL allows you to express dynamic dependencies between filters.

For drop-down filter using SQL, the values of the first column in the query result are always interpreted as containing the allowed option values, i.e. the allowed input values of the filter that are used in the SQL conditions, and the values of the optional second column are always interpreted as containing the option labels, i.e. the textual representation for the dashboard user.

tip

The SQL query for a drop-down filter can in fact produce a result set with more than 2 columns. Any additional columns are ignored, so they can be used e.g. for sorting the rows. The exception is a static SQL filter with a parent filter, whose third column holds the parent value of each option.

Slow Queries

Avoid slow queries, because the SQL queries of drop-down filters produce options for the user to select. While Cluvio's built-in caching of query results greatly improves the user experience for repeated access to the same options, with parameterized SQL queries in dynamic drop-down filters the options for a particular combination of input parameters may often not be cached and result in a new query execution, which a dashboard user experiences as a loading indicator on the UI control. That is why query performance is of particular importance for the SQL queries used in dynamic drop-down filters.

Additional Settings

Use the Configuration tab to set a default value and additional configuration.

The Refresh Rate option of a dynamic SQL drop-down filter determines the maximum age of the options data. The filter's SQL query is automatically re-run when the filter options need to be shown on a dashboard and the last result is older than what the refresh rate allows. This behavior for the data of dynamic SQL drop-down filters is analogous to the behavior for the data of report SQL queries.

In contrast, the Refresh Values option of static SQL drop-down filters determines the fixed interval in which the SQL query is re-run to refresh the options. These queries are run on a fixed schedule in the background unless Refresh Values: Manually is selected, in which case the options are only refreshed when you re-run the query in the filter editor.

For drop-down filters using SQL, the default value can either be static or dynamic by enabling Default to first value. When Default to first value is enabled, Require selection is implied. Thus, you should only use this setting if your SQL drop-down filter always produces at least one value, otherwise input validation fails and the report queries using the filter are unable to run.

A saved default can stop being one of the options — someone removes it from a manual list, or the SQL stops returning that row. The Default Value picker then shows the stale value marked (invalid), beside the warning that it is no longer among the options, rather than appearing empty as though no default were set.

If Validate Values is enabled, any input value must be in the allowed options of the drop-down filter, which prevents use of arbitrary input values e.g. through the manipulation of filter URL parameters.

Dependent Filters

A drop-down filter can show options that depend on what is selected in other filters. For example, once a country is picked, the city filter offers only that country's cities. The filter whose options depend on another is the child and the filter it depends on is the parent.

There are two ways to make a filter depend on another:

• Dynamic SQL: the filter's SQL query uses other filters directly, just like a report query does. This is the recommended way.
• Static SQL with a parent filter: the query returns a third column with the matching parent value for each option, and you pick the parent filter in the filter editor. This is the older mechanism. It is limited to a single parent, and the number of combinations of parent and child values it can hold is limited, because all of them are loaded up front (see Large Result Sets below).

On both, when the selection in a parent filter changes, any selected values of the child that are no longer among its options are removed from the child's selection. This carries on down a chain of dependent filters.

With Dynamic SQL

Use the filter SQL syntax in the query of a dynamic SQL drop-down filter to restrict its options by the selection in other filters. For a city filter that depends on a country filter:

SELECT DISTINCT city_id, city_name
FROM cities
WHERE {country_id=country}
ORDER BY city_name

When the selection in country changes, the query of the city filter is re-run with the new selection and the drop-down shows the result. While country has no selection, the condition resolves to 1=1, so the city filter offers all cities.

A dynamic SQL query can use any number of filters, of any type: other drop-downs, text inputs, date ranges and so on. It can also use SQL snippets, so the query of a filter has the same capabilities as the query of a report. If a report and a dynamic SQL drop-down filter run the same query, they share the cached query result.

Mutually Dependent Filters

Because a dynamic SQL query can use any other filter, filters can also depend on each other. A familiar example is the set of filters in an online shop for brand, size, color and price, where a choice in one narrows the options in the others (a faceted search). For a brand filter and a color filter that restrict each other, brand would use:

SELECT DISTINCT brand FROM products WHERE {color=color} ORDER BY brand

and color would use:

SELECT DISTINCT color FROM products WHERE {brand=brand} ORDER BY color

info

The query of a dependent filter runs again every time the selection in one of its parent filters changes. Results are cached for each combination of parent selections, so a combination that was queried recently loads quickly, while any other combination waits for the query to run. Keep the queries of dependent filters fast, as described under Slow Queries above.

With Static SQL and a Parent Filter

The query of a static SQL drop-down filter cannot use other filters. Instead, it can nominate one other filter as its parent filter:

1. On the Drop-down Options tab, choose Static SQL.
2. Select the parent in Parent Filter, next to the data source. Only filters that are themselves static SQL drop-downs can be selected.
3. Write a query that returns three columns: the option value, the option label, and the value of the parent filter that the option belongs to.

For a city filter whose parent is a static SQL country filter with the query SELECT DISTINCT country_id, country_name FROM countries:

SELECT DISTINCT city_id, city_name, country_id
FROM cities

The third column is what ties each option to the parent: unlike further columns in other drop-down queries, it is not ignored. Its values must match option values of the parent filter. The city drop-down then offers only the cities whose country_id is selected in country. While country has no selection, its default values are used instead. If it has no default values either, the city filter offers no options until a country is selected.

The query runs on its own refresh schedule, independently of the parent filter. All the options for every parent value are loaded in advance and narrowed down in the browser.

Default to first value is not available for a filter with a parent filter.

Large Result Sets

Because the child filter must produce a separate row for every combination of an option and a parent value it belongs to, the result sets of child filters can quickly become impractically large, and more so with chains of parent and child filters. Use dynamic SQL instead, which loads only the options for the current selection and can depend on more than one filter.

Value Selectors

A value selector allows to tap into SQL value expressions associated with the input text.

Count

SELECT {my_dropdown.count}

The .count selector produces the number of selected values:

• 0 or 1 for a single-select drop-down
• 0, 1 or more for a multi-select drop-down

Selected

SELECT {my_dropdown.selected}

The .selected selector produces a boolean expression: true if the filter has at least one value selected, false otherwise.

Min / Max

SELECT {my_dropdown.min}, {my_dropdown.max}

The .min and .max selectors produce the minimum/maximum of the selected values (not the labels!) (numeric or lexicographic sort order) or NULL if there is no selection.

Value

SELECT {my_dropdown.value}

The .value selector produces a value-type-specific SQL expression for the input value, or NULL when the drop-down has no selection.

Single-select drop-downs

This selector is only applicable to single-select drop-downs. It always produces NULL for a multi-select drop-down filters.

Raw Value

SELECT * FROM {my_dropdown.value_raw}

The .value_raw selector results in direct substitution of the drop-down value (not the label!) into the SQL. It can be used to dynamically alter the SQL query executed, e.g. to allow dynamically selecting a schema or table to query:

caution

The ability to use the filter input unquoted in SQL can allow SQL injections. Reports using such inputs should be shared only with trusted users and constrained via fixed parameters when embedding or via the use of drop-down filters with strict input validation.

Values

SELECT {my_dropdown.values}

The .values selector produces the selected drop-down values (not labels!) as a comma-separated list in parenthesis (e.g. ('one', 'two', 'three', ...)), such as can be used with an WHERE IN clause, or a database- and value-type-specific empty set expression.

Values Concatenated

SELECT {my_text_filter.values_concat}

The .values_concat selector produces a single SQL string literal with all selected drop-down values (not labels!) separated by comma (e.g. 'one,two,three'), or an empty string when the filter has no selection.

Values Array

SELECT {my_text_filter.values_array}

The .values_array selector produces the selected values as a SQL array (e.g. ['one','two','three']) or an empty array when the filter has no selection.

Raw Values

SELECT {my_text_filter.values_raw} FROM my_table

The .values_raw selector results in direct substitution of the selected drop-down values (not labels!) into the SQL query, separated by comma. This may be useful to dynamically select columns in the query. The same caution applies as with value_raw for single-select text filters.