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,