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

[Koritala]

Hi,

I was trying to write custom SQL with ODBC connector for redshift view.

In fx option in report builder, when I write my redshift SQL like, select * from sales where region IN @regionparameter

and sales_date >=@startdateparameter and sales_date <=@enddateparameter, it is throughing error message saying syntax is not proper.

Could anyone please let me share with redshift view how can we write exact SQL code to pass the multiple values in slicer with date range date picker in Report Builder.

Thanks,

Srinivas.

[ajaybabuinturi]

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.

[Koritala]

Hi Ajay,

Can you please share the SQL code where I need to pass the product and caegory parameters along with region parameter. 

Thanks,

Srinivas.

[ajaybabuinturi]

Hi @Koritala,

Try with below code

select * from sales
where region in (select trim(value) from regexp_split_to_table(@regionparameter, ',') as value) 
and product in (select trim(value) from regexp_split_to_table(@productparameter, ',') as value)
and caegory in (select trim(value) from regexp_split_to_table(@categoryparameter, ',') 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.

[Koritala]

Hi Ajay, I tried your code, but unfortunately, it did not work. For everyone reference, here I am attaching the screenshots.. please advise anything I missed. I have tried to pass a single selection in my slicer in report bilder as per screenshot.

[v-nmadadi-msft]

Hi @Koritala ,

As a troubleshooting step,
Please check if the parameter is configured correctly in report server itself, test it out by running on the report directly.

Additionally to check if the issue is related to the particular parameter or the syntax of the code, please run some other simple SQL code with the same parameter and test it out.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[Koritala]

Hi nmadadi,

I have configured the parameter 100% correctly only.

My undxerstanding is we are not able to query which is not as per the redshift db norms.  My SQL is very simple. We are failing to write the proper syntax for parameters.

If you have any questions, plz let me know.

Thanks,

Koritala

[v-nmadadi-msft]

Hi @Koritala  ,

1) You might not be able to configure Parameters as both "Multi-value" and "Allow null".
"Multi value" and "Allow blank" is an allowed combination but Null is treated differently.
source: Power BI Report Builder - Power BI | Microsoft Learn
Check if that is causing the error you are facing.


2) Also verify if the parameter value you are trying to pass are of the correct data type and matches the source columns data type.

3) To resolve the error and validate the query syntax, replace the parameter  @lfundcode with a hard-coded comma-separated string directly in the query:

 FROM regexp_split_to_table('ABC123,DEF456', ',') AS value

This allows you to confirm that the logic for filtering level_one_fund_code works as intended.


If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[Koritala]

Hi nmadasi,

Thanks for your response.

I have configured the parameters data types properly. But still not working. Can you send me a sample rdl file for reference? 

[v-nmadadi-msft]

Hi @Koritala ,
As per this document not all data sources support parameters

Source: Report parameters in Power BI Report Builder - Power BI | Microsoft Learn

If you still feel the issue is important and not related to source. Please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious. 

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you

[v-nmadadi-msft]

Hi @Koritala ,
We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[Koritala]

Hi nmadadi, issue not get resolved.

I have followed all steps what people has mentioned in this thread. If anyone tried with redshift db table this is issue, I would appreciate if they can share .rdl file here. Because many are giving sample code on their assumptions. May be that is the reason it took this much time. If you can share sample file which worked from your end, it will be helpful.

Thanks for your follow-up.

[Koritala]

Hi Ajay,

Thanks for your response.

I have a question in your provided code What does it mean by "trim(value)". Do you want me to give as similar your code?

Thanks,

Koritala

[ajaybabuinturi]

Hi @Koritala,

The value is the default column name returned by Redshift’s STRING_TO_TABLE function. STRING_TO_TABLE(@regionparameter, ',') turns a comma-separated string (like 'East,West,North') into a table, where each row contains one region. That temporary table has one column, and it's automatically named value.

[DataNinja777]

@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,