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:

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 different document types from our Dundersign Demo Data data source. One of them is “img”, and we want to create a CASE statement to have this visible as “image” in our Dropdown. We’ll also need to put the CASE statement in our connected chart so they’re both using the value “Image” when filtering.

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

select case d.type
	when 'img' then 'image'
	else d.type
	end as "Type"
from "dundersign"."documents" as d
group by "Type"
order by "Type" asc
limit 1000;

In the Visual SQL interface

In the Data Explorer interface

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 are 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 documents of type “img” 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 Id column from the Documents table as in the previous example, where DOC_TYPE is the name of our Dropdown:

select count(d.id) as "Count of Documents"
from "dundersign"."documents" as d
where ({DOC_TYPE.IN('
	case d.type
	when \'img\' then \'image\'
	else d.type
	end'
)})

In the Visual SQL interface

In the Data Explorer interface

Add a CASE statement to a Dropdown

Add a CASE statement within a SQL Mode chart query