81

Can anyone tell me how to display all the selected value of my multi value parameter in SSRS report. When giving parameter.value option it gives error.

Jakub Hampl
  • 36,560
  • 8
  • 70
  • 101
  • 1
    Here's the way to do it using the JOIN function, as part of a larger blog on multivalue parameters: http://www.wiseowl.co.uk/blog/s187/multivalue-report-builder.htm. – Andy Brown Nov 07 '11 at 12:23

4 Answers4

155

You can use the "Join" function to create a single string out of the array of labels, like this:

=Join(Parameters!Product.Label, ",")
Matt Hamilton
  • 188,161
  • 60
  • 377
  • 317
12

=Join(Parameters!Product.Label, vbcrfl) for new line

  • 1
    @nadiObi : It should be [vbCrLf](https://stackoverflow.com/a/27223391/7794769) instead. – stomy Apr 11 '18 at 23:01
6

I didn't know about the join function - Nice! I had written a function that I placed in the code section (report properties->code tab:

Public Function ShowParmValues(ByVal parm as Parameter) as string
   Dim s as String 

      For i as integer = 0 to parm.Count-1
         s &= CStr(parm.value(i)) & IIF( i < parm.Count-1, ", ","")
      Next
  Return s
End Function  
Booji Boy
  • 4,279
  • 4
  • 34
  • 44
  • +1 This works in a report header where the "Join" solution requires a table (which can't go into a report header). – Jeff Sep 08 '17 at 13:31
0

Hopefully someone else finds this useful:

Using the Join is the best way to use a multi-value parameter. But what if you want to have an efficient 'Select All'? If there are 100s+ then the query will be very inefficient.

To solve this instead of using a SQL Query as is, change it to using an expression (click the Fx button top right) then build your query something like this (speech marks are necessary):

= "Select * from tProducts Where 1 = 1 " 
IIF(Parameters!ProductID.Value(0)=-1,Nothing," And ProductID In (" & Join(Parameters!ProductID.Value,"','") & ")")

In your Parameter do the following:

    SELECT -1 As ProductID, 'All' as ProductName Union All
    Select  
    tProducts.ProductID,tProducts.ProductName
    FROM
    tProducts

By building the query as an expression means you can make the SQL Statement more efficient but also handle the difficulty SQL Server has with handling values in an 'In' statement.

Markive
  • 2,271
  • 1
  • 21
  • 25