Aggregated Lookups in SSRS

SSRS 2008 R2 has a nice function to be able to lookup another dataset. Using Lookupset you can return a list of entries in another dataset that match your specified values. The structure of the function is as follows:

Lookupset([Source Expression], [Destination Expression], [Result Expression(Value to Retrieve)], [Dataset])

As an example, this passes the customer address ID from the current Dataset to match the Address ID in the AddressesDataset and Returns the matching postcode.

 Lookupset(Fields!CustomerAddressID.Value, Fields!AddressID.Value, Fields!Postcode.Value, "AddressesDataset")

This is all well and good, but what if there are multiple matching entries that you want to aggregate? In my case - Sum. There isn't a function out of the box in SSRS that I'm aware of, but I found this very handy little piece of code. (This could be easily modified to aggregate in some other way should you wish)

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

Drop this into the 'Code' area and then simply call it in your expression and pass the lookupset to it. The resulting value will be the sum of the called entries

Code.SumLookup(Lookupset([Source Expression], [Destination Expression], [Result Expression(Value to Retrieve)], [Dataset]))

 

As an example, this matches the client again but returns a sum of that clients billed invoices:

Code.SumLookup(Lookupset(Fields!ID.Value, Fields!CustomerID.Value, Fields!Billed.Value, "InvoicesDataset"))