reporting-servicesssrs-2008-r2ssrs-grouping

SSRS distinct lookupset function


I'm using Join(Lookupset) to find unique group values which returns a sequence number. This is my function:

Join(LookupSet(Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value
    , Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value
    , Fields!CustomerSeqNo.Value
    , "PickingList"), ",")

The problem is on some items there are multiple transactions. I want to remove the duplicates.

I found a blog http://blogs.msdn.com/b/bobmeyers/archive/2012/06/18/creating-short-lists-using-the-lookupset-function.aspx but could not get SSRS Report Builder to reference Linq assembly. My issue is

enter image description here

How can I just show the unique values?


Solution

  • You don't need Linq, but you do still need custom code (in BIDS go to Report -> Report Properties -> Code)

    You can put a RemoveDuplicates function in here, something like this:

    Public Shared Function RemoveDuplicates(m_Array As Object()) As String()
    
        System.Array.Sort(m_Array)
        Dim k As Integer = 0
        For i As Integer = 0 To m_Array.Length - 1
            If i > 0 AndAlso m_Array(i).Equals(m_Array(i - 1)) Then
                Continue For
            End If
            m_Array(k) = m_Array(i)
            k += 1
        Next
    
        Dim unique As [String]() = New [String](k - 1) {}
    
        System.Array.Copy(m_Array, 0, unique, 0, k)
    
        Return unique
    
    End Function
    

    To use it in your Join:

    Join(Code.RemoveDuplicates(LookupSet(...)),",")