@select-list

Update the SQL query dynamically from a dropdown list in the ChartSQL Studio UI

Example chart with a @select-list filter

Quick Reference

-- @select-list-{NAME}: option1, option2

Valid Values

List

A list of values to use in the select list options that will transform the SQL query based on the selected option.

Full Example

SQL

-- @title: Select List - Dynamic SQL query from user selection
-- @subtitle: An example SQL query which uses a dynaic SQL query from user selection
-- @formats: currency
-- @select-list-Channel: referral, coldcall, search, event
-- @chart: line
SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM sales
WHERE Channel = '{{select-list-channel.selected}}'
GROUP BY Month
ORDER BY Month ASC;

Chart

Use Cases

@select-list allows you to define a custom drop down selection that can be used as a variable within the query. This is useful when you have chart data that you want to change at the query level, including complex dynamic SQL.

@select-list allows you to create customizable reports using a single base query that the user can then select within the UI. @select-list allows you to consolidate certain queries which are very similar.

Default First Value

The value of the select list is never null, therefore your SQL query does not need to handle the empty case. The first element in the @select-list is the default value.

If you want to simulate any/null/none, have your first select value be a value like 'none' or 'any' and handle this yourself in code.

Templating Language

The templating language that processes the dynamic SQL is Handlebars.js. This allows for basic control flow (if/then/else), loops and equality checks.

-- @chart: column
-- @series: TotalSold
-- @select-list-channel: All, Referral, Organic, Paid
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
{{#if (eq select-list-channel.selected 'All')}}
	-- Do nothing we are going to return all channels
{{else}}
	WHERE Channel = '{{select-list-channel.selected}}'
{{/if}}
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

Handlebars Object Reference

The following variables are provided to the SQL script Handlebars processor that you can use in your handlebars logic.

{
    //The value selected, defaults to the first value in the list
    'select-list-channel': {
        'selected': 'All'
        'values': ['All', 'Referral', 'Organic', 'Paid']
}