SSRS Distinct LookupSet Issue

Question

I apologize for the long post but I'm losing my mind here. I've tried looking this up but I keep getting error messages on any suggested fixes on this thread:

SSRS distinct lookupset function

I've even tried to completely recreate a similar data set in that question but keep getting issues.

This is the data set I created.

Using this in the expression box, and grouping by itemID, rackID, UseByDate

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

I get

but I would like to remove the duplicates in the LookupSet so it would just display "1".

I tried the first 2 options in that link above but they both provided an error message:

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

with this expression:

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

returns this warning:

[rsRuntimeErrorInExpression] The Value expression for the textrun 'CustomerSeqNo.Paragraphs[0].TextRuns[0]' contains an error: Operator '&' is not defined for type 'Integer' and type 'CalculatedFieldWrapperImpl'. and this error

The other solution doesn't even deploy. Any help here?

score 1 · Accepted Answer · answered Apr 09 '20 at 20:31

Luckily for you @JMG, I just had to do this for a customer!

Here's the function:

public function DistinctValues(input() as Object) as string
    dim newList as String

    for each n as string in input
       if InStr(newList, cstr(n) + ", ") = false
           newList += cstr(n) + ", "
       end if
    next

    return left(newList, len(newList) -2)
end function

So what it's doing is parsing through each value in the array. We are going to insert each unique value into a comma delimited string. Before doing so, we just check the string with InStr to see if that value already exists.

Make sure you cast the return value to string via CSTR(Fields!CustomerSeqNo.Value) to avoid any datatype issues. Your code should look something like this.

Code.DistinctValues(LookupSet(Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value, Fields!itemId.Value & Fields!UseByDate.Value & Fields!rackId.Value, CSTR(Fields!CustomerSeqNo.Value), "PickingList"))

SSRS Distinct LookupSet Issue

1 Answers1