• Article

Using Single-Value and Multivalue Parameters (Report Builder 2.0)

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, a Select All option on the report toolbar drop-down list automatically appears. 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 (Report Builder 2.0).

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 Filter Report Data (Report Builder 2.0) and Creating Report Parameters and Setting Report Parameter Properties (Report Builder 2.0).

Security noteSecurity 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" in the Reporting Services documentation in SQL Server Books Online.

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)" in the SQL Server 2008 documentation in SQL Server Books Online.

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.

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 (Report Builder 2.0).

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 (Report Builder 2.0).