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. 


Comments (9)

  1. zinggm says:

    bweckler, you seem to know what you’re talking about regarding multi-valued params in 2005 RS. I am running the April CTP of SQL Server 2005. I am having a problem with a multi-valued param. I have a simple example, a report displaying last name and first name, running off a stored proc that has this code:

    ALTER PROCEDURE dbo.usp_Name2

    @LN varchar(1000) = null

    AS

    –select @LN

    declare @sql varchar(8000)

    set @sql =

    ‘SELECT LN, FN

    FROM dbo.Name2

    WHERE LN in ( ‘ + @LN + ‘)’

    exec(@sql)

    RETURN

    ——-

    If I run the stored proc or the dataset withini RS, and supply two last names that would be available for the param, as ‘DOE’, ‘WAYNE’; I get two records returned accordingly. However, when I run the report, select multiple values, I get the following error: "Invalid column name ‘DOE’. Invalid column name ‘WAYNE’. Any ideas? Oh, I also have your JOIN code in the dataset properties for Parameter: JOIN(Parameters!LN.value, ", "). Thanks if you can help.

  2. zinggm says:

    bweckler, I posted a comment earlier, you can email me directly if it’s easier. mike.zingg@pni.com

    Thanks.

  3. Alex says:

    SELECT F_SHORT_DESCRIPTION, F_FULL_DESCRIPTION, F_INCIDENT_NUMBER, F_ACCIDENT_DATE, F_STATUS, F_PAID_LTD, F_ESTIMATE_YOURS,

    F_SPARE_07_CODE, F_SURNAME, F_GIVEN_NAME

    FROM PROGRAMMER.ACCIDENTBYEMPLOYEE

    WHERE (F_ACCIDENT_DATE BETWEEN : FromDate AND : ToDate) AND (SUBSTR(F_SHORT_DESCRIPTION, 1, 3) | | ‘ – ‘ | | SUBSTR(F_FULL_DESCRIPTION, 1,

    100) = : Company)

    Hi There,

    I have an oracle db which i have successfully connected too, however i am having trouble passing my :parameters… i keep getting all sorts of errors do i have to declare them in the dataset query??

  4. If you’ve applied Service Pack 1 to your SQL Server 2005 Reporting Services installation (I hope you…

  5. UT says:

    Hi Brian,

    I had posted a comment about multi-valued parameter before, but then I have also found an answer on your blog here (unbelievable). My issue was solved by using Join function.

    Thanks and Regards,

    UT.

  6. Jessica says:

    Hi,

    I’m using the orignal version of RSS with multivalue function enabled. I have oracle db which use multivalue parameter to pass my query parameter. But I got no row back from db if multiple value were selected. It works fine with single value.

    Is it because of different datatype? datafield "AR" has datatype NUMBER and parameter type is string. How I can get round this problem?

    Min query:

    SELECT     AR, VECKA, EKVAL, PLANERAD_VIKT, LEVERERAD_ANDEL

    FROM         ELLEN.ELL_EKVAL_ORDINART_HISTORIK_VY

    WHERE     (AR IN (:ar)) AND (:ar IS NOT NULL) OR

                         (:ar IS NULL)

    Thanks in advance!

  7. Mike's Blog says:

    This is the first blog of a series about discussing the improvements of installing SQL Server SP2 . If