question

natongm avatar image
natongm asked

How to create Mulivalue filter in SSRS

I have a simple inline query that returns several cities, and I want to be able to filter resultset in SSRS by selecting a city from a multi-value list.
bids
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please mark all helpful answers by clicking on the thumbs up next to them. If any answer solved your problem, indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
opusbabu avatar image
opusbabu answered
Option 1. SELECT * FROM tableName WHERE City IN ('City1','City4','City3','City4','City5') Option 2. Use StoredProcedure with parameter like @CityNames SELECT * FROM tableName WHERE City IN (@CityNames) Call/Execute the StoredProcedure by passing the City Values to the Parameter
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you opusbabu. I only have read access to the database, so I cannot create any database objects.
0 Likes 0 ·
ruancra avatar image
ruancra answered
1.Create the function below in the database 2.In your query declare @parameter varchar(max) 3.In your query call the function in your where clause: where City IN (Select * from dbo.ufn_Split (@parameter,',')) 5.In SSRS enable 'Select Mulitple Values' for the @parameter 6.Create a second dataset which does a Select distinct City from your table create function ufn_Split ( @string nvarchar(MAX) ,@delimiter char(1) ) returns @results table (value nvarchar(MAX)) as -- This function takes two parameters; the first is the delimited string, the second is the delimiter begin SELECT @String = LTRIM(Rtrim(@String)) declare @index int declare @slice nvarchar(MAX) -- Have to set to 1 so it doesn't equal z -- Ero first time in loop set @index = 1 if @string is null return while @index != 0 begin -- Get the index if the first occurence of the split character select @index = charindex(@delimiter, @sTRING) -- Now push everything to the left of it into the slice variable if @index != 0 select @slice = left(@string,@index - 1) else select @slice = @string -- Put the item info the result set insert into @results(value) values (@slice) -- Chop the item removed off the main string select @string = right(@string, len(@string) - @index) -- Break out if we are done if len(@string) = 0 break end return end
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot @ruancra!
0 Likes 0 ·
Cool man :)
0 Likes 0 ·
@ruancra, I only have read access to the database, so I cannot create any database objects. I am going to hold on that function.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.