Dashboard SQL Reference

Dashboards are expressed as SQL queries. Each query defines an UI element. Queries are separated by ;.

All SQL is valid DuckDB SQL. For more look at the DuckDB SQL docs.

Shaper creates a custom SQL types. You can cast your data to these types to render the data differently. The simplest way to cast a column’s type is by appending the type with ::, for example SELECT 'hello'::LABEL.

This can be used to control the visualization of the data, add filter options and control the dashboard layout.

Tables

Data is rendered as table by default. Column names are used as table headers.

PERCENT

SELECT mynumber::PERCENT AS percent;

Renders a DOUBLE as percentage value. Your numbers should probably be between 0 and 1.

TREND

Show a trend indicator as a table column:

SELECT
  10 AS "this week",
  5 AS "last week",
  (10 / 5)::TREND AS "change";

The change column will show “100%↑”.

Single Value

SELECT 'hello world';

If a query returns a single row and a single column, it is rendered as big single card.

PERCENT

SELECT 0.33::PERCENT AS percent;

Renders a DOUBLE as percentage value. Your numbers should probably be between 0 and 1.

COMPARE

Show a comparison value below the value and a trend indicator:

SELECT
  10 AS "this week",
  5::COMPARE AS "last week";

This will show last week: 5 [100%↑] below the value.

Bar Charts

To render a bar chart you need specify along which axis to render the chart and how to plot the values. Additionally you can group the data by a category.

Any additional columns will only be visible in the tooltip on hover.

Example:

SELECT time::XAXIS, value::BARCHART, category::CATEGORY FROM my_table

XAXIS, YAXIS

You can render a bar chart along the x-axis or y-axis.

Often you will be using a timestamp as the axis column. The axis column must be a VARCHAR, TIMESTAMP, TIME or numer type.

BARCHART

Mark the column as BARCHART that you like to display as value of the bars in the chart. This must be a numeric value or INTERVAL.

CATEGORY

Mark a VARCHAR column as category to render multiple bars for each point on the axis.

BARCHART_STACKED

When using CATEGORY you can change BARCHART to BARCHART_STACKED to render categories as a single, stacked bar instead of rendering multiple bars.

_PERCENT

Use BARCHART_PERCENT or BARCHART_STACKED_PERCENT to show the value of the chart as percentage. This sets the max value of the chart to 100%. The values should most likely be between 0 and 1.

Line Charts

Line charts work almost identical to bar charts, but they don’t have the YAXIS and _STACKED options.

XAXIS

Specify the column to use as x-axis. Most likely this will be a time dimension.

LINECHART

Specify which column to show as value (numeric or INTERVAL)

CATEGORY

Optionally mark add a column category to render multiple lines in the line chart

LINECHART_PERCENT

Render the value of the chart as percentage. This sets the max value of the chart to 100%. The values should most likely be between 0 and 1.

Filtering

Make dashboards interactive by adding filter options to them.

The resulting filter value will be available in all following queries on the dashboard via DuckDB’s built-in getvariable('varname') function.

varname is the column name you specified via AS varname in the filter query.

DATEPICKER

Create a date picker:

SELECT today()::DATEPICKER AS date;

The value (above today()) is the date that is selected by default. You can use the resulting variable in the following queries with getvariable('date'). The variable is of type DATE.

DATEPICKER_FROM, DATEPICKER_TO

Create a date range picker:

SELECT
  (today() - INTERVAL '7 days')::DATEPICKER_FROM AS "from",
  (today())::DATEPICKER_TO AS "to";

The query specifies the default date range and generates two variables from and to;

Use the variables in following queries:

SELECT *
FROM mytable
WHERE created_at BETWEEN getvariable('from') AND getvariable('TO');

DROPDOWN

Define a drop-down select menu:

SELECT type::DROPDOWN AS type
FROM my_table
GROUP BY type
ORDER BY type;

The first value is selected by default. You can use the resulting variable in the following queries with getvariable('type'). The variable is of type VARCHAR.

