Chartio is going away March 1, 2022
Documentation Controls and Variables

SQL Mode Syntax for Controls

Using the Controls in SQL Mode is mostly straightforward–typically, you wrap the Control name in curly brackets (i.e., {YOUR_CONTROL_NAME}). However, the syntax for referencing multi-select Dropdowns, for example, is slightly different than you may expect. To help you understand these references, check out our examples below.

Note: Syntax may vary depending on your database type. These examples are common use cases and do not cover all possible Control options.

Removing quotes from Text Input values

Text Input Controls are single-quoted. To remove the quotes, append .RAW to your Control name:

{CONTROL_NAME.RAW}

.RAW can only be used on single values, not lists.

Date filters

Applicable Control types: Calendar, Date Slider, Hidden Variable

Date filters have two attributes: a start and end date. Append .START or .END to use either attribute, respectively.


select sum("m"."cost") as "Total sum of Cost"
from "public"."marketing" as "m"
where "m"."created_date" between {CONTROL_NAME.START} and {CONTROL_NAME.END};

Date Bucket

The format for referencing a Date Bucket looks like this:

{CONTROL_NAME.BUCKET('"table"."column"')}

Just replace CONTROL_NAME with the name of your Date Bucket Control.

Note: Make sure to wrap the table-column reference in single quotes and, in your GROUP BY clause, use the same column alias provided in your SELECT statement.


select {CONTROL_NAME.BUCKET('"Users"."created_date"')} as "Date Bucket of Created Date"
from "public"."users" as "Users"
group by "Date Bucket of Created Date"

Getting the Date Bucket value

If you want to perform different calculations that depend on the current value of the Date Bucket, you can get the value, which is returned as an all-lowercase string:


{CONTROL_NAME} = 'bucket_value'

Just replace CONTROL_NAME with the name of your Date Bucket Control and bucket_value with day, week, year, etc.

Multiple select: IN

Applicable Control types: Dropdown, Hidden Variable list

Note: Column alias needs to be in single quotes.


  select count(distinct "a"."activity_id") as "Count of Activity"
  from "public"."activity" as "a"
  where {CONTROL_NAME.IN('"a"."activity_name"')};

Multiple select: NOT IN

Applicable Control types: Dropdown, Hidden Variable list

Note: Column alias needs to be in single quotes.


select count(distinct "a"."activity_id") as "Count of Activity"
from "public"."activity" as "a"
where {CONTROL_NAME.NOT_IN('"a"."activity_name"')};

Single select: EQUALS

Applicable Control types: Dropdown, Hidden Variable single value, Text Input


select count(distinct "a"."activity_id") as "Count of Activity"
from "public"."activity" as "a"
where "a"."activity_name" = {CONTROL_NAME};

Advanced Examples

You can apply functions or CASE statements to columns you’re filtering on.

CASE statement

Wrap the CASE statement inside the brackets with single quotes; any other single quotes used within the CASE statement need to be escaped with a backslash (\).

The following example uses a CASE statement within a multi-select Dropdown. You could use the same syntax for a Hidden Variable list.


select count(distinct "a"."activity_id") as "Count of Activity",
from "public"."activity" as "a"
where {DROPDOWN_NAME.IN('case "a"."activity_name" when \'Comment\' then \'Post\' else "a"."activity_name" end')};

Use wildcards in Text Inputs

Check out our FAQ showing how to use wildcards with multi-select Text Inputs.

For single-select Text Inputs, you’d append .RAW to your Control name and wrap your search term (including any wildcards) in single quotes. Here’s a general example:

"column_name" like '%{CONTROL_NAME.RAW}%'


where "column_name" like '%{CONTROL_NAME.RAW}%'
Need more help?