SSRS: Sum() LookupSet Function

Here’s a handy piece of coding which might help you:
http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/

You may use the SumLookup function to sum the results from LookupSet:
=Code.SumLookup(LookupSet(Fields!Parent_Number.Value,Fields!Parent_Number.Value,
Fields!StoreCount.Value, "StoreCount"))

Or use a combined ‘key’ to perform the lookup:
=Code.SumLookup(LookupSet(Fields!GroupName.Value & Fields!WeekNumber.Value,Fields!GroupName.Value & Fields!WeekNumber.Value,Fields!NrOfItems.Value,"DataSet1"))

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

Advertisements

3 thoughts on “SSRS: Sum() LookupSet Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s