SSRS: How to get a distinct list of values from an input parameter of field

Today I was building a SSRS report which should display the unique values of a column/field in the header of the report (still with me?).

Basically I had 3 report parameters which query the SSAS cube for values where ‘Measure1’ exists or > 1. Turns out that the list of customers which have at least one record in the cube, was quite large. Now the business asked me to list all the customer names which were selected in the report (via the input parameter), but this will fill up a few pages…

Another idea was to look at the MDX query result set and try to get a distinct list of values.

I stumbled upon this blog post which has a VB (?) function that splits a list in values and then checks for duplicate values (by ignoring them).
With an extra line of code (“System.Array.Sort(SpStr)“) I was able to sort the list of values also.


Here’s an example:
ssrs-distinct-list-of-values-from-list-parameter-values-example











The expression I used for this example:
="Distinct values: "+Code.RemoveDups("5,5,1,2,2,3,3,3,4,4,4,4,5,5,5,5,5")


Getting practical:
In my report, I’ve made a new internal @ListOfCustomers parameter which selects a single column/fields from your main dataset (result set) which you want to display in the header of the report. NOTE: Make sure to check the ‘Allow multiple values‘-checkbox and for your Available/Default-values, only select the CustomerName-field you’ll be using.

Now, for the textbox in the header of the report you may want to use an expression like:
=Code.RemoveDups(JOIN(Parameters!ListOfCustomers.Label, ", "))


The code I used:

Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()

Dim SpStr
SpStr = Split(items ,",")
System.Array.Sort(SpStr)

For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Trim()) Then
noDups.Add(SpStr(i).Trim())
End If
Next

Dim uniqueItems As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueItems)

Return String.Join(",", uniqueItems)
End Function



Copy/Paste this into the Code-section of your Report properties window:
ssrs-distinct-list-of-values-from-list-parameter-values-code








Advertisements

2 thoughts on “SSRS: How to get a distinct list of values from an input parameter of field

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s