SSRS - Use Join() inside a tablix outside a group

1
votes

I am using Microsoft SQL Server 2017 with Reporting Services and Report Builder 2016.

I got a DataSet which looks like:
DataSet example

I've already created a tablix which groups by Name and shows each Name one time and the count of the Name in the table. Something like this:

4 x Foo
2 x Bar

Now, I want to get a text containing all comma separated Names (each Name only one time) and a text containing all comma separated Name counts. It should look like this:

Foo,Bar
4,2

How is this possibile using SSRS Report Builder?

I already tried:

  • Use Join() inside tablix (not working)
  • Use a List instead of a tablix (generally working but it repeats the items vertically and not horizontally)
2
sql-serverreporting-servicesreportbuilder

2 Answers

0
votes

Using the built-in functions you can combine LookupSet with Join like so:

=Join(LookupSet(Fields!ID.Value, Fields!ID.Value, Fields!GRADE.Value, "DataSet1"), ", ")

Hopefully this explains how to use the Join function and reassures you that it is indeed possible. It also doesn't matter if you use a Table, List, or Matrix or if you are in a group or not.

However, this does not remove duplicate values or provide any sorting options. In order to do that, you have to add a custom function in the report properties. Once you do, the expression would look like this:

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

There are several examples of this VB code you can search for or implement yourself if you need this functionality.

0
votes

The code you would want is:

Public Shared Function RemoveDuplicates(ByVal items As Object()) As Object()

    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 = k + 1  
    Next  

    Dim unique As[String]() = New[String](k - 1) {}  
    System.Array.Copy(items, 0, unique, 0, k)  
    Return unique  
End Function

You can use this via an expression using a lookupset() either within a region associated to a dataset or outside.

For outside, you can use:

=join(Code.RemoveDuplicates(lookupset(1,1, Fields!Name.Value, "DataSet1")), ", ")