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.
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.
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
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.
Finally, the report data displays the years selected.
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.
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,',')
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)
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.
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.
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.
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.
Finally, the filter expression value actually should look as displayed below.
Now, the resulting report using a multiple value tablix filter is illustrated below.
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.
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,',')))
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.
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.
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.
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.
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
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
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
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
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?
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.
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.
Tuesday, June 18, 2013 - 11:00:59 AM - Scott Murray
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.
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?
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
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
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