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,

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

      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.

  • 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.