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.