using aggregates in lookupset using two dataset

• ndengineer

  SSC Enthusiast

  Points: 141

  More actions

  October 21, 2015 at 3:54 pm

  #301667

  Hello,

  I have two dataset Sales , Budget.

  My Sales dataset has parameter as YTD/MTD. Depending on the selection i have to display the aggregated budget fields as

  If @parameter = YTD then Budget_Turnover_YTD \ Budget_QTY_YTD Else

  Budget_Turnover_MTD \ Budget_QTY_MTD

  (Imp: @Parameter is from sales dataset and its not in Budget dataset)

  I have included the calulation in the Budget dataset as calculated field Budget_Final.

  I use the following expression to display the above mentioned calculated field :

  =Code.SumLookup(Lookupset(Fields!Item_number.Value,Fields!Item_number.Value,Fields!Budget_Final.Value,"Budget"))

  But it the values under Fields!Budget_Final doesnt get calculated properly and shows wrong figures.

  So if any one can please guide as what solution needs to apply.

  Thanks,

  Nish.

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  October 22, 2015 at 3:34 am

  #1834841

  Care to post the custom code?

• ndengineer

  SSC Enthusiast

  Points: 141

  More actions

  October 22, 2015 at 9:40 am

  #1834950

  Here is the custom code i am using for aggregates in LookupSet :

  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