Filters

A filter is a UI control that lets users provide inputs for the SQL queries behind a dashboard's reports, making it possible to interactively change what the dashboard displays. The available filters appear in a filter bar at the top of the dashboard.

Adding Filters to a Dashboard

Filters are activated by using a Cluvio-specific curly-brace expression in a report's SQL query, referencing a filter by its variable name:

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

This query creates two filters on the dashboard:

• A built-in timerange filter that filters on the created_at column.
• A custom filter named product_categories that filters on the category column.

Built-in Filters

Built-in filters are available in any SQL query without a prior definition. There are two built-in filters that cover the most common time-based use cases:

• timerange — a date/time range selector for filtering data by an absolute or relative date/time range.
• aggregation — a selector for the time granularity used in a GROUP BY clause (year, month, week, day, …).

Both are also available as custom filter types — date/time range and date/time aggregation — which allow more than one instance per dashboard and offer additional configuration options.

Custom Filters

Any new variable name used in a filter expression creates a custom filter. When a report is saved with an unrecognized variable name, Cluvio defaults to a multi-select text input control. Filters created this way without a definition are called ad hoc filters.

In most cases you will want to create a filter definition for the variable name to configure the control type, default values, and other behavior.

Managing Filters

All filter definitions are managed from the Filters overview page.

The list shows each filter's display name, variable name, control type, value type, last update time, and current status (for SQL-driven filters — see SQL Filters).

From the actions menu on any filter you can:

• Edit — open the filter configuration dialog.
• Duplicate — create a copy with all settings pre-filled. The variable name is generated automatically by appending a numeric suffix (e.g. my_filter_1).
• Find Usages — search for all reports that reference this filter's variable name. Results open in the global search, making it easy to see where a filter is used before editing or deleting it.
• Delete — permanently remove the filter definition.

Deleting a Filter

Deleting a filter whose variable name is still referenced in report SQL queries will not remove those references. The reports will continue to work but the filter will revert to ad hoc behavior (multi-select text input with no definition).

Creating a Filter Definition

There are two ways to create a filter definition:

1. From the Filters overview page — select Add Filter and enter the variable name. This lets you create a definition before the filter is referenced in any report. Variable names of built-in filters (timerange, aggregation, tz, and their variants) are reserved and cannot be used.

2. From the dashboard filter bar — on a dashboard with an ad hoc filter, open the filter bar and click the filter's name. A dialog opens to create the definition. Clicking the name of a filter that already has a definition opens the edit dialog instead.

   

In either case, the following dialog is shown.

Display Name

The Display Name is the label shown in the dashboard filter bar. It defaults to the variable name but can be changed to anything more user-friendly.

UI Control Type

The UI Control Type is the primary classification of a custom filter and determines its visual appearance and available configuration options:

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

Value Type

The Value Type determines how filter values are validated, normalized, and injected into SQL. Many control types fix the value type automatically (a Date Range filter always uses the Date value type, etc.). The available value types are:

• Text — values are injected as SQL string literals. Use for text columns.
• Number — values are injected as SQL numeric literals. Use for numeric columns.
• Boolean — valid inputs are true and false, injected as SQL boolean literals.
• Date — values must be ISO 8601 dates (YYYY-MM-DD). Use for DATE or TIMESTAMP columns (the time portion is ignored for comparisons).
• Time — values must be ISO 8601 times (HH:MM:SS). Use for TIME columns.
• DateTime — values must be ISO 8601 date-times (YYYY-MM-DDTHH:MM:SS). Use for TIMESTAMP or DATETIME columns.

Default Values

Any filter can have default values, which are pre-selected when a user first opens the dashboard. Default values must be valid according to the chosen value type.

When a filter has no selection (empty input, nothing chosen from a dropdown, etc.), it has no effect on the data:

1. Condition expressions ({column=variable}) are replaced with the tautological condition 1=1, so all rows pass the filter.
2. Transformation expressions ({column:variable}) are replaced with the unmodified column expression.
3. Value expressions ({variable.value}) typically resolve to SQL NULL. See each filter type's documentation for specifics.

SQL Filters

The Drop-down List control type supports populating its options dynamically from a SQL query run against a datasource. This makes it easy to present users with the actual values present in your database.

For SQL-powered drop-down filters:

• Datasource — the database to run the options query against.
• SQL Query — the query whose first column provides the option values. The optional second column provides human-readable labels.
• Data refresh rate — how often Cluvio re-runs the options query to keep the list up to date (from every 15 minutes to once a day, or manual-only).

The status indicator on the Filters overview page reflects the most recent options query run:

Status	Meaning
Green	Options query ran successfully.
Red	Options query returned an error. Hover for the error message.
Spinning	Options query is currently running.

To refresh the options immediately, use Refresh Filter Values from the filter's actions menu. If an options query is already running, you can use Cancel Refresh to abort it.

Filter SQL Syntax

Filter values are injected into SQL queries using Cluvio-specific curly-brace expressions that reference a filter's variable name in one of three forms:

Condition expression

{<column>=<variable>}
{[<complex_expr>]=<variable>}

Examples: {created_at=timerange}, {[CAST(created_at AS timestamp)]=timerange}

Replaces the entire condition. When the filter has no selection, it resolves to 1=1 so no filtering is applied.

Transformation expression

{<column>:<variable>}
{[<complex_expr>]:<variable>}

Example: {created_at:aggregation}

Use this form with transform filters: aggregation, date/time aggregation, and time zone.

Value expression

{<variable>.<selector>}

Examples: {my_filter.value}, {my_date_range.start}

Injects the raw value of the filter's selected option. When the filter has no selection, this typically resolves to SQL NULL. See each filter type's documentation for the available selectors and their exact behavior.