Dynamic SQL Charts

Creating dynamic SQL charts from user input

Overview

There are some situations where it is convenient to be able to dynamically build SQL based off of runtime information.

Dynamic SQL is achieved with an embedded scripting language called Handlebars that is interpreted before the SQL is executed by ChartSQL Studio.

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

Data Context

The data variables that are available for use within the Handlebars script are provided by ChartSQL's various features.

• User Select Lists

Handlebars Overview

Handlebars is a lightweight templating language embedded within ChartSQL Studio that allows you to dynamically build SQL queries based on runtime data. This flexibility enables customizable and responsive SQL generation tailored to user input and other conditions.

Variable Output

To display the value of a variable, simply wrap the variable name in double curly braces. This is useful for injecting dynamic data directly into your SQL query.

Example:

SELECT
product_name,
price
FROM
products
WHERE
category = '{{select-list-category.selected}}';

In this example, '{{select-list-category.selected}}` outputs the value of the variable `select-list-category.selected`, allowing the user to filter products by category interactively.

Control Flow

Handlebars provides control structures such as `#if`, `#unless`, `#each`, and `#with` to manage the logic within your templates. In SQL, these structures help conditionally include or exclude query parts.

Example:

SELECT
TRUNC(date_closed, 'MONTH') as Month,
sum(amount) as Sales
FROM
sales
{{#unless (eq select-list-channel.selected "all")}}
WHERE
Channel = '{{select-list-channel.selected}}'
{{/unless}}
GROUP BY
Month
ORDER BY
Month ASC;

Explanation:

• {{#unless}}: This is the inverse of {{#if}}. In the provided example, if the selected channel is not "all", the SQL `WHERE` clause filters results by the specified channel.

These examples offer a starting point for using Handlebars in your SQL queries. For more advanced usage, refer to the Handlebars documentation for additional functionalities and helpers.

User Select Lists

Chart SQL queries can be dynamically altered based on a user accessible 'Select List', provided by the @select-list directive.

With @select-list, you can alter the SQL query to dynamically choose different columns, table, WHERE conditions in order to create highly customizable charts.

An example of a chart with a Select List opion.

See @select-list directive for details