Display Parameter(Multi-value) in Report

小开

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

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

小开

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

小开

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

小开

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.