Filters

A filter is a UI control for the data shown on a dashboard. All filters available on a dashboard are grouped in a filter bar at the top of a dashboard. There are built-in filters and custom filters.

Query Parameters

The input values of any filter are bound to SQL queries via the use of Cluvio-specific query parameters. A Cluvio query parameter is an expression enclosed in curly brackets ({}) that references a filter variable name in one of three forms:

1. Condition

   {[<expr>]=<filter_variable_name>}
   For example: {[created_at]=timerange}
2. Transformation

   {[<expr>]:<filter_variable_name>}
   For example: {[created_at]:aggregation}
3. Value

   {<filter_variable_name>.<selector>}
   For example: {my_date_range.start}

Parameters of the 1st and 2nd type always expect a SQL expression (<expr>) in square brackets ([]) on the left-hand side of the = or : operator (e.g. a column name) and a filter variable name on the right-hand side.

Simplified Syntax

When the left-hand side <expr> for a condition or transformation is just a column name, the surrounding square brackets can be omitted, as in {created_at=timerange}. This simplified syntax is considered idiomatic in Cluvio query parameters whenever you are applying a filter directly on a column.

Parameters of the 3rd type are called value parameters which resolve to SQL value expressions. There are built-in value parameters for the built-in filters, as well as value parameters for custom filters. Which syntactic forms can be used with a particular filter, and which value parameters are available, is documented for each type of filter. The following is an example that uses Cluvio query parameters to bind inputs to the built-in filters aggregation and timerange in a report query:

SELECT
  {created_at:aggregation},
  COUNT(*)
FROM orders
WHERE
  {created_at=timerange}
GROUP BY 1
ORDER BY 1

note

The above example uses the GROUP BY and ORDER BY clauses with positional column aliases that refer to the result columns in the SELECT clause. Such positional references are supported by most, but not all SQL databases. If not supported, the SQL expressions from the SELECT clause have to be repeated in the GROUP BY and ORDER BY clauses.

When such a query is saved for a report on a dashboard, the UI will show a filter bar that looks as follows:

In this way, the Cluvio query parameters used in reports (or dynamic drop-down filters) determine the UI filter controls shown on a dashboard.

Built-in Filters

Built-in filters can be used in SQL queries without any preparation. The filter variable names of built-in filters are reserved and thus cannot be used for custom filters.

Built-In Date-Time Aggregation

The built-in date-time aggregation filter is a transformation used to aggregate data over a set of predefined date-time intervals. It is typically used in SQL in both the SELECT and GROUP BY clauses in combination with an aggregate function, as in the following query:

SELECT
  {created_at:aggregation},
  COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 2

Syntax

{[<date-time-expr>]:aggregation}

where <date-time-expr> is a date-time SQL expression (e.g. a column of type TIMESTAMP in PostgreSQL).

The generated SQL for the built-in aggregation filter is database-specific. Below is the generated SQL of the above query for a PostgreSQL database when aggregating by Day.

SELECT
  DATE_TRUNC('day', (created_at)::timestamp)::date,
  COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 2

The default selection for a built-in date-time aggregation filter is Day.

Built-In Date-Time Range

The built-in date-time range filter is a condition used to reduce the visualized data to an absolute or relative range of date & time. It is used in SQL in the WHERE clause of a query:

SELECT
  *
FROM orders
WHERE
  {created_at=timerange}

Syntax

{[<date-time-expr>]=timerange}

The generated SQL for the timerange filter is database-specific. Below is the generated SQL of the above query for a PostgreSQL database when filtering by the last 7 days (including today) at time of writing.

SELECT
  *
FROM dashboards
WHERE
  ((created_at) >= '2023-03-16T00:00:00' AND (created_at) <= '2023-03-22T23:59:59.999999')

The default selection for a built-in date-time range filter is All time, i.e. an unbounded range.

tip

String/Text expressions (e.g. columns) can be used with the timerange filter, as long as the values are in the ISO8601 format, e.g. 2016-07-01T00:00:00.

Custom Filters

Any filter that is not a built-in filter is a custom filter. All custom filters of an organization can be managed in the Filters section of the organization settings.

A custom filter is created in one of two ways:

1. Explicitly, by selecting Add Filter from the Filters overview page in the organization settings.
2. Implicitly, by using a variable name for a query parameter in a SQL query that is not yet used by any other filter.

When a new custom filter is created implicitly, it will initially not appear in the Filters overview page in the organization settings. Such a filter does not (yet) have a definition and is referred to as an "ad hoc" filter. Such an ad hoc filter is always a multi-value Text Input, which can only be changed once a filter definition is created.

The following is a list of the custom filter types available in Cluvio:

• Text Input
• Yes/No
• Drop-down List
• Date-Time
• Date-Time Range
• Date
• Date Range
• Time
• Time Range

Filter Definitions

While it is not necessary for a filter to have a definition if the default behavior of a simple text input is sufficient, it is only with a filter definition that you get full control over the appearance and behaviour of your filter.

A filter created by selecting Add Filter from the Filters overview page already has a definition. To create a filter definition for an ad hoc filter, e.g. {name_column=name_filter}, simply click on the filter name in the filter bar (in edit mode):

For filters with an existing definition, the name is a link that takes you directly to the Edit Filter dialog, where you can configure your filter definition.

Every custom filter lets you choose a Display Name separately from the filter variable name. The display name is the name of the filter shown in the filter bar on a dashboard.

The UI Control Type is the primary classification of a filter and determines the filter's visual appearance and the types of values it accepts as input.

Value Types

The Value Type determines how input values are validated, normalized and represented in (database-specific) SQL. The possible value types depend on the choice of control type, with many control types predefining the value type (e.g. a Date-Time Range filter always has value type DateTime). The following is a list of filter 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

Custom filters can have default values configured. The default values must be valid according to the chosen Value Type. If a custom filter has no selection (e.g. empty text input, no options selected from a drop-down filter), 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 expression 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. 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 a particular value parameter for details.