Using Single-Value and Multivalue Parameters

Using Single-Value and Multivalue P...

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (August 2008)

Using Single-Value and Multivalue 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 clear all values in the list.

In text boxes on the design surface, both single-valued or multivalued parameters appear as simple expressions that begin with the symbol @. For example, a parameter named Store appears as [@Store]. For more information, see Understanding Simple and Complex Expressions (Reporting Services).

To set the multivalue property for a parameter, select the Allow multiple values option on the Report Parameter Properties dialog box. You can set any parameter type to multivalue except Boolean.

The primary use of multivalue parameters is to allow for 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 how to create multivalue parameters, see Tutorial: Adding Parameters to a Report and Creating Report Parameters and Setting Report Parameter Properties.

Security Note:
In any report that includes a parameter of type String, make sure to use an available values list (also known as a valid values list) and make sure that any user running the report has only the permissions that are 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 unintentionally execute the malicious script or link. To reduce the risk of unintentionally running malicious scripts, open rendered reports only from trusted sources. For more information about how to secure reports, see Securing Reports and Resources.

Security Note:

In any report that includes a parameter of type String, make sure to use an available values list (also known as a valid values list) and make sure that any user running the report has only the permissions that are 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 unintentionally execute the malicious script or link.

To reduce the risk of unintentionally running malicious scripts, open rendered reports only from trusted sources. For more information about how to secure reports, see Securing Reports and Resources.

Writing Queries for Multivalue Report Parameters

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

The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue 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, realize that you are circumventing the logic the report server provides to support multivalued parameters.

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, realize that you are circumventing the logic the report server provides to support multivalued parameters.

Filter expressions for datasets, data regions, and groups are defined on the Filter page of the corresponding Properties dialog box. 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 (Reporting Services).

Writing Expressions that Refer to Multivalue Parameters

When you refer to a parameter in an expression, you use the built-in Parameters collection. 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 are set to allow multiple values.

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 Allow multiple values option has been selected.
`Parameters!` <ParameterName> `.Count`	The number of values in the array.
`Parameters!` <ParameterName> `.Value(0)`	The first value in a multivalue array.
`Parameters!` <ParameterName> `.Label(0)`	The first label in a multivalue array.
`Parameters!` <ParameterName> `.Value(Parameters!` <ParameterName>.`Count-1)`	The last value in a multivalue array.
`Parameters!` <ParameterName> `.Label(Parameters!` <ParameterName>.`Count-1)`	The last label in a multivalue array.
`=Join(Parameters!` <ParameterName> `.Value,", ")`	An expression that concatenates all the values in the array of a multivalue 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 Collection References in Expressions (Reporting Services).

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads

Tasks

Concepts

Other Resources

Help and Information