I'm building a report in BIDS based on a SharePoint list. I want to make a multi-valued parameter to filter the list by a name, but each name shows up multiple times in the list. The report is already going to be filtered by work site, and I need this filter in addition. I'm pretty new to SSRS, so there may be a simpler solution, but I've thought of two ways this could be done, though neither is particularly efficient.
One way is to create a separate MVP for each site, and manually write out each name for that site in the parameter's Available Values. The problem is that I don't think there's a way to hide a parameter based on which site you're looking at; for instance, if I'm looking at Site A, the drop-down lists for Sites B-Z would all also show up. They wouldn't do anything, since the superfluous sites would already be filtered out, but it'd be an eyesore. This approach would also be difficult to maintain in the long-run, requiring someone to come in and manually add every new name to the parameter.
The other, better approach is to add a new dataset with only names and site IDs, and find some way to filter out every repetition of a name, so all that's left is the name and the associated site. I have no idea how this could be done, though.
Finally found an explanation: http://blogs.msdn.com/b/sqlforum/archive/2011/04/28/walkthrough-how-to-get-distinct-values-of-a-column-of-a-sharepoint-list-using-sql-server-reporting-services.aspx
Essentially, this has you create one parameter to serve as a sorta jury-rigged dataset based on a piece of custom VB code. That first parameter is then used to populate a second, multi-valued parameter, which contains all unique values.
The VB code:
Public Shared Function RemoveDuplicates(parameter As Parameter) As String()
Dim items As Object() = parameter.Value
System.Array.Sort(items)
Dim k As Integer = 0
For i As Integer = 0 To items.Length - 1
If i > 0 AndAlso items(i).Equals(items(i - 1)) Then
Continue For
End If
items(k) = items(i)
k += 1
Next
Dim unique As [String]() = New [String](k - 1) {}
System.Array.Copy(items, 0, unique, 0, k)
Return unique
End Function
NB: When you set up the dataset in this process, make sure to alter the query to reflect your own data source; don't just copy/paste blindly.