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 database 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. 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";

Case statments in categorical dropdowns

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.

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

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

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

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

Adding a case statement to your SQL Mode query