Documentation Controls and Variables FAQs

Use CASE statements with Dropdowns

CASE statements use conditional logic to alter query results or to perform calculations on your query results. It’s a flexible SQL and SQLite expression that can be used anywhere a query is set in Chartio. It can also be combined with other functions or formulas.

Below are a few use cases where you could use this expression in Chartio:

Add a CASE statement to a Dropdown

Dropdowns on dashboards are set up using data source queries. If results within your data source aren’t formatted how you’d like them to be displayed within your Dropdown, you can edit your values using a CASE statement.

Let’s say we have a Dropdown with various marketing campaign IDs from our SaaS Company Demo Data data source. One of them is “FB”, and we want to create a CASE statement to have this visible as “Facebook” in our Dropdown.  We’ll also need to put the CASE statement in our connected chart so they’re both using the value “Facebook” when filtering.

Here’s what our categorical Dropdown query would look like:

select case m.campaign_id
           when 'FB' then 'Facebook'
           else m.campaign_id
       end as "Campaign Id"
from "public"."marketing" as m
group by "Campaign Id";
In the Visual SQL interface
CASE statement in Dropdown query - Visual SQL
In the Data Explorer interface
CASE statement in Dropdown query - Data Explorer

Add a CASE statement within a SQL Mode chart query

You can also add a CASE statement directly in your SQL Mode queries within your chart to edit how your results will be displayed or within your filters.

As we mentioned in the previous section, you need to update your connected chart’s query to properly filter on your Dropdown if you created it using a CASE statement.

In this example, we add a CASE statement for our Dropdown within the filter to make sure the FB campaigns are included in our results using the format below:

where {CATEGORICAL_DROPDOWN_NAME.IN('CASE statement here')}

Note: Any other single quotes inside the CASE statement in parentheses need to be escaped with a backslash.

Here’s what our chart query looks like using the same Campaign Id column as in the previous example:

select count(m.campaign_id) as "Count of Campaigns"
from "public"."marketing" as m
where (
  {CASE_TEST.IN('case m.campaign_id when \'FB\' then \'Facebook\' else m.campaign_id end')}
)
In the Visual SQL interface
CASE statement for categorical Dropdown in chart query - Visual SQL
In the Data Explorer interface
CASE statement for categorical Dropdown in chart query - Data Explorer