To add a custom default value, return it as first result of the query. Use UNION ALL for this:

SELECT 'default'::DROPDOWN AS type
UNION ALL
(
  SELECT type
  FROM my_table
  GROUP BY type
  ORDER BY type
);

LABEL

Specify separate label and value for a dropdown:

SELECT
    EXTRACT(MONTH FROM range)::TEXT::DROPDOWN AS month,
    strftime(range, '%B')::LABEL
FROM range(DATE '2024-01-01', DATE '2025-01-01', INTERVAL 1 MONTH)
ORDER BY range;

This uses a number as value for the dropdown and shows the month name as label.

DROPDOWN_MULTI

Same as DROPDOWN, but allows selecting multiple values:

SELECT type::DROPDOWN_MULTI AS type
FROM my_table
GROUP BY type
ORDER BY type;

By default all values are selected. The resulting variable is a list of VARCHAR values. You can use it with an IN clause:

... WHERE type IN getvariable('type')

LABEL

Labels for multi-select dropdowns work the same as for single-select dropdowns. See the example above.

HINT

For multi-select dropdowns you can add a hint in addition to the value and the label:

SELECT
    type::DROPDOWN_MULTI AS type,
    count(*)::HINT
FROM my_table
GROUP BY type
ORDER BY type;

A common use case is to show the count of items for each category in a dropdown.

Download Buttons

DOWNLOAD_CSV

Download the result of a query as CSV file:

SELECT concat('mydownload-', today())::DOWNLOAD_CSV AS Download;
SELECT *
FROM my_table
WHERE created_at BETWEEN getvariable('from') AND getvariable('to')
ORDER BY created_at;

To render a download button return a single row with a single column with the DOWNLOAD_CSV type.

The value (here concat('mydownload-', today())) is the name of the file that will be downloaded.

And the column name (here Download) is the label of the button.

The query that follows the download button query is the data that will be downloaded.

You can use variables from previous queries to customize the download and create flexible download interfaces with this.

DOWNLOAD_XLSX

Download the result of a query as Excel file:

SELECT concat('mydownload-', today())::DOWNLOAD_XLSX AS Download;
SELECT *
FROM my_table
WHERE created_at BETWEEN getvariable('from') AND getvariable('to')
ORDER BY created_at;

Excel files (.xlsx) work the same as CSV files. See above.

Layout

Shaper tries to automatically layout the dashboard for you and use all available screen space.

Filters and download buttons are at the top in the header of a section. Other queries are rendered as content below.

Depending on the screen size, up to 5 content queries are rendered next to each other.

SECTION

Use SECTION to create a new section in the dashboard:

SELECT 'My Section'::SECTION;

This will create a new section with the title “My Section”.

Create a section without title to only affect the layout:

SELECT ''::SECTION;

If a section header query returns no rows, the whole section is hidden:

SELECT ''::SECTION WHERE FALSE;
SELECT 'this query result is never displayed';

This is useful to conditionally hide a section depending on other query results or filters.

Adding filters or download buttons creates a new section automatically.

LABEL

Add labels to any element on the page with a LABEL query right before it. Return a single row with a single column:

SELECT 'My Label'::LABEL;
SELECT 10 AS "My Value";

For content queries the label is shown as title at the top of the card.

For filters and download buttons the label is shown before the button. DROPDOWN_MULTI is an exception and shows the label on the button itself.

PLACEHOLDER

Leave an empty space in the content grid:

SELECT ''::PLACEHOLDER;

Placeholders are useful to align content vertically if you have no other content to fill the space:

SELECT 200 AS "Total Overall";
SELECT 150 AS "Total This Week";
SELECT ''::SECTION;
SELECT ''::PLACEHOLDER;
SELECT 50 AS "Total Last Week";

This shows four cards on the screen. “Total This Week” and “Total Last Week” are aligned vertically. And the space below “Total Overall” is empty.