Connect a SQL Mode Chart

Chartio has a special syntax that will allow you to use your Dashboard Variables easily in SQL Mode.

Note: These instructions assume you have already added a Dashboard Variable.

Open your chart in SQL Mode. You should see a dropdown menu labeled Dashboard Variables. Open it, and select a variable from the list. That variable will be inserted into your query where the cursor is placed.

Select the dashboard variables in the chart editor

Syntax

Using the variables in SQL Mode is mostly straightforward, though the syntax for Dropdowns is slightly different than you may expect. See examples below. Note: syntax may vary depending on your database type. These examples are common use cases and do not cover all possible variable options.

Removing Quotes

Text variables are single-quoted. To remove the quotes, append .RAW to your variable:

{VARIABLE.RAW}

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

Date Filters

(Calendar, Date Slider, Hidden Variable)

Date filters have two attributes: a start and end date.

SELECT SUM("m"."cost") AS "Total sum of Cost"
FROM "public"."marketing" AS "m"
WHERE "m"."created_date" BETWEEN {VARIABLE.START} AND     {VARIABLE.END};

Multiple select: IN

(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 {VARIABLE.IN('"a"."activity_name"')};

Multiple select: NOT IN

(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 {VARIABLE.NOT_IN('"a"."activity_name"')};

Single select: EQUALS

(Dropdown, Hidden Variable single value, Input Widget)

SELECT COUNT(DISTINCT "a"."activity_id") AS "Count of Activity"
FROM "public"."activity" AS "a"
WHERE "a"."activity_name" = {VARIABLE};

Advanced Examples

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

CASE statement

(Dropdown, Hidden Variable list)

Statement inside bracket needs to be single quoted, and any other single quotes need to be escaped with a backslash.

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')};