Advanced Variables¶

When your dashboard needs dynamic behavior beyond simple filters, this advanced variables guide shows how to:

wire dependent variables (country → state, metric selectors, etc.),
conditionally emit filters using helper macros,
show or hide charts/boards based on the current values.

Dependent Variables¶

Dependent filters arise whenever one variable drives another (pick a country, then the state dropdown updates). To keep the UI and SQL in sync:

Source the dependent dropdown from a query that references the parent variable.
Guard the filtering logic with the filter macro so the dependent filter only applies when both variables have values.

variables:
  country:
    column: locations.country
    input: select

  state:
    query: |
      SELECT DISTINCT state
      FROM locations
      WHERE {{ filter('country', '=', country) }}
      ORDER BY state
    input: select

queries:
  customers:
    sql: |
      SELECT *
      FROM customers
      WHERE {{ filter('country_name', '=', country) }}
        AND {{ filter('state_name', '=', state, country and state) }}
    source: my_postgres

The state list refreshes whenever country changes, and its filter only runs when both country and state are defined. This keeps the dropdown options clean and the SQL lean.

Conditional Filters¶

Use the filter macro (and filter_date_range for ranges) to keep your SQL compact while still handling undefined or null gracefully. You can rely on the default behavior (value is defined), or pass an explicit condition to control when the filter fires.

queries:
  sales:
    sql: |
      SELECT *
      FROM revenue
      WHERE {{ filter('region', '=', region) }}
        AND {{ filter_date_range('order_date', date_range) }}
        AND {{ filter('total_revenue', '>=', min_revenue) }}
    source: my_postgres

If you need customized fallback values or formatting, use Jinja inside the arguments (e.g., region or 'North').

Layout Visibility (`visible`)¶

Variables can drive which charts or boards appear on the dashboard. Layout items and charts support a visible field that controls whether the item is rendered.

Note: variables.visible is a separate, unrelated field that controls whether a variable's input control appears in the UI. visible on a layout item or chart controls whether that item is included in the rendered output.

The visible field accepts:

A variable name (bare string) — item is shown when the variable is truthy
A Jinja boolean expression — no {{ }} braces required
A {query, column} probe — executes a named query and reads a single boolean cell
true / false — static visibility

If visible is omitted, the item is always shown.

Conditionally Showing a Row¶

Use a checkbox variable to toggle a section:

variables:
  show_details:
    input: checkbox
    default: false

rows:
  - summary_kpis

  - visible: show_details
    rows:
      - region_map
      - region_table

Conditionally Showing a Chart¶

Use a nested-face wrapper with visible to show/hide a chart:

variables:
  show_forecast:
    input: checkbox
    default: false

rows:
  - trend_line
  - visible: show_forecast
    rows:
      - forecast_line

Swapping Content¶

List both items with mutually exclusive expressions:

variables:
  mood:
    input: select
    options:
      static: [happy, sad]
    default: happy

rows:
  - visible: "mood == 'happy'"
    style:
      background: "#FFF9C4"
    text: |
      # :smile:
      Everything is awesome!

  - visible: "mood == 'sad'"
    style:
      background: "#37474F"
      color: white
    text: |
      # :cry:
      Everything is terrible.

Query-Backed Visibility¶

Execute a named query and read a single boolean cell:

queries:
  layout_flags:
    sql: "SELECT {{ has_schema }} AS show_warm"

rows:
  - visible:
      query: layout_flags
      column: show_warm
    rows:
      - column_inventory

The query must return exactly one row and the named column must hold a boolean-coercible value. Multiple rows raise a ValueError.

This approach keeps your layout definitions declarative and easy to read.