Multi-select parameters give your users control over their reports while reducing the number of reports they have to work with. In this example, I will demonstrate how to create a multi-select parameter list and pass the values to a stored procedure that will then populate the report. I will be working with the AdventureWorks2008R2 database to create a report which will list sales quotas and amounts for selected Sales Reps.
The key to this solution is a delimited list of values that can be passed from the report to a stored procedure. I will be using the comma as a delimiter, but any delimiter will work.
For this example I will be using two parameters. The first parameter will provide a list of Sales Territories. The second parameter will provide a list of Sales Reps based on the selected Sales Territories from the first list. The report will show sales quotas and amounts for the selected Sales Reps.
Create a stored procedure that will return a list of Sales Territories. This stored procedure will be used by the first parameter of the report.
Create a second stored procedure that will return a list of Sales Reps for 1 to N Sales Territories. This will be used by the second parameter of the report. The list of selected Sales Territories will be passed to the stored procedure as a comma delimited list of TerritoryIDs in a parameter called @TerritoryID. (If your list of values have commas, then you'll have to use a different delimiter.) The size of the parameter should be determined by the maximum list of values that might be sent to the stored procedure.
There are many different ways to break up a delimited list of values. They are usually referred to as Split Functions. I like to use a recursive CTE (Common Table Express) to split the values up. After the list of values are in a table structure, the table structure can be joined to the rest of the tables needed to return the list of Sales Reps.
Create the stored procedure for the body of the report. In this example, it will also have a parameter, @BusinessEntityIDs that will contain a comma delimited list of selected Sales Reps.
Add the three stored procedures that were created in steps 1 through 3 to a new report. Then create the layout for the report. My report is laid out by grouping the Sales Reps by Territory.
Set the Properties of both parameters by right clicking on the parameter and selecting Parameter Properties from the drop down list.
Check the Allow multiple values checkbox.
Select Available Values from the left hand list.
Select the Get values from a query option button.
Set the Dataset drop down list to the proper dataset.
Set the Value field drop down list to the proper field. This is the value that will be returned to the dataset that needs it.
Set the Label field drop down list to the proper field. This is the value that will be displayed to the user.
The list of selected values need to be returned to the ListSalesRep_s and the RptSales_s stored procedures. This will be done using the JOIN expression.
Right click on the stored procedure that needs the list of values and select Dataset Properties from the drop down list.
Select Parameters in the left hand list of the Dataset Properties dialog box.
In this example, the delimited list of Territories need to be assigned to the @TerritoryIDs parameter. Click the function button to enter a function for the parameter value.
Add the following code for the expression. Make sure to use the Value property, not the Label property or the wrong list of values will be sent to the stored procedure.
It's time to run your report. When the drop down lists are pulled down, there should be check boxes to select only the rows you want. Each time the selected items are changed in the Territory IDs drop down, the Sales Rep list will automatically be regenerated when the Sales Rep list is pulled down.
I think all reports should display the values of the parameters used in the report. This way when someone brings up a concern about a report, you know exactly which parameter values were used.
If you'll notice in the sample below, I listed only the three Territories that were selected, but since all the Sales Reps for those territories are used in the report, the word ALL is used instead of listing each Sales Rep out individually. This can be done with an IIF expression in a Label control.
The first parameter of the IIF expression compares the count of how many items are in the parameter list, with how many that were selected. If the two values match, then the label ALL is used. If they don't match, then the JOIN expression is used again, but this time the Labels are joined together. Make sure to include a space after the delimiter so that the values don't run together.
To download the AdventureWorks2008R2 database, go to codeplex.com and search for the "AdventureWorks 2008 R2 database".
Thanks,IT is working .Becoz of this code we can pass paremeter to procedure,Jst Mention process for value display as label with seperated by comma.
I am having an issue closely related to this post. Let me just say, that I am a complete novice with SQL/SSRS (but I'm trying to learn). I have created a dropdown parameter selections for an SSRS report. I am able to set-up a parameter (via a simple select Query), and I can get the report to execute the parameter when someone selects a single value. However, if someone selects all or more than one values, then my report doesn't return any records.
However, I cannot seem to get anything to work. I am not sure what I need to do. Below are my two queries.
Thank you to anyone who can help!!!
I enjoyed reading this article. Also I created the report successfully. But I found it is little bit hard to understand the steps for novice audience.
What I observed special in this article is usage of RECURSIVE QUERY in PROCEDURE. It;s good to learn such functionality.
Keep publishing some more articles.
Can you post the .rdl file for this report ?
"An error occurred during local report processing. an error has occurred during report processing. The value expression for the query parameter @Category contains an error: Overload resolution failed because no Public 'Join' can be called with these arguments: 'Public shared function join(SourceArray as string(), [delimite as string=]) as string;'. Argument matching parameter 'SourceArray' cannot convert from 'string' to 'string()'. 'Public shared function join(sourcearray as SYStem, Object(),[Delimiter as string =])as string'; Argument matching parameter 'sourcearray' cannot convert from 'string' to 'object()'.
Mickey, Thanks for your reply. I achieved the same using the stored procedure to load the drop downs either by the returned value or by default with an -ALL- value and generate the report.
I have a type-o in my reply. DL2 would be like ListSalesTerritory_s.
You are almost there. Steps 6 and 7 are doing exactly what you need. In your case, you will use the selected values from DL1 in the JOIN function and pass that value to the stored procedure for DL2 through it's Parameter. Continue the process for DL3 and DL4. When looking at the code above, ListSalesRep_s would be DL1 and RptSales_s would be DL2. Then continue the pattern for the other two.
I have hit into a roadblock in my report where in which i need to populate multiple dropdown when i select a dropdown in the report.
On selecting the values of DL1, i need to update all the values in Dl2,Dl3,Dl4. All the dropdowns are dynamic and get values from a dataset with defaul value set to the same dataset.
Here Dl2 is dependent on dl1 and dl3 is dependent on dl2 and dl4 is dependent on dl3.
Its easier for me to populate the dropdown list based on the previous selection, but the way to populate multiple drop down list looks a bit complicated to me.
Any help in this regard would be appreciable.
There are two ways you can do this. The first solution involves adding a "Select All" row to your stored procedure and handling that option in the stored procedure that generates the report. The second solution involves a raido button, drop down list, or check box like you suggested, but it does involve an additional call to the in the middle of the parameter selection process for the report.
1. Add a row using the UNION ALL statement in the stored procedure that returns your list of values. Also, include a fake sort column so that the "Select All" always appears at the top. Note: Use UNION ALL whenever you don't need to have duplicates removed from the second select statement.
Note: Make sure that the "ID" for the "Select All" is not a real value in your real dataset.
2. In your stored procedure that returns the report data, use a technique that will return some or all values.
a. I use a split function to split my list up, but you need to also see if the -1 was returned. My list of values is in @List. If -1 was passed in, then I'll select the full list from the master list table. Otherwise I will only return the values they selected and returned in a comma seperated list in the parameter @List.
Create a control that will determine if the list is selected or unselected. Have it return 1 or 0 respectively.
Add the stored procedure twice to your project.
Use Sproc A on the Available Values properties.
Use Sproc B on the Default Values properties.
Note: this solution can affect the performance of the report if the stored procedure that populate the list control takes a long time to run.
I'm sorry I didn't see your comment until now. Are you still having issues with the multiselect? If you are, please let me know some more details.
I appologize for not seeing your question until now. You have hopefully found the answer. If you haven't, then the answer is below.
There are two ways to set defaults for a multiselect parameter.
Go into the properties for the parameter and select Default Values from the list on the left.
The second option in the list, "Specify Values" will allow you to hard code values.
Click the Add button to add a row to the value list.
Put a value in each row.
The thrid option "Get Values From a Query" will allow you to get a list from the database. This is my prefered method.
Add the field with the default values to the Value Field property.
Thanks for the post. I have a question that maybe you can help with, since I haven't been able to find anything online anywhere else. I am doing something similar to what you are doing here. Howvever, I need to take it one step further. I have a multi-select parameter with a large list of values. The reporting screen used to build the report has a "Select All" option in the list. However, this report is displayed in a webbased UI and the control used to display the list of values does not have a "Select All" option just yet. We don't want the user to have to select or unselect the individual items when checking or unchecking all items. So I want to use a radio button that lets the user to check or uncheck all items in the list.
So my question, is there a way to programmatically check or uncheck all items in a multi-select parameter? Nothing jumps out at me when I look at my options and I've done plenty of searching online about this.
I appreciuate any feedback you have regarding this issue.
Great Article! I emphatically agree with your comments about parameters always appearing on reports. I had something very similar going with showing values of multi-select parameters on the report and was looking for a way to display "ALL". Your final tip is just what I needed. Thanks!
We recently upgraded our SSRS instance from 2008 to 2012. After the upgrade any reports with a multi-select list no longer function correctly. When the user selected the parameters for the report then presses view report - it just posts back with no execution or even an attempt at execution - just your normal .net postback.
Any idea what is going on here in 2012? I have had very little luck searching for a soltuion online.
In the report parameter, I have 3-multi value drop-down select values are there with unchecked. In these 3-value I want to set first 2-values to default value (i.e. check box should be selected) and last value should be un-checked in default condition. Let me know this can be possible in SSRS.
I just tried making a Parameter value Checked by default.
Click View, then Report Data. Right click a Parameter value and then click Report Parameter Properties.
Click Default Values and then click the Radio Button "Specify Value", then click Add.
The value of the check box goes into the input box and then click okay.
Does anyone know how to set a checkbox defaulted to "Checked"?
I finally resolved my issue with the FieldName.Value issue that worked on the tabs, but would not work properly in the Tablix header.
The solution was to create a child group from the parent group and then I could reference that Field value.
Here is an example: The Parent Group is StateName. To have the StateName go into the Tablix header and change in each tab, I created a Child Group StateName1 both in the report query and in the Child Group. Now it works.
It worked fine.... Thank you so much.
Patrick LeBlanc did it on his example at the site http://www.bidn.com/blogs/PatrickLeBlanc/ssis/762/ssrs-r2-naming-excel-worksheet-tabs, but I cannot recreate the same work on a new project. I was able to get his example to work.
1. Put a Table control on your page butting up to the header at the top.
3. Set the detail row too your Widgets data(Detail 1 Widget Name Cost City).
4. Group by the State Name. Make sure to check the box that asks if you want a header row.
4.a A new column will be added. Delete it. You don't need it.
5. In the properties of the Grouping row, set "break between each instance of a group"
6. There is a triangle (drop down menu) to the right of the text "Column Groups" at the bottom of IDE. Click it and select "Advanced Mode". This will expose some new rows in the "Row Groups" and "Column Groups" area. They all say static. These rows represent the header rows that are displayed in the table.
7. Select the "Static" row in the "Row Groups" that is right above the detail row and go to the property window. Set the following properties.
Note: If you are going to keep any of the other static rows in "Row Groups" that are above the one whose properties you just set, then you need to set their properties as well.
I'm sorry there aren't any pictures to help with this. Good luck. Let me know if there are any other questions. I can work with MSSQLTips.com to get some images loaded for you.
What you can do is create a table on your master report that only contains the subreport in a cell in the detail row. This "outer table" would have a row for each value of your 4th parameter. You would then pass the value from the row (something like =Fields!YourFieldname.Value) to your fourth parameter. If the subreport only appears once per page, then you can set the page break to occur after each row.
In my experience subreports are very costly. Depending on what you are having the subreport do, you might be able to use a combination of a table control and a list control. The Table control would be the outer table like I explained above, and the list control would allow you to lay out all the data elements you had in your subreport. The List control would then nest inside the table control. I use this technique in lieu of a sub report 99% of the time.
Let me know if you have any more questions. I'm happy to help.
I'm having a problem using a subreport inside a Master page. My Master page has 4 parameters with one parameter that varies.
The one subreport inside the Master page will take 3 parameters that are constant and the 4th parameter will change by way of a loop. The output will create many reports generated from the 4 parameter.
How do you create a loop from the Master page changing the value of the 4th parameter to run the subreport as many times as there are values in the 4 parameter?
Another way to approach the solution, would be to pass the primary key IDs instead of the company names. That way you don't need to address the special characters. This will also cut down on the size of the string that is passed to the stored procedure. In step 5, I use the primary key for the Value field and the Territory Name for the label. I then join the selected Values to create a string of delimited primary keys.
Thank you for pointing out this crucial design element that needs to be considered strings are being manipulated.
Thanks for your great comments and questions. I thought about addressing them when I wrote the article, but I wanted to limit the scope of the article to the multi-select parameter.
In my environment at work we use a scalar table function to split the string up. I did not use that technique here, because I didn't want to address the performance implications of use user defined functions.
I also didn't want to get into a discussion of varchar(max) and the varchar(100) was plenty for returning integers for this database. We do use a varchar(max) in my environment at work.
I do apologize for the confusion of the label.
And finally, the recursive CTE. Yes there is default a limit of 100 loops, but you can override it with a query hint "OPTION (MAXRECURSION X)" where X can be 0 to 32,767. The query hint is added at the end of the SELECT statement. My split function uses 4000. Why? When I created the table function, we could not pass a varchar(max) to a function so we used varchar(8000). This meant the maximum number of loops I would need was 4000. I haven't changed it, because if more than 4000 values need to be split, I would want to consider a redesign of the approach being taken for that code base.
I took the same approach using Join with comma (",") as the delimiter, but it turns out we have a lot of client data that has comma's already in it. Guess what happens??
So basically I came up with an approach to use an obsure delimiter for Join ("~"), and then doing an outer Replace on the joined string using the comma (",").
Wow Mickey this is very advanced stuff, I'm sure it will help intermediate users though.
I've done something similar using a scalar function to get the comma separated valued from a list. As was already posted, there are easy ways to do this but they certainly don't apply to stored procs so if you are you need to worry about this.
The input parameter for your proc is a varchar(100) ? That limits how many territories you can select.
The recursive CTE you are using doesn't specify a limit so it will use the default limit which is 100 rows - this inbuilt limit of CTE prevents the recursion looping infinitely, although your where condition should prevent that anyway when it finds the last comma.
Thank you for the feedback and alternative approach. I think Mickey was trying to outline an option she had had success with in her development efforts.
seems a bit complicated for a multi select parameter. I usually create 2 datasets and use the first parameter for the second one like below. this solves my purpose.
Hi, Thank You so much for this post. It was one of my last interview question also. Really helpful for me.
Thank you for the update. The tip has been updated.
This is an awesome post Mickey!
We usually call this as Multivalued Parameters. It's good to know that there's a lot of other ways on how to implement Multivalued Parameters.
The second and the third storedproc av the same name, is it an ALTER or...?