Sum Result of SSRS LookupSet Function

Monday, May 27, 2013
by jsalvo

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  
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"))


comments powered by Disqus