Variables and time range
Add placeholders to a query so viewers can pick dates or values, and give a report its own time range.
You will make a query reusable with placeholders and let report viewers change the date range.
Before you start: you need access to the Query workspace. Setting a report's default range needs Full control.
Write a variable
Put {{name}} in your SQL, or {{name=default}} to give it a default.
SELECT status, COUNT(*) AS invoices
FROM invoices
WHERE status = '{{status=overdue}}'
GROUP BY status
- Names start with a letter or
_and use letters, digits and_. - A plain number is used as a number; anything else becomes safe text. Both
'{{x}}'inside quotes and bare{{x}}work. - A variable with no default and no value stops the run with an error naming it.
- Values are limited to 256 characters, and a query may use up to 64 placeholders.
Fill variables in the workspace
When your SQL has variables, a Variables strip appears under the editor with one box per variable. A blank box uses the default, and a box with no default shows "required".
Reserved date range
The names {{range.start}} and {{range.end}} are special. They are filled by a time range picker instead of a text box.
SELECT COUNT(*) AS orders_placed
FROM orders
WHERE created_at >= '{{range.start=2026-01-01 00:00:00}}'
AND created_at <= '{{range.end=2026-12-31 23:59:59}}'
The picker's Time range options are All time, Today, Last 7 days, Last 30 days, Last 90 days, Last 12 months and Custom… (which adds From date and To date). Dates snap to whole days in your time zone. All time sends no values, so your defaults apply.
Time range on a report
- Open a report whose widgets use the range variables. The Time range picker appears in the header. It only shows when some widget uses the range, or a range is already set.
- A viewer's choice is their own for that visit. It is not saved.
- In edit mode, changing the picker sets the report's default range, which is saved. Presets are stored as presets, so "Last 7 days" keeps rolling.
- Your home page report also opens with its default range.
Compare with the previous period
Turn on Compare with previous period next to the Time range picker. Every widget that uses the range runs again over the same-length period just before — for example, this month against last month, or the last 7 days against the 7 days before. Headline numbers show the change, and charts and tables show how much the total changed.
The comparison is only on the report page itself; shared links and scheduled emails show the current period alone for now. In edit mode, a widget's Comparison setting can also be Against the previous period, Later half against the earlier half or None.
Report filters
A report filter is a control in the report header that sets a variable for every widget that uses it — for example one Region picker that narrows every chart on the page.
- In the widgets' SQL, use the same variable, and give it an empty default so "All" works:
WHERE ({{region=}} = '' OR region = '{{region=}}') - Open the report in edit mode and click Filters. Every variable used on the page is listed.
- Turn one on and set its Label, a Default value, and Choices from:
- Typed by the viewer — a text box.
- A record type and Field — a dropdown of that field's values, plus All.
- Save the report.
A viewer's pick is their own for that visit. A pick made in edit mode becomes the saved default. Shared links, scheduled emails and the home dashboard use the saved defaults; people viewing a shared link cannot change them.
Other variables on a widget
For variables other than the range pair, open Configure… on the widget. A Variables section lets you pin a value for that widget. The report's time range always wins over a pinned range value.
Tips
- Use a default so the query runs by itself.
- Open in Sheets keeps your current values by writing them into the query as defaults.