CODEWITHSUNDEEP
variablesreporting-services
Niel
Niel

Reputation:

Putting multi-valued string parameters into a table in SSRS

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.

Upvotes: 1

Views: 935

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

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

Upvotes: 1

Related Questions