Variables

Variables are the filters viewers pick in the bar above the widgets: a period, a branch, a warehouse. A widget's query reads each one as $VAR.<name>. The ⚙ beside the variables bar in the editor opens this section.

On the left, the list of variables and Add variable; on the right, the selected one's form. Save saves the whole list. Variables are versioned with the widgets: a change reaches the App on the next Publish.

A variable

Under the form, Use in SQL shows how to write it in a query. A dashboard has at most 20 variables.

Types

Date range

The presets are Last hour, Today, Last 7 days, Last 30 days, Last 90 days, This month, Last month, This year and All time, besides Custom range. PostgreSQL computes the bounds when the query runs, in the viewer's time zone. A custom range includes its last day. All time has no bounds: both are NULL.

A range also gives its previous period, $VAR.period.prev_from and $VAR.period.prev_to: as long, and just before. This month gives last month, Today yesterday, Last 30 days the 30 days before.

SELECT count(*) FILTER (WHERE reported_at >= $VAR.period.from AND reported_at < $VAR.period.to) AS this_period,
       count(*) FILTER (WHERE reported_at >= $VAR.period.prev_from AND reported_at < $VAR.period.prev_to) AS previous
FROM work_orders

A KPI over this query shows the change against the previous period.

Select: where the options come from

A single Select also offers All, which means unset. When a variable it depends on changes, the list reloads, and a picked value the new list no longer offers is dropped.

Acme's Branch variable is a Select from the table branches. Its options show each branch by its row presentation, and its value is the branch's id.

Defaults

Changing a variable's type, its Allow several values or its options' source clears its default.

Reset, in the bar, takes every variable back to its default once a viewer changed one.

Unset means no filter

A variable with no value binds NULL. Write each filter so that NULL lets every row through. The widget drawer's Optional filter writes this for you:

WHERE ($VAR.branch IS NULL OR wo.branch = $VAR.branch::uuid)
  AND ($VAR.period.from IS NULL OR wo.reported_at >= $VAR.period.from)
  AND ($VAR.period.to IS NULL OR wo.reported_at < $VAR.period.to)

Filtering is not restricting

A viewer can send any value of the right type for a $VAR, including one the bar doesn't offer. A Select of the viewer's own branches doesn't stop them from asking for another one. To restrict rows, use $USER in the query:

WHERE wo.branch = $USER.branch

$USER.<column> reads the viewer's current row in users on every run, with the column's own type. Any column works, including ones you added, except password_hash. In the editor, $USER is you.

Next steps

• Editing a Widget: inserting a filter.
• Data: auto-refresh.