Reporting Services Multi Value Parameter
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:
April 2nd, 2010 2:44pm

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
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2010 4:24pm

Nathon, You would need to check this link after going thro DJ's points. this will give you an overview, KarthikShanth. "Mark as Answer" if this helps you!
April 2nd, 2010 11:32pm

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:
Free Windows Admin Tool Kit Click here and download it now
April 5th, 2010 11:50am

Hi Nathon, Here's a URL that gives an example of what I was talking about using an IN clause: 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, 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
April 5th, 2010 1:53pm

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:
Free Windows Admin Tool Kit Click here and download it now
April 5th, 2010 6:32pm

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.
April 6th, 2010 1:03am

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
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2010 8:07am

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
April 8th, 2010 9:48am

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
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2010 10:32am

Hi Dan... Did you ever find a solution for this?.. i'm currently struggeling with the same problem.. - Mikkel
June 8th, 2011 5:06am

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
Free Windows Admin Tool Kit Click here and download it now
June 15th, 2011 1:49pm

I also have this problem, I love the way that link shows only one example and it doesn't even work
January 20th, 2012 2:41pm

Do I need to write a stored procedure to parse the tokens of the multi select parameter? I've tried and tried to use the IN in the expression but it either blows up saying it can't work on the expression or it just processes the first element in the multi select array, I will call this parameter an array, but most of the new developers are scared of this word.
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2012 2:43pm

OMG microsoft has created a monster!! you need to write complex split statements in .net to tokenize the array when expressions in SSRS are not created to work for these data structures!!! I refuse to write a stored procedure everytime I want to work with a multi select parameter
January 20th, 2012 2:46pm

Hi EveryOne ! Its a little bit late but I want to share my solution. 1. Dataset Window a. Provide the parameters for your report and your store procedure. b. In this case we will provide @indate and @num both for date and number control of your item. This will be defined in your main datasource. c. Create a text query that will filter Todays numcontrol with the date provided in a Temporary Table. This will be done in the Dataset window as a simple select statement as text in the command type. Select Numcontrol from Values where ValueDate = @ indate This will be your dynamic checkbox selection in the Report Viewer. 2. Activate Report parameter inside “Report Parameter” Query Window a. in Properties choose string b. check the Multi-value checkbox In Available Values and Default Values: Select radiobutton From query and Chose the name of the Dataset from step 1.c. Press ok. 3. Use Function in SQL SERVER named fnSplitCSV. tHIS ONE I FOUND IN GOOGLE . lol! 4. Modiy your store procedure in the where clause WHERE T0.i_numcontrol_valor in(SELECT * FROM dbo.fnSplitCSV (@num)) 5. Done!!  Hope it helps!Himilce Jackson
Free Windows Admin Tool Kit Click here and download it now
January 23rd, 2012 1:46pm

Hi Dean0780. Check my solution its works beautifully!Himilce Jackson
January 23rd, 2012 1:51pm

I am using the JOIN function in the parameter filter to generate an xml string and then using openxml in the stored procedure. This has a bit more overhead than the other solutions but is also very easy to write. I have done my best to outline my solution below. Define your stored procedure parameter as xml or varchar(max). In my case it is named @pXmlAccounts. Define your multivalued parameter. In my case it is Accounts.Open the data set designer for your data set.In the parameter filter for your xml, set it to something similar to ="<accounts><account accountnum=" & Chr(34) & JOIN(Parameters!Accounts.Value, Chr(34) & "/><account accountnum=" & Chr(34)) & Chr(34) & "/></accounts>". This will provide you with an xml representation of the selected values.In your stored procedure, open the xml document using sp_xml_preparedocument. Use INNER JOIN OPENXML() to only include the selected values. SELECT AT.* FROM AccountTest AT INNER JOIN OPENXML(@xmlAcctHndl, '/accounts/account') WITH(AccountNum varchar(12) '@accountnum') AS xmlAccount ON xmlAccount.AccountNum = AT.AccountNum I hope that this helps someone. It is a fairly easy solution to implement and is much easier than writing a large string splitting routine in TSQL. David
Free Windows Admin Tool Kit Click here and download it now
February 27th, 2012 1:37pm

Hi It's much easier than using join/split etc. Just set your parameter as multi value and then use your @parameter in the IN clause. eg: WHERE (division IN (@parDivision)) In the parameters for the dataset are; @parDivision = Parameters!parDivision.Value (no joins etc needed)
March 19th, 2012 8:59pm

Hi, I've just been working on a SSRS report and one of the params had to be multi-value... and unfortunately they had to be strings, not integers. If they were strings I could use the JOIN function in SSRS to make a comma separated list and feed that into the DB and use some dynamic SQL to build an IN clause around that string... but they were strings so I had to place quotes around them before my dynamic sql could work... I was going to go down the fnSplitCSV path instead, when it occoured to me that I could put single quotes around each value BEFORE it hit the DB, then it would be in the correct format for an IN clause in dynamic sql without any further parsing... So, in the parameter value field for the main query SQL I used this expression: ="'" + Replace(Replace(Join(Parameters!ParmNameList.Value, "~"), "'", "'''"), "~" ,"','") + "'" This is a modification of the normal Join(Parameters!ParmNameList.Value, ",") expression used when the values are all integers. Firstly I used ~ to separate the parameters, incase any of them included a quote. If the ~ character may exist in a parameter value, another char could be used. The inner Replace finds single quotes "'" and replaces them with escaped quotes (three single quotes) so any values with a single quote won't break the string. The outer replace replaces the ~ delimiters with ' , ' so they look like comma delimeted quoted values. Wrap more single quotes around the lot and we're done.
Free Windows Admin Tool Kit Click here and download it now
May 9th, 2012 7:10pm

DJ, I was able to get my SSRS report to work in BIDS using the filter method described above. When I deploy the report it no longer works do you have any suggestions as to why this may be? Thanks John
May 21st, 2012 1:21pm

wow, thanks so much... doesn't make any sense at all, BUT IT WORKS :)
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2012 5:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics