Designing Data Access - Professional SQL Server 2005 Reporting Services - page 147

this returns an error. I can check the query parameter mapping in the data set, and it's set to
Parameters!ProductSubCategories.Value as you would expect. How is this possible?
When you check the box in the Report Parameters dialog to enable the Multi-value feature, the parameter
value (and Label ) properties are managed as an array instead of single value. If you use the expression
builder to reference the parameter, you'll see that it references only the first element in the multi-select list,
using index zero:
=Parameters!ProductSubCategories.Label(0)
Why zero and not one for the first element? It's a programming thing. In Visual Basic, when arrays and
collections are enumerated, the first index value is zero . This means that if there were four elements, the
indexes would be 0 through 3.
Now, back to the query for the main data set. Behind the scenes, the array is parsed and selected values
are converted to a comma-delimited list for use in the query. To use these values in a similar manner
within the report requires some programming. The following Visual Basic function may be used to con-
vert the parameter Label property array into a string value for use in the report header (see Figure 5-27):
' ************************************************************************
'
' Accepts parameter array of any data type
' and returns a comma-delimited string of
' parameter values.
'
' Paul Turley, 11/17/06
'
' ************************************************************************
Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = “”
For Each sParamItem In Parameter
If sParamItem Is Nothing Then Exit For
sParamVal &= sParamItem & “, “
Next
'-- Remove last comma & space:
Return sParamVal.SubString(0, sParamVal.Length - 2)
End Function
Figure 5-27