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.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.START} AND {CONTROL.END};

Date Bucket

SELECT {CONTROL.BUCKET('"Users"."created_date"')} AS "Date Bucket of Created Date"
FROM "public"."users" AS "Users"

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:

{DATE_BUCKET_NAME} = 'bucket_value'

Just replace DATE_BUCKET_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.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.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};

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.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 implement an “is one of” filter with your Text Input using wildcards.