[SOLVED] sum two values from different datasets using lookups in report builder

sum two values from different datasets using lookups in report builder

I have a report that should read values from 2 dataset by Currency:

Dataset1: Production Total
Dataset2: Net Total

Ive tried to use:

Lookup(Fields!Currency_Type.Value, 
       Fields!Currency_Type1.Value,
       Fields!Gross_Premium_Amount.Value, 
       "DataSet2")

This returns only the first amount from dataset 2.

I've tried Lookupset function as well but it didn't SUM the retrieved values.

Any help would be appreciated.

Solution

Thanks Jamie for the reply. THis is what i have done and it worked perfect: From Report Properties--> Code , write the below function:

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)
Next
If (ct = 0) Then return 0 else return suma 
End Function

Then you can call the function:

code.SumLookup(LookupSet(Fields!Currency_Type.Value, Fields!Currency_Type1.Value,Fields!Gross_Premium_Amount.Value, "DataSet2"))