Post
Ask Question

Remove Duplicate Filter Values From SSRS Parameter Drop-Down

Problem Statement

While trying to create SQL Server Reporting Services, which are based on SharePoint list data, which we might have to create filter parameters in the report to filter out the main report data. However when creating, the filters are based on SharePoint list column from 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 the duplicate values.


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


How to do 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 the duplicate values.

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

Duplicate filter code

  1. Public Shared Function RemoveDuplicates(parameter As Parameter) As String()  
  2. Dim items As Object() = parameter.Value  
  3. System.Array.Sort(items)  
  4. Dim k As Integer = 0  
  5. For i As Integer = 0 To items.Length - 1  
  6. If i > 0 AndAlso items(i).Equals(items(i - 1)) Then  
  7. Continue For  
  8. End If  
  9. items(k) = items(i)  
  10. k += 1  
  11. Next  
  12. Dim unique As[String]() = New[String](k - 1) {}  
  13. System.Array.Copy(items, 0, unique, 0, k)  
  14. Return unique  
  15. End Function  

The code is added to SSRS code section by right clicking the report area and selecting report properties.


Add Visual Basic code, as shown below.


Add Unique Values to the Main Parameter from VB Code

Right click ProductsFilter main parameter and add the expression given below to the specific values section of the available values.

=Code.RemoveDuplicates(Parameters!DummyProductsFilter).


Add it to both label and value field.


In the default values section, add the expression given below.

=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. After running the report, we can see Nutella, which had duplicated values has become unique in the drop-down.


Summary

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


Similar Articles