Share via

Distant Plastic Trees (Multi-Valued Parameters)

  • Article

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

  • Anonymous
    September 14, 2005
    The comment has been removed
  • Anonymous
    September 14, 2005
    bweckler, I posted a comment earlier, you can email me directly if it's easier. mike.zingg@pni.com

    Thanks.
  • Anonymous
    November 16, 2005
    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??
  • Anonymous
    August 14, 2006
    If you've applied Service Pack 1 to your SQL Server 2005 Reporting Services installation (I hope you...
  • Anonymous
    August 16, 2006
    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.
  • Anonymous
    December 07, 2006
    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_ANDELFROM         ELLEN.ELL_EKVAL_ORDINART_HISTORIK_VYWHERE     (AR IN (:ar)) AND (:ar IS NOT NULL) OR                     (:ar IS NULL)Thanks in advance!
  • Anonymous
    December 12, 2006
    This is the first blog of a series about discussing the improvements of installing SQL Server SP2 . If
  • Anonymous
    May 31, 2009
    PingBack from http://portablegreenhousesite.info/story.php?id=12225
  • Anonymous
    June 13, 2009
    PingBack from http://gardendecordesign.info/story.php?id=2965