Distinct values for SSRS Parameter when query returns Duplicates

Friends,

This issue is very common when you are pulling data from Sharepoint list to load parameter values. In this post let us discuss the solution for the same.

Step 1 : Create a report and Go to the Report Tab at the top.

Step 2 : Go to the Report properties and then code and write the following code in it.

Public Function RemoveDuplicates(parameter As string) As String

Dim value as String

Dim items As Object() = Split(parameter,”~”)

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

If items(i) <> “”

if i =0 then

value = items(i)

else

value = value+”,”+items(i)

End If

End If

Next

Return value

End Function

Step 3 : Create a Dataset which contain all the values that need to be display in the parameter including duplicates. Let’s name it Color Dataset.

Step 4 : Now create a parameter that should take values from the Color dataset. Make it a hidden parameter. Let’s name it Hidden_Param.

Step 5 : Create another parameter that will use to show only unique values in the report parameter. Let’s name it Main_Param, also select multiple values option.

Use following Expression in the Available Value under it specific values on both the labels Add as well as Value .

=Split(Code.RemoveDuplicates(join(Parameters!Hidden_Param.Value,”~”)),”,”)

Step 6 : Use this Main parameter in your main dataset , Dataset properties’parameter to map it.

Step 7 : See the preview and its done

Thanks Swati for the document !!

Regards,
Roopesh Babu V