Report Builder / Reporting Services: Multi Value Column Printed in a Single Textbox

I got a request to print a column with multiple values in a single textbox. The first result looked like the upper part of the picture; the second one like the one below.

In my example I use the Noordenwind database with this SQL query, called DataSet2:



SELECT     dbo.Orders.[Order-id], dbo.Orders.Klantnummer, 
dbo.Producten.Productnaam
FROM         dbo.Orders INNER JOIN
             dbo.Orderinformatie ON dbo.Orders.[Order-id] = dbo.Orderinformatie.[Order-id]
INNER JOIN Producten ON Producten.Productnummer = dbo.Orderinformatie.Productnummer
Where Klantnummer ='VINET'



Data:



10248 VINET Queso Cabrales
10248 VINET Singaporean Hokkien Fried Mee
10248 VINET Mozzarella di Giovanni
10274 VINET Fløtemysost
10274 VINET Mozzarella di Giovanni
10295 VINET Gnocchi di nonna Alice
10737 VINET Konbu
10737 VINET Jack's New England Clam Chowder
10739 VINET Inlagd Sill
10739 VINET Filo Mix



In the first example I used a textbox with this expression:

=join(LookupSet(First(Fields!Klantnummer.Value, "DataSet2"), Fields!Klantnummer.Value, Fields!Productnaam.Value, "DataSet2"), ", ")

As you can see, we get duplicate values and the values are not sorted. To get this done I needed a function:



Function RemoveDuplicates(objItems As Object()) As Object()
System.Array.Sort(objItems) 

Dim intK As Integer = 0

For intI As Integer = 0 To objItems.Length - 1
If intI > 0 AndAlso objItems(intI).Equals(objItems(intI - 1)) Then
Continue For
End If
objItems(intK) = objItems(intI)
intK += 1
Next

Dim objSortedUnique As [Object]() = New [Object](intK - 1) {}

System.Array.Copy(objItems, 0, objSortedUnique , 0, intK)

Return objSortedUnique 
End Function

This function you have to add to the report property code:

This function we have to add to the expression in the textbox, like this:



=join(Code.RemoveDuplicates(LookupSet(First(Fields!Klantnummer.Value, "DataSet2"), Fields!Klantnummer.Value, Fields!Productnaam.Value, "DataSet2")), ", ")



Like we can see in the second example, the values are sorted and unique.