Display Parameter(Multi-value) in Report

Question

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.

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

score 158 · Answer 1 · answered Nov 18 '08 at 22:21

158

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

=Join(Parameters!Product.Label, ",")

answered Nov 18 '08 at 22:21

Matt Hamilton

200,371
61
386
320

If he is adding the join expr to the header, it would cause the results to grow over the body of the report no? – mirezus Feb 06 '09 at 14:03
Thanks for giving a great answer before I had to tear my hair out while googling! – Pulsehead Nov 27 '12 at 15:37
3

or `=Join(Parameters!Product.Value, ",")` – Doug_Ivison Feb 02 '16 at 18:58
What if you have page breaks on each product? Is it possible to display one product per page? – Pepys Mar 07 '18 at 09:56

score 12 · Answer 2 · answered Mar 09 '09 at 11:55

12

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

answered Mar 09 '09 at 11:55

1

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

score 6 · Answer 3 · answered Mar 09 '09 at 20:18

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

answered Mar 09 '09 at 20:18

Booji Boy

4,522
4
40
45

+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

score 0 · Answer 4 · answered Dec 21 '18 at 09:35

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.

Display Parameter(Multi-value) in Report

4 Answers4

Linked