Using Single-Value and Multivalue Parameters in Queries and Expressions (Report Builder 3.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 Parameters (Report Builder 3.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.
.gif "Security note") 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. For more information, see Security (Report Builder 3.0). |
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, SQL Azure, SQL Server 2008 R2 Parallel Data Warehouse, 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 SQL Server Books Online.
SELECT FirstName, LastName, e.JobTitle
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityIDID
WHERE e.Title IN (@Title);
Note
The version of the data provider that you use determines the support for parameter syntax. If a data provider does not support named parameters, use the syntax that is supported by the external data source. For example, in some versions of SQL, a question mark (?) is used to indicate a parameter.
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 to a Dataset (Report Builder 3.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 information about creating cascading parameters with multiple values and including the Select All feature, see How to have a Select All Multivalue Cascading Parameter.
For more information and examples of single-value and multivalued parameters in expressions, see Using Parameters Collection References in Expressions (Report Builder 3.0).
See Also
Tasks
How to: Add, Change, or Delete a Report Parameter (Report Builder 3.0)
Reference
Report Parameter Properties Dialog Box, General (Report Builder 3.0)
Concepts
Using Parameters Collection References in Expressions (Report Builder 3.0)