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

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
-- @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;
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']
}
Last updated
Was this helpful?