Hello,I am using SSRS 2008 to build a report that allows you to select multiple values in the drop down list. I have three datasets where I am using to pass multi-value parameters. In the property of each parameters I have checked the," Allow Multiple Values" and the drop down allows me to select more than one value. Also, I have set up the where clause of my stored procedure as such: WHERE (j.SpecialtyID IN (@SpecialtyID)) AND (j.StateID IN (@StateID)) AND (a.JobStatus IN (@Status)) However, it is not working properly and I keep getting errors. Does anyone know how to resolve this issue? Thanks Kajoo

Hi Kajoo,it passes selected multi values string into comma separted text, So in your SQL you have create TableValued User defined function which accepts your selected text and split those values into rows return as table then you need match SpecialityID with user defined function.

Hi Kajoo,it passes selected multi values string into comma separted text, So in your SQL you have create TableValued User defined function which accepts your selected text and split those values into rows return as table then you need match SpecialityID with user defined function. Here is the example for split multiple values.CREATE FUNCTION SplitParameterValues(@InputString NVARCHAR(max), @SplitChar CHAR(5))RETURNS @ValuesList TABLE(param NVARCHAR(255))ASBEGINDECLARE @ListValue NVARCHAR(max)SET @InputString = @InputString + ‘,’WHILE @InputString ”BEGINSELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(‘,’, @InputString)-1) INSERT INTO @ValuesListSELECT @ListValueSELECT @InputString = SUBSTRING(@InputString, CHARINDEX(‘,’, @InputString) + 1 , LEN(@InputString) – CHARINDEX(‘,’, @InputString))–select @InputStringEND RETURN ENDAnd in your caseJ.SpecialityID iN(Select Param From dbo.SplitParameterValues(@SpecialityInd , ',')) ORYou can inner join Table Values user defined Table Valued Function dbo.SplitParameterValues(@SpecialityInd , ',) on SpecialityID. and this works for any multivalued parameter.Regards,Balwant.

Hello,Thanks for taking the time to respond to my question.I don't follow the last part where you state you can inner join by the user defined fuction:"You can inner join Table Values user defined Table Valued Function dbo.SplitParameterValues(@SpecialityInd , ',) on SpecialityID. and this works for any multivalued parameter." Please explain. ThanksKajoo

Hi,'Steps you have done are fine.Can you please post the error you are getting?-JayeshThanks, Jayesh > Vote for answer if it helps you.

Hello,Thanks for taking the time to respond to my question.I don't follow the last part where you state you can inner join by the user defined fuction:"You can inner join Table Values user defined Table Valued Function dbo.SplitParameterValues(@SpecialityInd , ',) on SpecialityID. and this works for any multivalued parameter." Please explain. Thanks Kajoo i.e.You can write following sort of query in your sp as follows select columnList... From TableName t inner join dbo .SplitFunctioName(@MultiValueString , ',') as filter on t.filtercolumnid = filter.value Regards, Balwant.

Hi,Try with below where conditionWHERE ',' + @SpecialtyID + ',' LIKE '%,' + J.SpecialtyID + ',%' AND ',' + @StateID + ',' LIKE '%,' + J.StateID + ',%' AND ',' + @Status + ',' LIKE '%,' + A.JobStatus + ',%'Rajesh Jonnalagadda http://www.ggktech.com

Hello, Thanks again!!!I tried both approaches, creating a function suggested by Balwant and the where clause which was suggested by Rajesh. They both seem to be passing the multi-value parameters into the stored procedure but for some reason when I run the report it does not return any data. I opened a trace (SQL Profiler) and captured the execute statement and ran it and it worked. Meaning when I executed the captured stored procedure in management studio (as illustrated below) it retuned data. exec sp_rpt_MavericksStaffCareJobReport @Specialty = N'Anesthesiology,Burn Surgery,Cardiology', @State = N'California,Colorado', @Status = N'Inactive' However, it does not work when I run the report. I don’t get any errors… It just does not return data. Any ideas why? Does SSRS 2008 support passing multi-value parameters? Thanks guys Kajoo

Hi Kajoo,Please check this link http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/ for the detailed steps on how to solve such this issue.thanks,Jerry

Hello, Thanks again!!!I tried both approaches, creating a function suggested by Balwant and the where clause which was suggested by Rajesh. They both seem to be passing the multi-value parameters into the stored procedure but for some reason when I run the report it does not return any data. I opened a trace (SQL Profiler) and captured the execute statement and ran it and it worked. Meaning when I executed the captured stored procedure in management studio (as illustrated below) it retuned data. exec sp_rpt_MavericksStaffCareJobReport @Specialty = N'Anesthesiology,Burn Surgery,Cardiology', @State = N'California,Colorado', @Status = N'Inactive' However, it does not work when I run the report. I don’t get any errors… It just does not return data. Any ideas why? Does SSRS 2008 support passing multi-value parameters? Thanks guys Kajoo Hi Kajoo,Dont you think there is problem where you passing the parameter value as per your original thread you are comparing SpecialityID, StateID and Status but as your captured SP, Report Returns Speciality, State and Status so I think your report should pass id's not description. You can change this behaviour by changing ValueField and LabelField.Regards,Balwant.Failure in Life is failure to try...

Thanks guys!! It is working just fine right now. Balawant, I had changed the parameters in my stored procedure from INT (SpecialtyID) to Varchar(500) (Specialty). So I was passing the correct values. However it was not working correct. However, I deleted the entire report and built it again, but this time I used INT (ID) instead of Varchar(Description) and added the expression below to my parameter in the report side and is working just fine. =JOIN(Parameter!SpecialtyID.Value,”,”) Thanks Kajoo

I took Jerry Nee's suggestion to check this site (http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/) and was able to pass multi-value parameters to a stored procedure and get all the records (filtered correctly) displayed in my report. In fact, I was able to create two multi-value parameters and filter the dataset down according to both. It's reusable and works very well. Here's what I used in my dataset report query, which is actually a stored procedure, and it works like a charm: SELECT * FROM dbo.dimCustomers WHERE City IN (SELECT Val FROM dbo.udf_StringToTable(@MultiValCity, ',', 1)) AND State IN (SELECT Val FROM dbo.udf_StringToTable(@MultiValState, ',', 1))