SQL Server Reporting Services Using Multi-value Parameters

By:   |   Comments (45)   |   Related: > Reporting Services Development


After working with SQL Server Reporting Services ( SSRS ) for a time, eventually a report user will want to select more than one value for a parameter. How does SSRS handle multi-value parameters?


Allowing users to select multiple values from a parameter list is a great feature in SSRS; it allows report consumers more selection flexibility when running reports. However, how the parameters are passed to the dataset (or report object filters if used) varies depending on if the datasource is based on T-SQL embedded in a dataset within a report or if the data is passed via a stored procedure. Furthermore, once the report is run, a good practice is to display the selected parameter list somewhere within the report. The below instructions will convey the various techniques needed to utilize multi-value parameters. Finally, we will cover using a filter based multi-value parameters.

Embedded Parameters

Of the various options, passing multi value parameters to an embedded query is the less complex of the two methods. Utilizing this method, the report designer just needs to write a normal query, and utilize the "IN" key word in the criteria section of the query and then refer to the multi-value parameter using the @parameter name syntax within the parentheses portion of the IN statement. Using an AdventureWorks database and report example, the below code, inserted into a report dataset, notates the required syntax. This syntax should be somewhat standard to many of you who write T-SQL on a daily basis.

SELECT P.FirstName + ' ' + P.LastName AS Employee
    ,DATEPART(Year, SOH.OrderDate) AS Year
    ,DATEPART(Month, SOH.OrderDate) AS MonthNumber
    ,DATENAME(Month, SOH.OrderDate) AS Month
    ,SUM(DET.LineTotal) AS Sales
FROM Sales.SalesPerson AS SP
INNER JOIN Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID
INNER JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
INNER JOIN HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID
INNER JOIN Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
WHERE (YEAR(SOH.OrderDate) IN (@ReportYear))
GROUP BY P.FirstName + ' ' + P.LastName
    ,DATEPART(Year, SOH.OrderDate)
    ,DATEPART(Month, SOH.OrderDate)
    ,DATENAME(Month, SOH.OrderDate)

Next we will setup the parameter to accept multiple values. In the below example, a parameter called @ReportYear is already created, so right mouse clicking on the parameter (Report Year in the below example ) and selecting Parameter Properties will open the Report Parameter Properties window. Now check the Allow multiple values option. If you are setting up a new parameter, right mouse click on Parameters and then select New Parameter.

parameter spec
parameter property

Subsequently, we will define the values to be used for our parameter list; this list will be the values presented to the report consumer. First, we define a dataset using the following simple query to generate a list of values for the ReportYear field.

Year(SOH.OrderDate) AS Year
Sales.SalesOrderHeader AS SOH

Year Lookup DataSet

Finally, we set the dataset, Year_Lookup, to be used for the available values for the ReportYear parameter, and note below.  


Now, the parameter is setup to accept multiple values. The setup required several steps including setting up our main report query to accept a parameter using the IN criteria, changing the allow multiple values option on the parameter properties, and last, generating a list of available values, in this example using another query. The end result is two fold. First, the report consumer now sees check boxes next to each parameter item which allows the user to select multiple values, as displayed below. Second, the report displays only the years selected.

Parameter List

Finally, the report data displays the years selected.

Final Report

You may notice in the above figure that the title shows #Error after Sales Report for:.This field references the @ReportYear parameter; when this parameter was just a single value, it displayed correctly. However, now that the parameter is multiple values, SSRS is unable to display a value. Fortunately, the SSRS Join function can be used to display all the values selected and separate them with a fixed symbol. For this report we will break up the years with an &. The exact formula used is as follows:

=JOIN(Parameters!ReportYear.Value, " & ")

The report with the JOIN function utilized is displayed below.

Join Function

Multiple Value Parameters and Stored Procedure Based Datasets

Using stored procedures as the basis for SSRS datasets offers many advantages including potential reuse by other reports and potential performance advantages. However, multi-value parameters do not work well when getting passed to a stored procedure. Embedded SQL datasets noted above handle the parsing of the multiple values used in the IN criteria. To the contrary, when the multiple values are passed to a stored procedure, all the values are conveyed as one value.

The ReportYear parameter in our example report, for instance, would get passed as one value, "2006,2007,2008" which, of course would return no rows. Fortunately, we can use a string splitter function as part of our stored procedure to break up the years into multiple values. We will once again turn to using a Tally table by Jeff Moden; please see this article on using the Tally table to parse out the values, http://www.sqlservercentral.com/articles/Tally+Table/72993/.

