Removing Duplicate Rows from a Table in Bold Reports

When designing reports, it’s common to encounter the need to remove duplicate rows from a table to ensure data accuracy and integrity. This article provides a step-by-step guide on how to create a function using Visual Basic (VB) code to remove duplicate values from a comma-separated list in a report using the Code Module in Bold Reports.

Step-by-Step Guide

1. Select the tablix data region within the design area of your report. This action will enable the Grouping Panel in the design view.

2. Click on Details in the Grouping Panel. In the Basic Settings, select Set Groups. This will open the Grouping dialog. Here, you should group by ID or another unique identifier and then click OK.
   

   

3. Click on the outer grey surface of the report area, and then click on the code option in the Properties section of the report.
   

   

4. In the code editor that appears, insert the following VB code snippet. This function will remove duplicate entries from a comma-separated string:

   Function RemoveDuplicates(input As String) As String
       Dim values As New System.Collections.Generic.List(Of String)
       Dim output As String = ""  
       For Each item As String In input.Split(","c)
           If Not values.Contains(item.Trim()) Then
               values.Add(item.Trim())
               output += item.Trim() + ", "
           End If
       Next
       If output.Length > 2 Then
           ' Remove the trailing comma and space
           output = output.Substring(0, output.Length - 2)
       End If
       Return output
   End Function
   

   

5. To apply the function within your report, use it in an expression to clean up fields with potential duplicate values. For instance, to remove duplicates from an address field, use the expression:

   =Code.RemoveDuplicates(Join(LookupSet(Fields!ID.Value, Fields!ID.Value, Fields!address.Value, "DataSet1"), ", "))
   

   

Preview snapshot:

By following these instructions, you can remove duplicate rows from a table in a report. This method ensures that your data is clean and reliable for analysis and reporting purposes.

You can download the sample report from here.