SSRS provides several built-in Lookup functions:  Lookup, MultiLookup and LookupSet.  Lookup returns a single value while MultiLookup and LookupSet return a set of values. If you want to learn more about the SSRS lookup functions check out this blog post. 

In some situations, we may want to sum the set of values returned by MultiLookup and LookupSet.  The code shown below seems like a logical approach to accomplish this:

=Sum(LookupSet(Fields!Parent_Number.Value, Fields!Parent_Number.Value,Fields!StoreCount.Value, “StoreCount”))

Unfortunately, the built-in Sum function doesn’t work in this scenario since LookupSet returns an array of objects.  To sum the result of the LookupSet function, we need to add custom code to the SSRS report.

To add code to an SSRS report, right-click on the report background and select ‘Report Properties’. 

In the ‘Report Properties’ menu, click ‘Code’

Paste the following code in ‘Custom code’ field:

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct += 1
Next
If (ct = 0) Then return 0 else return suma
End Function

We can now use the SumLookup function to sum the results from LookupSet.

=Code.SumLookup(LookupSet(Fields!Parent_Number,Fields!Parent_Number.Value,Fields!StoreCount.Value, “StoreCount”))