I am not going to repeat Mr. Moden's code in the article, since it would be beneficial for you to understand what it can and cannot do. We will however use this function in dataset stored procedure which is noted below. Please make the following notes about the stored procedure. First we are passing in the concatenated multi-value parameter as a single entity and we use a varchar parameter (which must be large enough to accept the maximum length of all the parameters which might be selected). Next using the splitter function, the values are parsed out and placed into a temporary table, #YEAR_LIST. Last, the year criteria is moved from being part of the where clause to being part of the joins.

-- =============================================
-- Author: Scott Murray
-- Create date: 01/01/2013
-- Description: Sales by year query with parameter breakout
-- =============================================
ALTER PROCEDURE dbo.usp_Sales_by_Year
@ReportYear varchar(50)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

--Parse values into table which will be an inner join on main data query.

--Main Dataset

SELECT P.FirstName + ' ' + P.LastName AS Employee
,DATEPART(Year, SOH.OrderDate) AS Year
,DATEPART(Month, SOH.OrderDate) AS MonthNumber
,DATENAME(Month, SOH.OrderDate) AS Month
,SUM(DET.LineTotal) AS Sales
FROM Sales.SalesPerson AS SP
INNER JOIN Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID
INNER JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
INNER JOIN HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID
INNER JOIN Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
INNER JOIN #YEAR_LIST AS YEARLIST ON YEAR(SOH.OrderDate) = YEARLIST.Item --Use join instead of where clause
--WHERE (YEAR(SOH.OrderDate) IN (@ReportYear))
GROUP BY P.FirstName + ' ' + P.LastName
,DATEPART(Year, SOH.OrderDate)
,DATEPART(Month, SOH.OrderDate)
,DATENAME(Month, SOH.OrderDate)


Certainly other methods exists to handle the parsing and include using the function in the where clause (I would avoid this method as I would not want to call this function for every row!). Alternatively, you could use a cross apply to match the years with the dataset. The final report utilizing the stored procedure methods is displayed subsequently.

Report Final Stored Procedure

Using Filters with Multiple Value Parameters

One last alternative involving the use of parameters pertains to dataset or object filters. Using filters at the object level, for example on a tablix, actually allows the same "larger" dataset to be used for multiple purposes while at the same time filtering the individual report parts based on a particular criteria. This setup can be advantageous in using a single dataset for all the report data; however, you also need to be careful about retrieving "very large" datasets while only using very small sets of the data. To setup a filter, first select the object in question, and then right mouse, click and select properties as illustrated below.

Filter Prpperties

On the properties window, select the Filter window, and click add. Select the "field" or expression that is to be filtered, and then select the "In" Operator. Last, click on the fx expression builder button to the right of the value field to open the expression builder box.

Filter Properties Settings

As shown in the below illustration, within the filter expression box, click on Parameters under Category, and then double click on ReportYear Under years. What appears in the expression value box includes "(0)" at the end of name. This zero actually means retrieve the parameter in ordinal position 0 (ie the first parameter of the selected parameters). As you are probably realizing, that is not what we would like to filter on; we want to filter on all the parameter values selected. The trick to make the filter work, is as easy as removing the "(0)" from the expression.

Filter Parameter Screen 1

Finally, the filter expression value actually should look as displayed below.

Filter Parameter Screen 2

Now, the resulting report using a multiple value tablix filter is illustrated below.

Final Report Filter


Multiple value parameters are a wonderful tool within SSRS; their methods in practice, though, varies depending on their usage within embedded T-SQL, within stored procedures, or as part of an SSRS object filter. Embedded T-SQL is somewhat easier to use, however, the query can not be easily shared; to the contrary, using a stored procedure offers the ability to reuse a query (and other set based and logic structures), but you must parse parameter. Using a multiple value parameter with an object filter is also easy to implement as long as you know how to implement the parameter values in the filter. Last, it is often beneficial to display the parameters selected by the report consumer; the join function in SSRS allows you to display the parameter list easily in the report header or body.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, May 2, 2023 - 4:51:35 PM - Scott Murray Back To Top (91145)
This Tip should help Ryan:

Tuesday, May 2, 2023 - 1:43:24 PM - Ryan D Back To Top (91144)
How did you create the sparkline in the 3rd column? Can you provide instructions for that please?

Friday, January 5, 2018 - 9:19:14 AM - Scott Murray Back To Top (74829)


Scott.. I would go to SQL 2016 and use String_Split.

Friday, January 5, 2018 - 8:52:20 AM - Scott Back To Top (74824)

Hi Scott


This is a useful article and helps clarify a few things I was a bit unclear about. 

