I have an SSRS 2005 report that I want to enable for multi value. However, I always run into the issue with the way it passes multiple values. How exactly do I get a multi-value parameter setup and working on both the report AND SQL side? I used the MS example of checking the multi checkbox for the parameter and then using a WHERE Column IN (@MultiParam), but this doesn't work. When you select one item, or is it multiple I can't remember, it fails. I really appreciate the help!Nathon Dalton Software Developer Systems Administrator Network Administrator Blog: http://nathondalton.wordpress.com

Hi Nathon, I agree, its a little messy to use multi-parameters. There are two primary methods I use to handle this: Filters and SQL. The Filter method is easier. Filter Method: Create a multi-value parameter. Let's call it @Animals and you select Dog, Cat, Bird (or Select All) Your main Dataset, which will return a result set to your report, does not reference @Animals in it's where clause. Instead, click on Dataset Properties and select Filters In the Filter setup dialog, click on the column in your result set that corresponds to the Animal value. For the operator, select the "In" operator For the value, type in [@Animals] Done! This will "post-filter" your SQL query and only return the values that have been filtered by your multi-value parameter. Only Dog, Cat, Bird records will return to your report. The downside to this approach is that the processing occurs at the Report Server level and not by your Database server, because you are not using SQL to do the work. In many cases (most cases!) I find this the easiest and quickest way to do what you want. SQL Method: Create a multi-value parameter. Let's call it @Animals and you select Dog, Cat, Bird (or Select All). Same as the Filter Method! You will need a stored procedure for your main report Result Dataset. Let's call it sp_get_animals. sp_get_animals will take one argument, @Animals, so the calling mechanism looks like this: exec sp_get_animals @Animals When you are configuring your parameters in the Query dialog, use the following expression to define the value of your parameter: =join(Parameters!Animals.Value,",") This will create a string that looks like this: "Bird,Dog,Cat" In the body of your stored procedure, you will have to parse @Animals to pick off Bird, Dog, Cat. Once you have parsed @Animals, you can use the SQL IN clause to actually process it. This method is definitely more complicated, but it has the advantage of passing the parameters directly to SQL and allows you to take advantage of your Database Server. When in doubt, use the Filter method if you can get away with it. It has the advantage of simpler SQL, and more intuitive to other members of your team. There are many examples of the SQL method on this website, and a quick Google search will also reveal examples of using SQL to do this. But like I said, it can be messy. Good luck, and I hope this helps. --DanDJAnsc

Nathon, You would need to check this link after going thro DJ's points. this will give you an overview http://msdn.microsoft.com/en-us/library/aa337292.aspxRegards, KarthikShanth. "Mark as Answer" if this helps you!

Thanks guys! I would like to pass this parameter to the stored procedure for multiple reasons. One is that I'm already passing the other parameters. The seconds is that I don't want the overhead of transporting the entire data set just to filter out some of it at the report. SQL is very good at dealing with sets of data and I would like to leverage that ability. So, I understand all of what you're saying, Dan. However, what would be the best way to parse the string in the stored procedure? Would I parse it and then still use the IN clause? I want it to work something like WHERE FIELDNAME IN (@MultiValueParam).Nathon Dalton Software Developer Systems Administrator Network Administrator Blog: http://nathondalton.wordpress.com

