Redshift Native SQL is not supporting to pass the multiselect parameters in report builder

Koritala ,

Redshift does not support directly using multiselect parameters in the form IN (@param) when used in Report Builder with the ODBC connector. The issue is that Report Builder passes multivalue parameters as a single comma-separated string, but Redshift expects a proper list for the IN clause. This mismatch leads to syntax errors. To work around this, you can split the comma-separated string into individual values using regexp_split_to_table, and then join that result to your main query.

Here is how the SQL should be written in your Report Builder custom SQL query:

WITH region_split AS (
    SELECT TRIM(value) AS region
    FROM regexp_split_to_table(:regionparameter, ',') AS value
)
SELECT *
FROM sales s
JOIN region_split r ON s.region = r.region
WHERE s.sales_date >= :startdateparameter
  AND s.sales_date <= :enddateparameter

Make sure in Report Builder that @regionparameter is configured as a multivalue parameter, and it will be passed as a string like 'East,West,North'. The regexp_split_to_table function breaks this string into rows, which can then be joined with your sales table. Also, be sure to replace @ with : for parameter names when using ODBC with Redshift, as the @ symbol is not always recognized correctly in native SQL execution. This approach allows you to dynamically filter based on multiple selected regions and a date range.

Best regards,

Hi Koritala,

I am assuming regionparameter as a text parameter that is a comma-separated string of values ('East','West','North'). regionparameter is a multi-value parameter, and SQL doesn't directly accept it in the IN clause via @parameter syntax.

Can you try with below updated SQL code. Let me know if you have any questions.

select * from sales
where region in (
    select trim(value)
    from regexp_split_to_table(@regionparameter, ',') as value
)
and sales_date >= @startdateparameter and sales_date <= @enddateparameter

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.