What concerns me with the Stored procedure method and using the Jeff Moden tally table is that there is an 8000 character limit for your multivalue parameter.  Whilst this is a lot and SHOULD cover all situations, if we have a report we develop today that may be used for years, I get nervous that at some point someone will select all and end up with over 8000 characters worth of parameters.  To make matters worse, this will be one of those "hidden" issues as the report will still execute but will be missing some data. 

How does one handle that?

Monday, November 20, 2017 - 7:26:24 PM - Charu Back To Top (70032)

Aricle is Clear and Consice, this was a big help worked straight away. Thank you.


Thursday, April 16, 2015 - 5:40:17 PM - Chuck Todd Back To Top (36956)

Thank You!

Exactly what I was trying to do - this was a big help!

Saturday, March 21, 2015 - 1:11:59 PM - MILVA Back To Top (36620)


exec quadrant 'Softener','REGULER','A',IN'('BLEACHING','SOFTENER')','1'

thank you


Tuesday, March 3, 2015 - 5:22:40 PM - Scott Back To Top (36430)

You can use your method, but just need to monitor performance.  Otherwise, using the procedure method, you create a table with the proper joins.

Tuesday, March 3, 2015 - 4:16:23 PM - Trey Back To Top (36429)

Hey Scott,

How would you use this function in the WHERE clause? I know you advised against it, but I'm not getting the data I expect using the Joins in the From statement.  I have two parameters I am passing in: Collection Area and Recruiter.  All Recruiters belong to a Collection Area, but not all Collection Areas have a recruiter. It seems if I design the procedure by your method, I'm losing my Collection Operations that do not have a Recruiter assigned.

If I use a splitter function in the WHERE clause, I handle these situations with the following:

And CO.CodeID In (Select Number From dbo.fn_SplitInt(@COID,','))

And (DM.OwnerType=1 Or Rec.CodeID In (Select Number from dbo.fn_SplitInt(@RecID,',')))


Monday, March 2, 2015 - 12:12:29 PM - Trey Back To Top (36405)

First of all, thanks! This worked great for my report. And secondly, my issue was in how I declared my variable. I used nvarchar(128) which wasn't enough to hold all of the information and when I changed that to varchar(8000), it worked beautifully.

I've got another question to ask but I'll post that separately since it is another issue that I am facing.

Monday, March 2, 2015 - 7:37:13 AM - Scott Back To Top (36399)

I would first run profiler to see what is being passed to the stored procedure.  Have you tried adding the dataset as a query instead of a stored procedure and see how SSRS handles it?


Sunday, March 1, 2015 - 2:44:39 PM - Trey Back To Top (36392)

Hey Scott,

Any advice on how to handle SSRS not displaying all of the records it should be? I've found that if I select multiple parameters that might have a lot of rows (data), the report will only display. For example, I've got cascading parameters in my report that start on a region level, go to an organization level, the a sub-center level and finally a collection operation level.

I find that if I select a Region and then all org centers/sub centers/collection operations I will generally only get about half the data that I should be getting.  But if I select down to only a few collection operations, I'll get all of the data back on the report.

I saw Jeff Moden advised against it, but I changed the varchar to (max) and it still cuts off records.

Friday, October 31, 2014 - 7:40:03 AM - Scott Back To Top (35134)

Yes.. the logic applies but the syntax in Oracle would be different.

Thursday, October 30, 2014 - 7:42:21 PM - conny totok effendy Back To Top (35130)

Great article...

What if I get data from Oracle database, this method still working?


Monday, October 20, 2014 - 6:40:44 PM - Mark Pekel Back To Top (35022)

I solved the problem with the following code. 

="Selected diagnosis codes: " + IIF(Parameters!diagcodes.Count=CountRows ("DiagnosisCodes"), "All codes selected",
 Join(Parameters!diagcodes.Value, " , "))





Saturday, October 18, 2014 - 8:19:36 AM - Scott Back To Top (35005)



You could do the "all selected" ; you need to do a comparison of the values selected vs the full # of values.

Friday, October 17, 2014 - 6:11:11 PM - Mark Pekel Back To Top (35000)

This is a very helpful article.  My question is if the user selects "Select All"  from the multivalue dropdown list is there some way to display a message like "All values selected" instead of displaying all the values in the list.  In my case displaying all the values would result in hundreds of values being displayed.


Thursday, October 2, 2014 - 8:31:21 AM - Kris Back To Top (34818)

I Love http://www.mssqltips.com/

Thursday, August 7, 2014 - 7:32:20 PM - Dave Back To Top (34051)

Much appreciated!!!

Tuesday, April 8, 2014 - 7:29:11 PM - scott murray Back To Top (30013)

K Sears:


Make sure you filter is using the IN.  Also, can you verify the query runs even when not filtering the list (remove the filter).  You might want to verify that the parameter Label matches the Parameter value? I would next use SSMS profiler to see the actually query that is being run.

Tuesday, April 8, 2014 - 2:04:49 PM - K Sears Back To Top (30009)

I have just started with ReportBuilder and am in HR, not an IT person.

I have two columns - one is fullname, the other is supervisorfullname.

I want to be able to filter the fullname by only showing those whose full name matches the supervisorfullname (thereby filtering results to only employees who are managers)

I have set up a separate dataset (Supervisor) with SupervisorFullName (as pulled from an shared datasource, not DataSet1 in ReportBuilder) as the only field.  I set up a parameter based on this dataset, allowing multiple values, etc.

I then set up a filter with the following condition on DataSet1 Full_Name IN Parameters!Supervisor.Value

When I run the report, I can select supervisor names from a list of supervisor names, but when I then click" view report", I get no data.

I appreciate any help you could give me.



Wednesday, April 2, 2014 - 12:22:47 PM - scott murray Back To Top (29945)

You just need to set the label and value items in parameter.

Wednesday, April 2, 2014 - 10:35:39 AM - Barb Tighe Back To Top (29944)

Hi, I have a multi-value parameter that I would like to display a description along with the value in the drop down list, for example the drop down list would show the value A and the description APPLE.  I need the query to pass only the value, A.  I am using a dataset query embedded in my report (not a stored procedure).

Monday, March 10, 2014 - 12:20:33 PM - scott murray Back To Top (29689)

Rene... Unfortunately, the multi-value parameters list drop downs do not allow you to jump through the list based on keyboard input.  That functionality only works on non multi-value lists.

Monday, March 10, 2014 - 10:52:37 AM - Rene Voller Back To Top (29686)


I have a multi-value parameter to select values from a list of chemicals. How do I use the keyboard to quick select from the list, e.g. I have chloride, chloride (T), chlorine (F), instead of using the scroll function, I would like SSRS to go through the list to the point where the text matches the input from the keyboard.

Friday, February 28, 2014 - 1:55:26 PM - Scott Murray Back To Top (29616)

Sam David,

First I would not share my private email address. That could be problematic.

As to your question.  For most projects we do use views and stored procedure to bring data into SSRS.  On only a few occasions would we use straight SQL; primarily for permissions and security reasons are why we use the procedures.

Friday, February 28, 2014 - 12:27:57 PM - Sam David Back To Top (29614)

Hello Scott Murray ,

I hope this email feedback comes to you. You have done an excellent work for me through this article. It will be a survival tip for me.

Please continue sharing very complex tricks of the trade like this and many more since 1) MS Certified Partners training classes don't cover this type of information but point here and click there and give me $3K for the class and go home. 2) Most of the Reporting Services books don't go beyond basic.


I need your HELP for some real life challenges to understand 1) Creating View and 2) Stored Procedures. Do you always use them for all the Calculated Fields in the FACT tables? Q.when do you use them and when do you not?

I can be reached at [email protected]


Thank You and Keep writing. It really helps.

Tuesday, December 10, 2013 - 8:02:28 PM - Dominic Back To Top (27755)

Nice article, help me a lot. Thanks

Wednesday, October 23, 2013 - 12:45:35 AM - Gareth Back To Top (27234)

Great work scott, helped alot!

Thursday, August 1, 2013 - 5:44:38 PM - Carla Sabotta Back To Top (26092)

Assuming that you're applying the JOIN... expression to the text field ("Sales Report for"), you need to include this text in the expression to display "Sales Report for:" in the report, as follows:

="Sales Report for:  " & JOIN(Parameters!Name.Value, " & ")


Thursday, June 27, 2013 - 7:24:26 AM - Raj Back To Top (25593)

I want to pass perameter values to the view. Is it possible .

Wednesday, June 26, 2013 - 1:56:15 PM - Bruno Servulo Back To Top (25577)

This is a nice article for beginners, but I've noticed a few doubts regarding filtering.

First, I would not use the filter like you, because that would work AFTER the SELECT's been made and so forth consuming unnecessary resources from your database.

You can use the report parameter in a dataset, and using the arrows in reportdata tab you can select the correct order. this way, you'll only select the data you want and apply the filter directly in the select.

another thing, to filter using multivalue parameters you can use IN (@parameter) operator. This will be much more performance friendly than CHARINDEX.

Wednesday, June 26, 2013 - 12:59:35 PM - Mark Back To Top (25576)

I have an interesting situation, I use the multivalue filter on a dataset and it works just fine in a development environment. That is the filter will always work whether I choose the "select all" or any single or multiple item selections available for the parameter.

