Putting multi-valued string parameters into a table in SSRS

Question

Hi 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. Thanks

Answer 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