Forum Discussion

Koritala's avatar
Koritala
Post Patron
1 year ago
Solved

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

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 ...
Need Help
ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

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's avatar
Koritala
Post Patron
1 year ago

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's avatar
    ajaybabuinturi
    Super User
    1 year ago

    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's avatar
      Koritala
      Post Patron
      1 year ago

      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's avatar
        v-nmadadi-msft
        Community Support
        1 year ago

        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