More About Multi-Value Parameters

Just for fun, I am going to talk about multi value parameters again. In this blog, Shortcomings of Multi-Value Parameters, I discuss two of the shortcomings. Now I am going to talk about how to use them in detail.

OverviewMany times you will need to have a parameter that allows the user to select more than one value. In this instance, when you are creating the parameter, check on the Multi-value check box in the Report Parameters window.


Available Values… Or Not!If you have a set list of items that you want the user to be able to select from, you can establish this list in the Available Values section of the Report Parameters window. You can either use a dataset, or type in static values.

To use a dataset to populate the available value, create the dataset in the data tab, making sure that you assign field names. Then in the Report Parameters window, click on your parameter, click on the From Query radio button in the available values section. Then select your dataset name, value column and label column. The value is what will be returned when referencing @Parameter in your SQL query, and the label is what will be shown in the drop down parameter. You can access both of these values in expressions:

Parameters!Parm1.Value or Parameters!Parm1.Label

You can have a multi-value parameter with or without available values. For example, you may have a parameter for Color, with the available values being red, green and blue. In this case, when you preview the report, you will have a drop down check list, with Select All, Red, Green and Blue listed. Users can click on one or more checkboxes, and select the colors they want to filter on. (If you have SP1, you will not get the Select All check box. The fix for this is SP2.)

If your multi-value parameter does not have any available values assigned to it when it is set up, your users are presented with a drop down multi line text box. The users can enter values, separated by a carriage return, and SSRS automatically formats it for an IN clause in SQL. The great thing about this: Users can copy a column of data from Excel and paste into the textbox! I had a client that worked with loans. They always needed information based on a list of loan numbers that someone had in an Excel spreadsheet. I developed a report that have a multi value parameter, they would copy the loan number column out of the spreadsheet, paste it into the parameter, run the report, export to Excel. It saved them from having to copy, look up, format, etc.

When using a Multi-value parameter, you cannot have a null value returned. This means that the user will have to select or enter at least 1 value from the parameter list. When setting up the parameter, you will get an error if you try to select the Allow null value check box. If there are no available values, the user must enter at least 1 value into the textbox.

Using in SQL
If you are going to be passing a multi-value parameter to a stored procedure, you will have to pass it as 1 string, and parse it in the stored procedure. See the blog referenced above for more information about this.

However, to use your multi-value parameter in a dataset SQL statement, just use the IN clause:

Where ItemID in (@Item)

SSRS will automatically add the commas and format it for you.

There is no way for you to tell if all of the values have been selected, or if the Select All was clicked on. This is an issue if you have a lot of values because the IN clause is worthless, but SQL still executes it, cost precious time and resources. If you expect to have a long list of values, or if you think that all of them will be selected most of the time, there is a workaround documented in the blog referenced at the top of this article.

Using in ExpressionsTo determine how many values have been selected or entered, use the Count property:

=Parameters!Parm1.Count

To reference a value directly, use:

=Parameters!Parm1.Value(arrayindex)

where the array index is less than the count.
If you want to display the list of values selected/entered, use the Join function:

=Join(Parameters!Parm1.Label, “, “)

The second argument is the delimiter; what character string is displayed between each parameter entry. You could also reference the parameter value.

Using in Code
You can reference multi-value parameters in embedded code as well. See my article about multi language report labels for a look at how that is used.

Passing in URL
To pass the multi-value parameter values in an URL to report manager, repeat the parameter name. For example:

http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

That’s all for now. I think this gives a lot of information. Hope it helps!

peace

CodeProject