Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Reporting Services Using Multi-value Parameters

MSSQLTips author Scott Murray By:   |   Read Comments (27)   |   Related Tips: > Reporting Services Development
Problem

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?

Solution

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
    ,SOH.SalesPersonID
    ,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.

parameterspec
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.

SELECT      
Year(SOH.OrderDate) AS Year
FROM
Sales.SalesOrderHeader AS SOH
GROUP BY
Year(SOH.OrderDate)
ORDER BY SOH.Year


Year Lookup DataSet

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

ParameterAvailableValues

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.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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)

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

--Parse values into table which will be an inner join on main data query.
SELECT Item
INTO #YEAR_LIST
FROM
dbo.DelimitedSplit8K(@ReportYear,',')


--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
,SOH.SalesPersonID
,DATEPART(Year, SOH.OrderDate)
,DATEPART(Month, SOH.OrderDate)
,DATENAME(Month, SOH.OrderDate)



END
GO
 

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

Conclusion

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


Last Update: 1/21/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, August 07, 2014 - 7:32:20 PM - Dave Read The Tip

Much appreciated!!!


Tuesday, April 08, 2014 - 7:29:11 PM - scott murray Read The Tip

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 08, 2014 - 2:04:49 PM - K Sears Read The Tip

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 02, 2014 - 12:22:47 PM - scott murray Read The Tip

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


Wednesday, April 02, 2014 - 10:35:39 AM - Barb Tighe Read The Tip

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 Read The Tip

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 Read The Tip

Hi,

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 Read The Tip

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 Read The Tip

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 sdavid0101@gmail.com

 

Thank You and Keep writing. It really helps.


Tuesday, December 10, 2013 - 8:02:28 PM - Dominic Read The Tip

Nice article, help me a lot. Thanks


Wednesday, October 23, 2013 - 12:45:35 AM - Gareth Read The Tip

Great work scott, helped alot!


Thursday, August 01, 2013 - 5:44:38 PM - Carla Sabotta Read The Tip

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 Read The Tip

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


Wednesday, June 26, 2013 - 1:56:15 PM - Bruno Servulo Read The Tip

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 Read The Tip

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?

MBK


Tuesday, June 18, 2013 - 11:00:59 AM - Scott Murray Read The Tip

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 Read The Tip

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 04, 2013 - 7:12:43 PM - Scott Read The Tip

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


Tuesday, June 04, 2013 - 3:41:57 PM - Dave Read The Tip

On March 29th Rajee wrote

"Hi,

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 08, 2013 - 9:23:56 AM - Scott Read The Tip

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


Wednesday, May 08, 2013 - 6:17:54 AM - Quinell Read The Tip

Hi

 

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 Read The Tip

 

Hi,

 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 07, 2013 - 3:31:18 AM - Eran Dagan Read The Tip

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

Eran

 


Tuesday, January 22, 2013 - 8:59:04 AM - Martyn Jones Read The Tip

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 

NOTICE!!!! THIS IS NOT THE ORIGINAL CODE FROM THE ORIGINAL ARTICLE!!!

 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 Read The Tip

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?

 

Thanks!


Monday, January 21, 2013 - 9:06:34 AM - Scott Murray Read The Tip

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 Read The Tip

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.