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:
- ConditionFor example:
{[<expr>]=<filter_variable_name>}
{[created_at]=timerange}
- TransformationFor example:
{[<expr>]:<filter_variable_name>}
{[created_at]:aggregation}
- ValueFor example:
{<filter_variable_name>.<selector>}
{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.
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
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
{[<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}
{[<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.
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:
- Explicitly, by selecting
Add Filter
from theFilters
overview page in the organization settings. - 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:
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
andfalse
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
orDATETIME
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
orDATETIME
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 theT
, e.g.2023-03-26 12:35:13
. Filters with this value type can be used on date-time SQL expressions (e.g.TIMESTAMP
orDATETIME
).
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:
- Conditions (
{<expr>=<filter_variable_name>}
) are translated into the tautological expression1=1
. - 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.