March 2, 2011 at 7:42 pm
I have an existing sql server 2008 r2 report that has a drop down menu where you pick only one item at a time. However, I would like to change this ssrs 2008 r2 drtop down menu so multiple items can be selected at one time. Thus can you tell me how I can change the existing drop down menu so multiple items can be selected at one time
March 3, 2011 at 12:41 pm
Hi - here's a copy of a "how to" blog post I wrote a month or so ago. Using multiple value parameters is easy once you've done it once, but the first time is fairly finicky. Below is a straight cut and past and doesn't refer to your situation specifically so ignore the stuff you don't need - Paul
SSRS How to: Set up a Report with Multiple Value Parameters, Pass Multiple Value Parameters to Drill Through Reports and Displaying Multiple Values in a Heading
This post is to help anyone trying to get Multi-Value Parameters working in SSRS 2008. Specifically:
1.Getting Multi-Value Parameters working
2.Getting a second query to provide the selections for the drop down box
3.Passing multiple parameters to a drill through report
4.Displaying multiple parameters in a heading
5.Displaying a comment like “All Items” in a heading in cases where ‘Select All’ is chosen, rather than showing all the parameters
1.Getting Multi-Value Parameters working
I’m assuming at this stage you've got your query up and running and just can’t get the multiple parameter bit working. That being the case, I’ve just one thing to say here:
The WHERE expression in a SQL query for a multi-value parameter IS NOT THE SAME as for a single parameter. Multi-value requires the IN statement.
Multi-value: [FieldName] IN (@ParameterFilter)
Single: [FieldName] = @ParameterFilter
Rob Farley showed me what SSRS was doing with multiple values in this blog:
2.Getting a second query to provide the selections for the drop down box
This query is needed when the main dataset is returning multiple instances of the same names (eg you’re looking at all instances that Product A, Product B etc was sold), and all you want to show in the drop down list is one instance of each.
Create a second dataset (let’s call it ‘DropDownListQuery’) and use a query something like below so only one instance of each variable is returned (Select Distinct works as well):
Select [FieldName]
From Database
Group By [FieldName]
Order by [FieldName]
In the Parameter Properties, select the name of the multi-value parameter, in this case ‘ParameterFilter’ (which will have turned up automatically because it was created in the query in Step One).
From the General tab, choose data type ‘Text’ (assuming it is) and tick ‘Allow multiple values’.
In the Available Values tab, select ‘Get values from a query’, then select the grouping query (in this case ‘DropDownListQuery’), and choose the Value and Label field from the dropdown box (which in the example above will be ‘FieldName’)
3.Passing multiple parameters through to a drill through report
First step is to set up the drill through report (we’ll call it ‘DrillThrough Report’) as you want it to look. In the query, you’ll need to include a statement allowing a multiple parameter (remember using the IN statement) which is the same as the multiple parameter statement in the main query.
Very importantly, TICK ‘Allow multiple values’ in the parameter properties.
As an aside, you can set up the drill through report to work independently of the main report, ie get a multi-value grouping query working as per step 2; it won’t have any effect on the drill through.
Go to the main report and right click on the textbox or placeholder you want to drill through on, select Text Box or Placeholder Properties, then choose the Action tab.
Click ‘Go To Report’
Select the Report you want to drill to (in our case ‘DrillThrough Report’)
Select ‘Add a parameter’ – this is the information which will be passed through
Select the parameter name from the drop down box (in our case ‘ParameterFilter’ from step 1)
->->THIS IS THE MOST IMPORTANT BIT <-<-
An expression needs to go in the Value box, so click on the fx button next to the Value box. In the expression area type the following (replacing the name that we’re using in this example (‘ParameterFilter’) with your own parameters name):
=Parameters!ParameterFilter.Value
It’s ok to bring up and click on the relevant parameter in the bottom half of the expression builder, but you must REMOVE any brackets which automatically turn up eg =Parameters!ParameterFilter.Value(0) must become =Parameters!ParameterFilter.Value
The drill through should now work.
4.Displaying the multiple parameters in a heading
The point of headings is to tell the viewer what is being shown. It is important with reports using parameters that the selected parameters are shown.
In a report using only one (or for that matter, many) single parameters this is easy, simply by using the Parameters!YourParamterName.Value command. For example, using our parameter from above and another one for the date range:
=”Sales for “ & Parameters!ParameterFilter.Value & “ for “ & Parameters!MonthYear.Value & “for ABC Division”
With Multivalue parameters, to have all of them appear in a heading, the Join command is needed:
=Join(Parameters! YourParamterName.Value, ", ")
Building on the single parameter example, it might look as follows:
=”Sales for “ & Join(Parameters!ParameterFilter.Value,”, “) & “ for ABC Division”
In the above example, each of the parameters selected will be separated by a comma and a space. You decide the format you want and type it between the parentheses.
5.Displaying a comment like “All Items” in a heading in cases where ‘Select All’ is chosen, rather than showing all the parameters
The downside of putting multiple parameters in a heading is when the user chooses ‘Select All’ from the drop down list, and the heading suddenly has 150 names in it.
The best way around this is have the heading display something like “Sales for All Products for ABC Divisions” whenever Select All is chosen, but at any other time to show the parameter options that have been chosen.
The only way to do this is to compare the number of rows returned in the grouping query (which provides all the parameter options) and the number of options selected by the user.
CountRows will do the first job, specifically: CountRows(“DropDownListQuery”) – note only the name of the dataset is needed, not a field name.
Parameters! YourParamaterName.Count will do the second job. This is the same command used to insert the parameters into the heading except instead of returning a value it is returning a count.
Put them together in an IIF expression in the heading and we have:
=iif(CountRows("DropDownListQuery ") = Parameters! ParameterFilter.Count, "All Products ", Join(Parameters!Filter.Value, ", "))
This can then be expanded as per the heading in step 4 to:
=”Sales for “ & =iif(CountRows("DropDownListQuery ") = Parameters! ParameterFilter.Count, "All Products ", Join(Parameters!Filter.Value, ", "))
& vbCrLf & “ for ABC Division”
The first time I did this they didn’t match due to blanks in the query so I inserted:
Where [FieldName] <> '' into the parameter grouping query.
By the way vbCrLF is a great little command to move whatever comes after it down a line in a text box.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply