controller controller - 1 year ago 436 Question

SSRS code to determine distinct count of a lookupset

I am trying to create custom code, coupled with an SSRS expression, that will allow me to display the number of distinct values in a lookupset array from another dataset ("Faults"). The expression in the target dataset ("Stations") will look like this:

=code.CountDistinct(Lookupset(Fields!StationNumber.Value,Fields!OpID.Value, Fields!OpID.Value, "Faults"))

Unfortunately I can't seem to get the CountDistinct function to work. I have functions that allow me to determine the max, min, and average of a lookupset, but I can't find anything to help me determine the distinct count of items in the lookupset.

I am not a VB expert, but I have been able to create the other simple functions with little trouble. I can't figure out what to do for the CountDistinct function, though.

Any help would be appreciated. Perhaps there's a better way to find the distinct count and reference it in another dataset in SSRS, besides using custom code? If so, please advise.

Answer Source

Give this a try in the code for your report. I just modified the code a little from the other answer to return an integer which is the count of distinct values. Reference.

Public Shared Function CountDistinct(m_Array As Object()) As Integer

    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
    Return k

End Function

I am not sure this code has changed enough to qualify as a new answer. I certainly don't want anyone to think I am plagiarizing any code.