I found a possible solution for MikkoM’s thread Parameter issue - Multi-select not available, but since it’s locked I thought I’d make a separate thread.
In short, I was similarly struggling to create a parameter with cardinality set to “Multi-select”, which won’t seem to save properly after clicking “Reconnect”.
What worked for me now is that I had to create the parameter and use it in the custom query right away. If I were to only create the parameter without referencing it in the query, it would fail to save upon reconnecting.
Toy example:
WITH
selected_products AS (
SELECT * FROM UNNEST(SPLIT('apple,banana,carrot', ',')) PRODUCT -- (1)
),
t_data AS (
SELECT '001' AS STORE_ID, 'apple' AS PRODUCT, 5 AS QUANTITY UNION ALL
SELECT '001' AS STORE_ID, 'banana' AS PRODUCT, 3 AS QUANTITY UNION ALL
SELECT '001' AS STORE_ID, 'banana' AS PRODUCT, 1 AS QUANTITY UNION ALL
SELECT '001' AS STORE_ID, 'carrot' AS PRODUCT, 6 AS QUANTITY UNION ALL
SELECT '001' AS STORE_ID, 'wrench' AS PRODUCT, 3 AS QUANTITY UNION ALL
SELECT '001' AS STORE_ID, 'whistle' AS PRODUCT, 3 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'apple' AS PRODUCT, 6 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'banana' AS PRODUCT, 1 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'carrot' AS PRODUCT, 2 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'carrot' AS PRODUCT, 7 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'stick' AS PRODUCT, 8 AS QUANTITY UNION ALL
SELECT '002' AS STORE_ID, 'pen' AS PRODUCT, 4 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'apple' AS PRODUCT, 3 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'apple' AS PRODUCT, 7 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'banana' AS PRODUCT, 5 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'carrot' AS PRODUCT, 9 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'wine' AS PRODUCT, 2 AS QUANTITY UNION ALL
SELECT '003' AS STORE_ID, 'glass' AS PRODUCT, 4 AS QUANTITY
),
t_data_modified AS (
SELECT
STORE_ID,
IFNULL(S.PRODUCT, 'miscellaneous') PRODUCT,
QUANTITY
FROM t_data T
LEFT JOIN selected_products S USING (PRODUCT)
)
SELECT
STORE_ID,
PRODUCT,
SUM(QUANTITY) QUANTITY
FROM t_data_modified T
GROUP BY STORE_ID, PRODUCT
Let’s say (1) is a placeholder, which I’m planning to replace with the parameter afterwards. At this point, if I were to create the parameter and reconnect right away, the multi-select option doesn’t get saved.
Instead, if I create the parameter and then reference it in the query before reconnecting:
WITH
selected_products AS (
SELECT * FROM UNNEST(@products) PRODUCT
),
/* ... */
This time, it works!
Note that if I tried to edit the parameter this time, the dialog appears over the custom query screen instead.
Fun fact: Just having the parameter mentioned in a comment seems to do the trick too, for some reason.
If any of you have the same issue, try this and let me know if it works for you or not.