Reputation: 23
I have a multi-value parameter (Param_Locations) from which a user can select multiple service locations (e.g. LocationA, LocationA Optical Shop, LocationB, LocationB Optical Shop, ...). The selection of these locations is important for the dataset I will be generating. I am then able to display the selected locations in the header of the report using: =Join(Parameters!Param_Locations.Label).
The issue I have is that I want to filter out any location that has the word "Optical" in the Label. I do not want those locations to show in the report header even though they may have been selected from the drop-down selection list.
I have tried using the Filter() function to no avail. Any ideas would be appreciated.
Regards! Babak C Phoenix, AZ
Upvotes: 1
Views: 3868
Reputation: 8395
You were correct to try the filter method. I tested the below code successfully in SQL Server 2012 SP1.
=Join(Filter(Parameters!Param_Locations.Label, "Optical Shop", False), ", ")
Upvotes: 2
Reputation: 39606
I would use Custom Code to do this; it's just slightly too complex to try and do with the native functions but you can loop through the parameter labels pretty easily in Custom Code:
Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
For i as integer = 0 to parameter.Count-1
If InStr(CStr(parameter.Label(i)), "Optical") = 0
s = s + CStr(parameter.Label(i)) + ", "
End If
Next
s = Mid(s, 1, Len(s) - 2)
Return s
End Function
Call the function like this in the report when you want to display the filtered parameter labels:
=Code.ShowParameterValues(Parameters!Param_Locations)
See Using Custom Code for more details.
Upvotes: 2