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

SQL Runner
1

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:

image
image988×1166 94.8 KB

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

image
image1656×1510 275 KB

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

1 Like
2
3
4
5
6
7
8
9
10
11
12
13

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

14

Have you tried using #raw in the parameter reference? i.e., {{#raw prm-country}}

15

Thanks @yuri,
regarding:

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

When I select null as argument I would expect to filter on null values only, but at the moment I get the same result as when I do not select any values in the argument:

Sigma-multiselect-argument-null
Sigma-multiselect-argument-null1646×494 11.2 KB

So, as a workaround, I have currently disabled the Show Null option and added a dummy entry to the list of values:

Sigma-multiselect-argument-null-2
Sigma-multiselect-argument-null-21049×892 43.2 KB

and changed my WHERE clause in

  CASE
    WHEN 
      LEN(ARRAY_TO_STRING(ARRAY_CONSTRUCT{{COUNTRY-PARAM}},',') ) = 0 THEN True 
    WHEN 
     ARRAY_CONTAINS('NULL'::variant, ARRAY_CONSTRUCT{{COUNTRY-PARAM}}) THEN (a.COUNTRY IS NULL OR a.COUNTRY in {{COUNTRY-PARAM}})
    ELSE a.COUNTRY in {{COUNTRY-PARAM}}
  END

I hope it can be useful to someone else :wink:

1 Like