1

I would like to put the input of a multi-valued string parameter in SSRS into a table, so I can join onto it, instead of using an in (@variable). I know how to do it in a proc, but I want to do it in SSRS itself, as sending it through to a proc limits it to 8000 characters.

Improve this question

1 Answer 1

Reset to default
1

Why not make a table of all possible values that the multivalue parameter can take and use it for your joins, limiting the rows by the multi-value parameter?

For example, let's say you want to have the total amount of invoices for your shops who are in your selected cities. You have a table of city names that you use to populate the selections of your multi-value string parameter. Your SQL for your dataset might like something like this:

SELECT CityName, ShopName, SUM(Invoice.Amount)
FROM Invoice 
  INNER JOIN Shop ON Invoice.ShopNumber = Shop.ShopNumber
  INNER JOIN City ON Shop.CityName = City.CityName AND City.CityName IN (@CityNameParam)
GROUP BY City.CityName, Shop.ShopName
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, Chris. This has actually helped me with another issue I had :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.