SSRS: Remove Duplicate Filter Values from Parameter Drop-down using VB code

Article
1/17/2024

Problem Statement

While trying to create SQL Server Reporting Services based on SharePoint List Data we might have to create filter parameters in the report to filter out the main report data. However, when creating filters based on a SharePoint List Column, the data that is pulled from the list can contain duplicate values as shown below. There is no out of the box methods to remove these duplicate values.

However, SSRS provides the option to add Visual Basic code to tackle these kind of situations. We will use Visual Basic code to clear out the duplicates and retain unique values.

How to Overcome it

In addition to the original main parameter we will create a dummy parameter to implement the logic.

Let’s call it as ‘DummyProductsFilter’. Set it as ‘Hidden’ as this will be used only for internal implementation of the logic. We will have another main parameter which is visible in the report UI.

In the Available Values tab, get the values from the report dataset.

Similarly for the default values tab, get the values from the same dataset.

Use Visual Basic code to filter duplicate values

The values from the Dummy parameter will be sent to this code block which will return unique values to the main parameter. What the code does is it initially sorts the array and checks the current item and previous item. Only if they are different, the item will be added to the array to be returned.



      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

The code is added to the SSRS Code section by right-clicking the report area and selecting Report Properties.

Add the Visual Basic code as shown below:

Add Unique Values to the Main Parameter from the VB code

Right-click ProductsFilter main parameter and add the below expression to the ‘Specific Values’ section of Available Values.



      =Code.RemoveDuplicates(Parameters!DummyProductsFilter)

Add it to both Label and Value field.

In the Default Values section add the below expression:



      =Code.RemoveDuplicates(Parameters!DummyProductsFilter)(0)

Test the Filter

Now we have set up the Hidden Dummy Parameter and Main Parameter to get the unique values. Running the report we can see that Nutella which had duplicated values has become unique in the drop down.

Summary

Thus we saw how to remove duplicate values in the SSRS Filter drop down using Visual Basic code.

Share via