Hello everyone,
I need to use multiple value as a parametric filter, in a custom SQL query.
I already read the thread “Query editon : ‘In’ Clause with parameter” but the solution explained (use the “Select matching database rows”) doesn’t fit because I don’t have a table but a complex query.
Atm I run the query without filter, and then I use the “Keep rows” action, but the query took too long to extract every record. It would be great to filter the custom query with a multiple value parameter!
I’ve added to that thread a description how it can be done using the “Multiple choice” parameter type with a visual query, not custom SQL.
For custom SQL, you can also use a multiple choice parameter to specify a list of values to filter.
Then create a calculated parameter {Calculated_Industry} calculated as below:
IF len({Industry})>0 THEN
'"' & replace({Industry}, ',', '","') & '"'
The calculation takes a list of comma-delimited values from the multiple choice parameter (e.g. A,B,C) and wraps each value in double quotes (i.e. "A","B","C") to make it work in custom SQL.
Finally Insert the parameter right into the SQL query as follows:
WHERE [Industry] in ({Calculated_Industry})