Manage Controls

Once you’ve added some Dashboard Controls, they won’t be much help until you connect them to charts, which can be done in both Visual Mode and SQL Mode. However, using Dashboard Controls isn’t confined to only Query steps. Explore more uses of Dashboard Controls to leverage them in Chartio.

If you’re having trouble with any of your Dashboard Controls, check out at our Troubleshooting tips.

Connect Controls to charts

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

In Visual Mode

  1. Open the chart to which you’d like to connect your Dashboard Control.
  2. Controls filter columns, so you’ll need to add the column to filter under the Filters section.
  3. As usual, choose the appropriate filter operator, but for the value, select your Control from the values list. The Control name will be wrapped in curly braces (e.g., {CONTROL_NAME}).

    For datetime Controls, such as Date Sliders and Calendars, you can append .START or .END to the Control name to connect either the start or end date of your Control, respectively (e.g., {CALENDAR_NAME.START}). Check out the other available datetime functions you can leverage in Chartio.

  4. When you’re ready, click Run Query to apply your new filter. Make sure to save your chart to keep your changes!

In SQL Mode

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

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

Select the Dashboard Controls in the chart editor

Syntax

Using the Controls 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 Control options.

Removing quotes

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

{CONTROL.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. 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"
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 {CONTROL.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 {CONTROL.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" = {CONTROL};

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

More uses of Dashboard Controls

Besides chart filters, you can also use Dashboard Controls in:

  • Data Pipeline formulas and some Data Pipeline steps (Filter, CASE statement)
  • Chart titles
  • Text elements
  • Column names (Can be used in both Interactive Mode and SQL Mode. For Interactive Mode, set the label.)

Edit Controls

To edit an existing Dashboard Control, open your dashboard settings (by selecting Settings from the dashboard’s sidebar menu) and switch to the Variables tab. You can also get to this tab, by selecting Edit Variable from your Control’s menu options.

From the Variables tab, you can edit the Controls’ default, format, and background color.

Edit a Dashboard Control from the Settings menu

Edit Filter

Some Controls like Dropdowns and Date Sliders are built using a database query and can be further edited with the option of adding Data Pipeline Steps to modify the results. To do this edit, use the Edit Filter option in the Control’s menu option to open up the Data Explorer.

Further edit Dashboard Controls and filters


Reset Dashboard Control Values

To reset a dashboard’s Controls to their default values, click on the Reset values button below the Control list in the Variables tab.

You can also reset the Controls by clicking on the dashboard title on the top left corner of any dashboard.

Click Dashboard title to reset Control values


Delete Controls

To delete a Dashboard Control from your dashboard, open the ellipses menu of the Control to be deleted then click Delete Filter.


Troubleshooting

Query log

If you are experiencing any formatting errors related to your Dashboard Controls, one of the best places to start troubleshooting is the query log. In the query log, you can view how Chartio translates your Dashboard Control syntax into a SQL query, and how the query changes when specific values are selected.

For more information on accessing the query log, see our query log documentation.

Executed query

To troubleshoot specific Control values used in a chart, use the Executed query view in the chart’s Data Explorer. Clicking on the Executed query option next to the Run Query button will open up the latest query ran for your chart with the actual Control values added to the query.

Executed SQL query

If your filters aren’t working as expected, see if the values are coming up as expected in the query and try modifying the filter options in your chart before running the query again. This allows you to view how the query results change based on different Control values.

Connected charts

Quickly verify if your charts are correctly connected to any of your Dashboard Controls by opening a Control’s ellipses menu then clicking Show Connected Charts. This will highlight all charts in the dashboard that are connected to that particular Control.