Multi-select Parameters in Workbooks (hand-written SQL source)

This write-up is about using multi-select parameters in a hand-written SQL (workbooks). If you are interested in using multi-select parameters in calculated fields of a workbook - then click here.

In workbooks, when you create a parameter and enable a multi-select, then place that parameter into your hand-written SQL statement, ax explained below.

Suppose I a multi-select parameter COUNTRY-PARAM , and it has 3 items selected from the listbox: Germany, Brazil and USA:

Now, let’s say that you have a hand-written SQL statement as the data source, and it looks like this:

SELECT 
ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}}, ',' ) PARAM_CSV_LIST_STRING,
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}}, ',' ) )  PARAM_STR_LENGTH,
, COUNTRY
FROM ORDERS_PRODUCTS_CUSTOMERS a
WHERE  a.COUNTRY in {{COUNTRY-PARAM}} END

Sigma will do a literal substitution of the parameter in the SQL, replacing
{{COUNTRY-PARAM}}
with
( 'Germany','Brazil,'USA')

Note that round brackets are automatically added by Sigma on both end of the list.

SELECT 
ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') PARAM_CSV_LIST_STRING,
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') )  PARAM_STR_LENGTH, 
COUNTRY
FROM ORDERS_PRODUCTS_CUSTOMERS a
WHERE 
a.COUNTRY in {{COUNTRY-PARAM}} END


Now, let’s add null handling. What if I want to display all rows when my parameter is empty?

SELECT 
ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') PARAM_CSV_LIST_STRING,
LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') )  PARAM_STR_LENGTH, 
COUNTRY

FROM SIGMASUPPORT.RETAIL_PRODUCT_SALES.ORDERS_PRODUCTS_CUSTOMERS a

WHERE 
	CASE WHEN 
    LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') ) = 0
    THEN True 
 		 ELSE a.COUNTRY in {{COUNTRY-PARAM}} END

Please note that this behavior is different from the way multi-select parameters worked in Dashboards.

NB! Please make sure that your parameter name is different, than the name of the field you are comparing it to, otherwise the SQL will break. For example:

WHERE a.COUNTRY = {{prm_COUNTRY}} // is valid, but

WHERE a.COUNTRY = {(COUNTRY}} // will NOT work

Thanks for posting this. I use Snowflake and I try to use SQL parameters that take multiple values. The {{prm-country}} does not work when I enter multiple values, it works just for a single value.
I have tried the array_construct({{prm-country}}) or array_construct{{prm-country}}but I get errors such as the

Function ARRAY_CONSTRUCT does not support ROW(VARCHAR(3), VARCHAR(3)) argument type