Share via

Counting Occurrences in a Concatenated String

  • Article

*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 IntegerDim rtn As Integer = 0 Dim occ As String() = s.Split(",") For Each el As String in occ If el = f Thenrtn = rtn + 1 End IfNextReturn rtnEnd 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