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