Distant Plastic Trees (Multi-Valued Parameters)

One of the cool new features of SQL Server 2005 Reporting Services is multi-valued parameters. SQL Server 2000 Reporting Services only allowed you to have a single value for a parameter. For example, if your parameter was color, you could pick red or blue but not red and blue. Using expression-based queries, you could get around this limitation with by passing in the delimited strings as a string parameter. However, if you weren't careful with parameter validation, this could leave your report open to a SQL injection attack. With SQL Server 2005 Reporting Services, you can simply mark the parameter as multi-valued in the Report Parameters dialog.

After setting your parameter to multi-valued, you then need to update your query. If you are usng SQL Server relational, you use syntax like:

SELECT sales FROM product WHERE color IN (@Color)

The data extension generates the correct comma delimited strings and handles the quoting for string data types. Oracle is similar except that it uses the :parameter syntax instead of @parameter. If you are using Analysis Services as your data source, the multi-values will be automatically added. If you look at the MDX generated, it might look like:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS FROM (SELECT (STRTOSET(@Color, CONSTRAINED), *) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE

If you switch a report over to use multi-valued parameters, you need to watch the parameter values in your report. The .Value propety of a parameter returns an array, not a scalar. For example, if you referenced the a multi-valued parameter via =Parameters!parametername.Value or =Parameters!parametername.Label in a textbox in the body of your report, you will see a #error when you preview. To get around this, you can use the Join function. Join creates a single string from an array of strings with an optional delimiter.

For example, if you wanted a comma seperated list of parameter values, you could use:

=Join(Parameters!parametername.Value, ", ")

Similarly, if you want to pass a set of values into a drillthrough report that has a multi-valued parameter, you can use the Split function.

Although the only built-in data extensions that support multi-valued parameters are SQL Server, Oracle, and Analysis Services, your custom data extension can also support them by implementing the optional IDataMultiValueParameter interface. Information on this interface will be available with SQL 2005 Books Online.