Using Single-Valued and Multivalued Parameters

Parameters can be single-valued or multivalued. A multivalued parameter can be set to more than one value. When you define an available values list for a multivalued parameter, Reporting Services automatically provides a Select All option on the report toolbar drop-down list. You can use this option to select and unselect all values in the list.

Note

The Select All option was disabled for SQL Server 2005 Service Pack 1, but restored in SQL Server 2005 Service Pack 2.

To set the multivalue property for a parameter, you choose the Multi-value option on the Report Parameters dialog box. You can set any parameter type to multivalue except Boolean.

The primary use of multivalue parameters is to allow a query restriction clause, for example, the Transact-SQL WHERE clause or the MDX Filter clause, to test for inclusion in a set of values instead of equality to a single value. For more information about creating multivalued parameters, see Tutorial: Adding Parameters to a Basic Tabular Report and Creating Report Parameters and Setting Report Parameter Properties.

Security Note:

In any report that includes a parameter of type String, be sure to use an available values list (also known as a valid values list) and ensure that any user running the report has only the permissions required to view the data in the report. When you define a parameter of type String, the user is presented with a text box that can take any value. An available values list limits the values that can be entered. If the report parameter is tied to a query parameter and you do not use an available values list, it is possible for a report user to type SQL syntax into the text box, potentially opening the report and your server to a SQL injection attack. If the user has sufficient permissions to execute the new SQL statement, it may produce unwanted results on the server. If a report parameter is not tied to a query parameter and the parameter values are included in the report, it is possible for a report user to type expression syntax or a URL into the parameter value, and render the report to Excel or HTML. If another user then views the report and clicks the rendered parameter contents, the user may inadvertently execute the malicious script or link. To mitigate the risk of inadvertently running malicious scripts, open rendered reports only from trusted sources. For more information about securing reports, see Securing Reports and Resources.

Writing Queries that Map to Multivalued Report Parameters

You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:

• The data source must be SQL Server, Oracle, or Analysis Services.
• The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
• The query must use an IN clause to specify the parameter.

The following example illustrates the use of an IN keyword in the WHERE clause of a Transact-SQL statement. For more information about the IN keyword or the results returned by this query, see IN (Transact-SQL).

SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c 
    ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)

To experiment with this in a report, define a dataset using this query. Change the properties for the automatically created report parameter Title in the following way:

• Select the Multi-value option.
• In Available values, select the Non-queried option. Enter the following list in the Value column (leave the Label column blank): Design Engineer, Buyer, Marketing Assistant.
• In Default values, enter Buyer.
• Run preview. Select different combinations of values for Title and verify that you get the expected results.

Note

The report server rewrites queries for data sources that cannot process parameters as an array. Rewriting the query is necessary to produce the intended result. A query rewrite is triggered when a parameter is defined as multivalued and the query uses an IN statement to specify the parameter. If you build a query that does not include the IN statement, be aware that you are circumventing the logic the report server provides to support multivalued parameters.

Filter expressions for datasets, data grouping, and data regions are defined on the Filter tab of the corresponding property pages. If you have defined a filter expression that refers to a multivalue parameter, you must use the IN operator in the filter expression. Filter expressions that use operators other than IN result in processing errors. For more information, see How to: Add a Filter (Report Designer).

Writing Expressions that Reference Multivalue Parameters

When you refer to a parameter in an expression, you use the global collection Parameters. When using multivalue parameters in expressions, you need to understand both how to address a single value and the entire array of values. The following table provides examples and descriptions of parameter properties for parameters that have the Multi-value option set.

Example	Description
Parameters!<ParameterName>.Value	An array of variant data values for the parameter.
Parameters!<ParameterName>.Label	An array of strings that are labels for the parameter.
Parameters!<ParameterName>.IsMultiValue	Boolean property indicating whether the parameter Multi-value option has been selected.
Parameters!<ParameterName>.Count	The number of values in the array.
Parameters!<ParameterName>.Value(0)	The first value in a multivalued array.
Parameters!<ParameterName>.Label(0)	The first label in a multivalued array.
Parameters!<ParameterName>.Value(Parameters! <ParameterName>.Count-1)	The last value in a multivalued array.
Parameters!<ParameterName>.Label(Parameters! <ParameterName>.Count-1)	The last label in a multivalued array.
=Join(Parameters!<ParameterName>.Value,", ")	An expression that concatenates all the values in the array of a multivalued parameter of type String into one string.
=Split("Value1, Value2, Value3",",")	Takes a string and creates an array of objects that can be used to pass to a subreport or drillthrough report expecting a multivalue parameter.

You can use SPLIT and JOIN functions to separate or combine values in the array in any expression. You can use STRING and CINT functions to convert the values into strings or integers.

For more information and examples of single-value and multivalued parameters in expressions, see Using Parameters in Expressions.

Change History

Release	History
12 December 2006	Changed content: Select All restored for SP2.

See Also

Tasks

How to: Add, Edit, or Delete a Report Parameter (Report Designer)

Concepts

Setting Parameter Properties for a Published Report
Using Parameters in Expressions
Using Parameters in Expressions

Other Resources

Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters
Report Parameters (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance