Reference workbook control values in SQL statements

When you write SQL statements in Sigma, you can reference the value of a control in your SQL statement by wrapping the control ID in curly brackets:

{{my-control-id}}

You can find the control ID in the Settings tab for a control. For more details about control IDs, see Reference control values in a formula.

The exact syntax depends on the output type of the control:

• Single value (Most control types)
• Min/max values (Number range, range slider, or date range)
• Multiple values (Multi-select list control)
• Hierarchical values (Hierarchy control)

Limitations and warnings

• If changing the value of a control results in a statement that queries a table with a different schema, the query produces different columns than expected, resulting in errors. See Return rows based on a schema text selector for an example that would be impacted by this behavior.

Reference single value control output in SQL

You can reference the selected or specified value in a specific control (the output) in a SQL statement. For a control that outputs a single value, use the syntax as follows.

Supported control types

• Single select list
• Text input
• Text area
• Number input
• Date
• Segmented
• Drilldown
• Slider
• Checkbox
• Switch
• Top N

Syntax

To reference the control value in a formula, enclose the control ID in double curly brackets and use the following syntax: {{New-Control-ID}}

Examples

To filter the table PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA for orders with a quantity greater than the value specified in a number input control with control ID number-control, use the following example SQL:

SELECT *
from EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
where QUANTITY > {{number-control}}

To filter the table FLIGHTS for flights that were diverted based on the value of a checkbox control with control ID checkbox-control, use the following example SQL:

SELECT *
FROM FUN.FAA.FLIGHTS
WHERE DIVERTED = {{checkbox-control}}

In this example, when the checkbox is selected, it returns True. When the checkbox is not selected, it returns False. The column DIVERTED in the FLIGHTS table is a logical column that contains True or False values.

Reference range or slider control values in SQL

You can reference the selected or specified values in number range, date range, or range slider controls in a SQL statement. For a control that outputs a range of numeric values, use the syntax as follows.

Supported control types

• Number range
• Range slider
• Date range

Syntax

To retrieve the minimum value of a number range or range slider control, use the following syntax:

{{#formula [Control-ID].min}}

To retrieve the maximum value of a number range or range slider control, use the following syntax:

{{#formula [Control-ID].max}}

To reference the start value of a date range control, use the following syntax:

{{#formula [Control-ID].start}}.

To reference the end value of a date range control, use the following syntax:

{{#formula [Control-ID].end}}.

Examples

To filter the table PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA for orders with a quantity between the minimum and maximum values selected on a slider control with the control ID slider-control, use the following example SQL:

SELECT *
from EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
where QUANTITY between {{#formula [slider-control].min}} and {{#formula [slider-control].max}}

To filter the table PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA for orders between the start and end date values selected on a date range control with the control ID current-date-range-control, use the following example SQL:

SELECT
  *
FROM
  sigma_element ('PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA')
WHERE
  "Date" BETWEEN {{#formula [current-date-range-control].start}} AND {{#formula [current-date-range-control].end}}

Reference multiple values from a multi-select list in SQL

You can reference the selected or specified values in a specific control (the output) in a SQL statement. The values in a multi-select list are outputted as an array. For example:

('apples','bananas','oranges')

Supported control types

• List values
• Legend

Syntax

To reference the control value in a query, enclose the control ID in double curly brackets and use the following syntax: {{my-control-id}}

To reference the control value in a formula, such as to use the ArrayLength function to count the number of selected values, use the following syntax: {{#formula ArrayLength([my-control-id])}}

If the control value is output with single quotation marks, you can remove these quotation marks by prepending the keyword #raw before the control ID:

{{#raw my-control-id}}

🚩

Sigma does not recommend using the #raw configuration value for use cases outside of dynamic schema selection. When used improperly, it can pose security risks, such as allowing users to bypass row-level security.

The exact syntax might be different for your connection depending on how your particular data platform interprets an array data type.

• For Snowflake, see Array in Semi-structured data types in the official Snowflake Documentation.
• For Databricks, see ARRAY type in the official Databricks documentation.
• For BigQuery, see Work with arrays in the official Google Cloud BigQuery documentation.
• For Amazon Redshift, see SUPER type in the official Amazon Redshift Database Developer Guide.

For example:

select
  *
FROM
  APPLICATIONS.GOOGLE_ANALYTICS.EVENTS
WHERE
  TRAFFIC_SOURCE IN {{TRAFFIC_SOURCE}}
limit
  10

For a more detailed example, see Return rows depending on the value of a multi-select control.

Return rows depending on the value of a multi-select control

If you have a list values control called City and you run this query to filter the STATIONS table based on the values selected in the control. The example query returns all rows where the CITY column value is selected in the City control. If there are no cities selected in the City control, all rows are returned:

SELECT
  *
FROM
  EXAMPLES.BIKES.STATIONS
WHERE
  CASE
    WHEN {{#formula ArrayLength([City])}} = 0 THEN True
    ELSE CITY in {{City}}
  END

Return rows based on a schema text selector

If you have a single-select or text control called schema-param that has a valid selection (a schema in your connected data platform), and the STATIONS table exists in the specified schema, the following example SQL returns all columns from the STATIONS column in the specified schema:

SELECT *
FROM {{#raw schema-param}}.STATIONS

When using this parameter to swap table, schema, or database names, the column names referenced in the SQL must be identical.

For more examples, see Injecting multi-select parameters in Custom SQL article in the Sigma Community.

Use formulas with control values in custom SQL

You can use formulas with controls in custom SQL. This allows you to cast control values as text, concatenate control values, and reference the contents of a control in a formula.

The following functions are supported in formulas in custom SQL::

• ArrayLength
• Text
• Concat

The following operators are also supported:

• +
• &

Syntax

To reference the control value in a formula, enclose the control ID in double curly brackets and use the following syntax:

{{#formula function_name([Control-ID])}}

Count the number of selected values in a multi-select list in SQL

To count the number of selected values in a multi-select list, use the following syntax:

{{#formula ArrayLength([Control-ID])}}

For example, if you have a list values control called Products, you can use the following SQL to return the number of selected products:

SELECT {{#formula ArrayLength([Products])}}

Convert a control value to text in SQL

To convert a control value to text, use the following syntax:

{{#formula Text([Control-ID])}}

For example, if you have a number input control called Number-Control, you can use the following SQL to cast the control value as text:

SELECT {{#formula Text([Number-Control])}}

Concatenate control values in SQL

To concatenate control values, use the following syntax:

{{#formula Concat([Control-ID-1], [Control-ID-2], ...)}}

For example, if you have two text input controls called First-Name and Last-Name, you can use the following SQL to concatenate the control values:

SELECT {{#formula concat([First-Name], ' ', [Last-Name])}}

Use multiple formulas with control values in SQL

You can use multiple formulas with control values in a single SQL statement. In the example below, the day-in-november control is a number range control that allows users to select a range of days in November of 2024. The SQL statement concatenates the starting and ending days as text with the month and year to create a date range.

SELECT *
FROM EXAMPLES.PLUGS_ELECTRONICS.PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA
WHERE
DATE BETWEEN 
  to_date({{#formula concat('2024-11-', text([day-in-november].min), ' 00:00:00')}})
AND
  to_date({{#formula concat('2024-11-', text([day-in-november].max), ' 23:59:59')}})

This example combines the concat and text functions with the .min and .max syntax to create a date range. Each range uses only one block of the {{#formula ...}} syntax, as only one is needed per output.

Reference hierarchical values from a hierarchy in SQL

Referencing the selected values of a hierarchy control in a SQL statement is not yet supported. See Hierarchy control