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"), it returns only the first amount from dataset2

Ive tried Lookupset function also but it didnt SUM the retrieved values.

Any one can help please.

Regards

From Report Properties-> Code, write the below code:

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 by:

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

Regards,

• Marked as answer by Gorgo Sabbagh Thursday, November 01, 2012 12:01 PM

From Report Properties-> Code, write the below code:

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

Then you can call the function by:

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

Regards,

Just missing a ct increment inside the foreach loop (corrected in quote)

• Proposed as answer by Homoro Thursday, January 09, 2014 3:24 AM

Dear all,

many thanks for that solution - I just copied it successfully, itsaved me tremedous work(arounds)

One question - what is the ct variable for?
If you leave it out, it works as well

suma is initialized with 0 and if the array has no element the foreach loop is never executed and suma stays 0, you can return suma anyway.

Peter-Christoph

Does this work for regular Lookup aswell?