Skip to main content

Filters

A filter is a UI control that allows to provide inputs for the SQL queries powering the reports on a dashboard, making it possible for users to interactively change what the dashboard displays. The available filters are displayed in a filter bar at the top of the dashboard.

Filter barFilter bar

Adding Filters to a Dashboard​

Adding filters to a dashboard starts with the SQL query of a report. Using a Cluvio-specific curly-brace expression in the SQL will make the report use a filter with a particular variable name.

For example, creating a report with the following SQL query:

SELECT *
FROM orders
WHERE
// highlight-next-line
  {created_at=timerange} AND {category=product_categories}

causes 2 filters to appear on the dashboard:

  • A built-in filter with variable name timerange that filters on the created_at column of the orders table.
  • A custom filter with variable name product_categories that filters on the category column of the orders table.

Built-in Filters​

Built-in filters can be used in SQL queries right away, without having to define them. There are 2 built-in filters that cover the common use cases of aggregating and filtering time-based data:

  • timerange: A filter control showing a date/time range selector for filtering data by an absolute or relative date/time range.
  • aggregation: A filter control that allows to select a desired time granularity (year, month, week, day, ...) for the aggregation of data points via a GROUP BY clause.

Both of these built-in filter types are also available as custom filters in form of the custom date/time range and custom date/time aggregation. Using custom filters allows more than one date/time range selector or aggregation control on a single dashboard, as well as more configuration options.

Custom Filters​

Additional filters with customized behavior can be added simply by using a new variable name in a Cluvio filter expression in a report's SQL query.

When a report is saved with the SQL query containing a new variable name, the filter defaults to a multi-select text input control. We refer to such filters without definitions as "ad hoc" filters.

In most cases, you would want to create a filter definition for the new variable name, which allows configuring the filter's control type and other options that together define the filter's appearance and behavior.

There are 2 ways to create a filter definition:

  1. On a dashboard that displays the new ad hoc filter control, open the filter bar as if to select filter values and click on the filter name of the filter. A dialog will open to create the filter definition. If you click on the filter name of a filter that already has a definition, the same dialog opens for editing the existing filter definition.

    image-400 image-400

  2. Use Add Filter from the Filters overview page and enter the variable name for the filter. In this way you can create a filter definition before the filter is actually used in a SQL query. Note that the variable names of the built-in filters (timerange, aggregation, tz and some of their variants) are reserved and cannot be used. An error will be shown if you mistakenly use a reserved variable name.

In either case, the following dialog is shown.

image-600 image-600

Configuration​

The Display Name allows changing the name shown in the dashboard filter bar. on a dashboard.

The UI Control Type is the primary classification of a custom filter and determines the filter's visual appearance and remaining configuration options. The following is a list of the filter UI control types available in Cluvio:

The Value Type of a custom filter determines how input values are validated, normalized and represented in SQL by Cluvio. The available value types for a custom filter depend on the choice of UI control type. Many UI control types predefine the value type (a Date / Time Range filter always has value type DateTime, a Date filter always has value type Date, etc.). The following is a list of value types available in Cluvio:

  • Text: The most general value type. Filter values of this type are represented in SQL as string literals and can be used to filter SQL text expressions (e.g. text columns).
  • Number: A value type that encompasses both integers and decimals. Filter values of this type are represented in SQL as numeric literals and can be used to filter numeric SQL expressions (e.g. numeric columns).
  • Boolean: A value type that represents boolean values. Valid input values are true and false and they are represented in SQL as boolean literals. Filters with this value type can be used on boolean SQL expressions (e.g. boolean columns).
  • Date: A value type for dates without time information. Valid input values are ISO8601 date strings in the format <year>-<month>-<day>, e.g. 2023-03-26. Filters with this value type can be used on date or date-time SQL expressions (e.g. DATE, TIMESTAMP or DATETIME columns). When used to filter date-time columns, the time portion of the column values is ignored for the comparisons.
  • Time: A value type for time values without a date. Valid input values are ISO8601 time strings in the format <hours>:<minutes>:<seconds>, e.g. 12:30:45 (24 hour format). Filters with this value type can be used on time or date-time SQL expressions (e.g. TIME, TIMESTAMP or DATETIME columns). When used to filter date-time columns, the date portion of the column values is ignored for the comparison.
  • DateTime: A value type for date & time information. Valid input values are ISO8601 date-time strings in the format <year>-<month>-<day>T<hour>:<minute>:<second>, e.g. 2023-03-26T12:35:13. A space is also accepted in place of the T, e.g. 2023-03-26 12:35:13. Filters with this value type can be used on date-time SQL expressions (e.g. TIMESTAMP or DATETIME).

Default Values​

All custom filters can have default values. If the default value is given as input, e.g. for a text filter, the default values must be valid according to the chosen Value Type. If a custom filter has no selection (empty text input, no options selected from a drop-down filter, etc.), the filter has no effect on the data being shown on a dashboard, because the Cluvio query parameters of custom filters which have no selection are translated as follows:

  1. Conditions ({[<expr>]=<filter_variable_name>}) are translated into the tautological condition 1=1.
  2. Transformations ({[<expr>]:<filter_variable_name>}) are translated into <expr> unaltered.

The translation of value parameters of custom filters that have no selection depends on the selector used and whether that selector is actually available for the filter in question. In many cases, referring to a value parameter of a custom filter that has no selection (e.g. {my_filter.value}) will translate to a SQL NULL expression, as will value parameters which reference an undefined selector. Please refer to the documentation of each filter's value parameters for details.

Filter SQL Syntax​

The input values of any filter are provided to the SQL queries via the use of Cluvio-specific syntax. The filter expression uses curly brackets ({}) to reference a filter variable name in one of three forms:

  1. Condition expression

    {<column_name>=<variable_name>}
    or
    {[<expr>]=<variable_name>} -- complex expressions must be wrapped in square brackets

    For example: {created_at=timerange} or {[CAST(created_at AS timestamp)]=timerange}.

    For most filters, when there is no selection, the resulting expression in SQL would be 1=1 so that no filtering would apply.

  2. Transformation expression

    {<column_name>:<variable_name>}
    or
    {[<expr>]:<variable_name>} -- complex expressions must be wrapped in square brackets

    For example: {created_at:aggregation}.

    Use this syntax with transform filters: built-in aggregation, custom date time aggregation and time zone.

  3. Value expression

    {<variable_name>.<selector>}

    For example: {my_filter.value} or {my_date_range.start}. When the filter has no selection, the value would typically be NULL or the (DB specific) min/max possible time for the time range start/end filters.

    See the documentation of specific filters for details on the available selectors.