1

enter image description hereI have a report item which is based on a calculation from 2 different entities. I am trying to group the report based on the product number.But I am not able to get the total for each group. But found a way to get the grand total from the report using the following code. I want to find a way to aggregate the total for each product and set it to zero when it moves to the next product.

Already tried: Creating a separated calculated field to the data-set but as it is from from 2 different data sets so I am not able to create a calculated field. Tried referencing the aggregate elsewhere in the report and tried to reference it, did not work.

Public totalReplacement as Integer
Public totalGSF as Integer

Public Function AddTotalReplacement(ByVal replacement AS Integer )
totalReplacement = totalReplacement + replacement
End Function

Public Function AddTotalGSF(ByVal thisGSF AS integer)
totalGSF = totalGSF + thisGSF
End Function

Public Function GetTotalReplacement()
return totalReplacement
End Function

Public Function GetTotalGSF()
return totalGSF
End Function

The above code keeps adding the total of each product with the before total. But I wish to the make the total replacement zero when it move to the next product.

VJ4407
  • 29
  • 5
  • would help if you show what you are getting now as an image and what your desired result is in a mock up of some sort.. Usually if you just group by the product you can get a sub total per group and a grand total at the end.. show the layout / design view.. might help – Harry Apr 08 '19 at 04:34
  • @Harry I have added the output screenshot which I am getting now. In the Column AB - you can see that its adding all the **Created_issued to Field Qty** (AA) for particular product number in A. for the product 10000182 you can see 3 and 8 quantities in the AA column but I am trying to use the code to calculate the sum of that particular product it returns as 33. It's adding 22+11, but I wish to see only 11. – VJ4407 Apr 08 '19 at 05:16

1 Answers1

0

Alright Do something like Below

  1. Switch to Design view.
  2. Right-click the data region cell that contains the field [Created_issued to Field Qty (AA)], and click Add Total.
  3. This adds a row with a sum of the dollar amount for each order.

Try this and it shall give you 11 as Result. Let me know if this works for you.

AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • Created_issued to Field Qty does not allow me to do that as it is a reportitem. It is based on calculations from 2 different data-sets. I was able to do that in the **Vendor Qty(X)** and **Received Qty(Y)**, it is also calculated field but from the same data-set. – VJ4407 Apr 08 '19 at 07:27