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)

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 formula, enclose the control ID in double curly brackets and use the following syntax: {{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 cloud data warehouse (CDW) 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 multi-select control called City and you run this SQL on a Snowflake connection, the following example query returns all rows where the CITY column value is listed in the selection for 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  
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{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 CDW), 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.