The Request
I have a report that has two data sets that I am using a lookup to get the value needed. The returned value could be either "Completed", "In Progress", "Not Started". What I want to do is get the Sum of the "Completed"  data elements returned. I have used this expression to look up the data set

=join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),",")

When I use the above expression I get a column with the values concatenated within the text box like this:
-- Completed, Completed, Completed, In Progress, Not Started --

What I want to do it get the sum of the "Completed" occurrences. So the answer I am looking for is "3".

The Solution
You can use custom code in your report. Since you would be using all core VB.Net functionality you would not need to add any custom references. Here is what I did:

Function SumOccurrence(s As String, f As String) As Integer 
Dim rtn As Integer = 0
Dim occ As String() = s.Split(",")
For Each el As String in occ
If el = f Then
rtn
= rtn + 1
End If
Next
Return rtn
End Function

So the number of "Completed" Activities could be found with:

=Code.SumOccurrences(join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),","), "Completed")

So the number of "In Progress" Activities could be found with:

=Code.SumOccurrences(join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),","), "In Progress")

Etc.

Related Info
Forum Post


See Also