SSRS code to determine distinct count of a lookupset

Question

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.

This might help you: http://stackoverflow.com/questions/27047483/ssrs-distinct-lookupset-function. Change the code to return unique.Count rather than just unique. Should get you most of the way there. — R. Richards, Jul 04 '16 at 15:57
I have seen that question, but was unsure how to modify the function to return the number of values in . For that reason, I didn't reference the link in my question. — controller, Jul 04 '16 at 16:33

score 2 · Accepted Answer · edited May 23 '17 at 11:45

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

    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
    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.

SSRS code to determine distinct count of a lookupset

1 Answers1