Again this is when I am editing and testing a report with the Report Builder.. However when I execute the report either from a url link or by directly clicking on the saved report (to run it), the display works fine the first time through (because my default is all available values). But as soon as I filter (select a single or more than 1 available items, and then click "View Report" the effect is as if the entire result set has been filtered out. 

Any ideas?


Tuesday, June 18, 2013 - 11:00:59 AM - Scott Murray Back To Top (25468)

You will need to do this in some custom code to parce out the parameter.   Or you may be able to use the Split function.

Tuesday, June 18, 2013 - 10:07:48 AM - Peter Back To Top (25466)

Great. It works well for me but I would like help with one related thing.

I have a multi value parameter to decide what graphs should be visiable. And for every one of the graphs I would like to do an expression to find out if this graph has been selected to be visiable. An expression something like  "=LockupMemberFunction(1,Parameter!ParamViews.Value)" where 1 is the first graph and ParamViews is the multi value list of selected graphs (for example 1,4,5). Please help me with that expression.

Tuesday, June 4, 2013 - 7:12:43 PM - Scott Back To Top (25293)

Dave... you need to run profiler to see what is being passed to the database.

Tuesday, June 4, 2013 - 3:41:57 PM - Dave Back To Top (25291)

On March 29th Rajee wrote


My report does have multivalues parameter and it is not rendering the values when I view the report. IF i passing single Values for the parameter it is working . If i pass multple Values fro the parameter it is not working . I have tried the join that you mentioned in the parameter properties still it is not working for me. Any other ideas pleas!!"


I have the exact same situation and have have tried all the suggestions posted. Has any one found a solution? 

Thanks in advance for your assistance.

Wednesday, May 8, 2013 - 9:23:56 AM - Scott Back To Top (23809)

What if you right mouse click on the dataset;  you should get additional options.

Wednesday, May 8, 2013 - 6:17:54 AM - Quinell Back To Top (23805)



When i click on my dataset properties. i only get a General option. no other options to specify a query and so forth. i use VS2010.

could you please tell me how this is possible?

Friday, March 29, 2013 - 5:56:26 PM - Rajee Back To Top (23078)



 My report does have multivalues parameter and it is not rendering the values when I view the report. IF i passing single Values for the parameter it is working . If i pass multple Values fro the parameter it is not working . I have tried the join that you mentioned in the parameter properties still it is not working for me. Any other ideas pleas!!

Thursday, March 7, 2013 - 3:31:18 AM - Eran Dagan Back To Top (22622)

Anothter option to filter data when using a multi-value parameter to a query is by using the CHARINDEX function in the WHERE clause.

For example:

Multi-value parameter: @Country. The  SSRS will send the following string to the query 'Israel, England, Spain'

In the query the WHERE clause will be look like this:

SELECT  Country,TotalSales

FROM  Sales

WHERE  (@Country='All'OrCHARINDEX(Country,@Country)>0)

Hope that helps



Tuesday, January 22, 2013 - 8:59:04 AM - Martyn Jones Back To Top (21616)

Gonzalo, the split function is contained in the linked article http://www.sqlservercentral.com/articles/Tally+Table/72993/

head down to the bottom to find figure 21, just under 


 and use all the code in figure 21, the rest of the article explains how it all gets put together. It works well.

 Joao, great article, the function is incredibly useful - I've always used the filters before and not really liked having the sql within the report for obvious reasons, thank you!

Monday, January 21, 2013 - 4:41:29 PM - Gonzalo Back To Top (21604)

This is something that is very useful for me. Since using SSRS I've been using VIEWS to be able to use multi-value parameters. However, I want to take advantage of using stored procedures, but the whole multi-value parameter issue came up. I read this article. but is there a universal splitfunction can be provided or is it different for everyone.

I'm not a master at creating any functions, so I would know where to start if I wanted to create a split function.

Can another article be created on how to make a split function?



Monday, January 21, 2013 - 9:06:34 AM - Scott Murray Back To Top (21596)

Hi Joao:

This process should work the same with non integer parameters.  I am unsure which of the 3 options you are saying does not work, but a couple of items I would check include:  1.  verify the parameter data type (on the General window/tab), 2. for filter based solution, again check the data types on the filter screen, 3.  if being passed to a stored procedure, run a profiler while executing the report to see exactly what is being passed to the procedure... depending on those results, run the stored procedure though some query check..... see what values the function is inserting into the temp table.

Monday, January 21, 2013 - 8:44:57 AM - Joao Castel-Branco Back To Top (21595)

Could you please tell how can you do this if the parameter value is not a integer? this doesn´t work with varchar values.

get free sql tips
agree to terms