Hi Nathon, Here's a URL that gives an example of what I was talking about using an IN clause: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/ssrs_multi_value_parameters This assumes that you are using SQL Server as your datasource, but the general principle is the same. If you are not using SQL Server as your source, you will probably have to use different syntax. At our site, I don't do it quite this way, but it is similar. At our site I use a stored procedure, but the stored procedure splits the incoming parameter and stores each individual element into a table designed to hold multi-values. I then use the table in a join statement to produce my final result set. My process looks like this: Collect your input values into a multi-value parameter @Animal: Bird, Dog, Cat Use an expression to map the parameter to your procedure: =join(Parameters!Animal.Value,",") Call the stored procedure: exec sp_get_animals @MyAnimal sp_get_animals takes an argument declared as @MyAnimal varchar(20000). I make it big because I want to leave enough room for all possible animals. sp_get_animals calls another stored procedure called sp_parse_multi_value: exec sp_parse_multi_value <data_name>, <data_type>, <data_string>, <delim> sp_parse_multi_value parses the data_string (in this case @MyAnimal) by splitting on every occurrence of <delim> (in this case a ",") and storing it in a row in my multi_value table. Each row of Animals in my multi_value table has a data_name of "ANIMAL" so that I know what the row represents. In addition, the data_type field tells me which column in multi_value I should use to store the value coming from my parsed data_string. I support INTEGER, STRING, REAL, and DATETIME as possible multi-values. Finally, back in sp_get_animals, I join my result set against the multi_value table that looks like this: select A.animal, A.species, A.zoo, A.city from animal A inner join multi_value M1 on (a.animal = M1.string_value) where M1.data_type = 'ANIMAL' This approach allows me to have as many multi-value parameters as I like without having to write additional SQL to parse each each multi-value. If I want another multi-value parameter, I just have to reference it in my join clause with a new correlation name (e.g., M2) I hope this makes sense. I like this approach because I have only had to write my multi-value split logic once. Now I can use multi-values whenever I want. Once again, stick with filters when you can! They are a lot simpler to implement! -DanDJAnsc

I wrote a SplitString function in .NET implemented through SQL CLR that accepts a SqlString value and a delimiter and then returns a table containing a record for each delimited item. I think this will probably work for what I need. This will also allow me to filter on the SQL Server instead of pulling all the data back to the report just to dump most of it. Thanks!Nathon Dalton Software Developer Systems Administrator Network Administrator Blog: http://nathondalton.wordpress.com

I have implemented the reporting services with java EE application. Now I am facing the challenge of passing the parameters to the servers and calling the reports based on the search parameter. Its easy to search the report server based on the report name but difficult to search and display the report based on the particular item inside the report.

Hi, You can also create an internal report parameter in which you define the default and available value based on the input multi-value parameter, using an expression as this: =JOIN(Parameters!Animals.Value, ",") You'll then be able to use WHERE Column IN (@Animals) in your query, without using report filters or stored proc. Not @InternalAnimals the internal report parameter derived from @Animals, but the same @Animals in which the user has selected one or many "animal", oddly enough. Regards, Eric

Hi Nathon, when you are using a stored procedure as the datasource it is still a bit cumbersome to get the multi-param working. However, using the mentioned WHERE field IN (@MultiParam) directly in a select statement in the report datasource should work. You can run into some strange effects regarding case-sensitivity but when you supply the multiparam possible values also out of the database this never causes trouble. Regards, Vulcan900

Hi All, I must be missing something here. How can passing the @MultiParm directly to the IN clause work? The IN clause expects a list of values formatted like this: WHERE my_animal IN ('Bird', 'Dog', 'Cat') The JOIN expression will create a string like this: 'Bird,Dog,Cat' If you pass this to your WHERE clause, it will not give you the results you expect. It's even worse if your MultiParm expects INTEGERs or DATETIMEs. Please give an example of how you would set up directly passing the @Mult-Parm to an IN clause. Thanks, --DanDJAnsc

Hi Dan... Did you ever find a solution for this?.. i'm currently struggeling with the same problem.. - Mikkel

Here's a sample of the SQL stored procedure that I use to get the dataset: Create proc [dbo].[spStoredProcName] @supervisor varchar(max) as begin create table #tempSups ( Supervisor varchar(62) ) declare @i int select @i = 0 select @i = charindex(',',@supervisor ) begin if @i <> 0 while @i >0 begin insert into #tempSups values (ltrim(rtrim(left(@supervisor, @i-1)))) select @supervisor = right(@supervisor, len(@supervisor) - @i) select @i = charindex(',', @supervisor) end end insert into #tempSups values (ltrim(rtrim(@supervisor))) From there, simply join your query to the temp table