Skip to main content

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.

image-500image-500

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.

Configuration​

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

Dropdown Dropdown

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

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.

    Dropdown Dropdown

  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.

    Dropdown Dropdown

  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 by Cluvio but can be used e.g. for sorting the rows.

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.

Dropdown Dropdown

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.

If Validate Values is enabled, Cluvio will enforce that 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.

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.