Report: show sum Sales Line.Quantity in PageLoop Header

Hi all,

In report 6631 Return Order Confirmation I would like to calculate the sum of all Sales Line Quantities. This quantity is the number of packages for the Return order. I use the following code on the OnAfterGetRecord trigger of Data Item Sales Line:

gDecTotalOrderQty := gDecTotalOrderQty + Quantity

I’m not sure if this is the best way to calculate, but I think this should work.
But now I would like to print this total in the PageLoop, Header section of the report, so before the Sales Lines are processed.

Is this possible?

You need to define one record variable for Sales Line and then put the value in variable using repeat until loop as you want to show the value prior to processing the sales line.

Hi Michiel,

I was a bit curious to know that why do you need such a customization. The reason I am aksing is because

  1. In case of a return Order with multiple Items (different Items) would show the sumed up quantity of all items (which does not makes sense)

  2. In case of a return order with same item but different UOM would again show the sumed up quantity (imagine adding 10 pound + 5 Box)-- even this doesnt makes sense.

I am not challenging your business requirement but was little bit curious. However if your business requirement needs this then

CASE-I — Define a variable of Sales line and filter it on Document Type, Document No and do a calcsum on the quantity field---- make sure you define it as sumindexfield in the key… (write the code in the onaftergetrecord of sales header (first dataitem)

CASE-II— Make a flow field in sales header to show the sum of quantities. In the report go to the property of the first data item (sales header) and define the new flowfield that you would make in the property ‘CalcFields’… and use this value from the sales header field.

Note :- Choose CASE-II only if you have not defined to many flowfields already or you do not plan to do so in near future. (To many flow fields will decrease the performance of your databse)

/Anup

Hi Amol,

Ooops, that’s a little bit too short explanation. Much appreciated, but can you be a bit more specific?

I’m not a developer…

Hi Anup,

Totally true what you are saying here, but in this case, I copied the standard report for a customer that really only has items with a BUOM of PCS. And the sum equals the total numbers of packages in the Return Order.

CASE-I looks promising, but I do not want to add any field to a table, if not really necessary. Is the routine you are describing on a report level? Could you be a little bit more specific, perhaps with an example piece of code? You will definitely have the report mentioned in your DB :wink:

If this is a RTC report done with Reporting Services, you can let the total happen in the footer of each page (even hide it if you want, it just has to exist) then use ReportItem![label].value. In my case, it looks like ReportItem!textbox44.value. It is ok that it is in the header and referencing something in a footer… it still works. Hope that helps!

Hi Michiel,

Follow the steps below:–

  1. Open the Sales Line table in Designer Mode–>View->Keys (Define Quantity in sumindexFields for the first Key)–> Save & Exit the object

  2. Open the Report in Designer mode–>View–>C/AL Global–>Define SalesLine (change the name if the name already exists) and define the Data as Record and SubType as Sales Line

  3. Select the first DataItem (Sales Header)–>Press F9—>Write the following–> In the OnAfterGetRecord trigger–>

SalesLine.RESET;

SalesLine.SETRANGE(“Document TYpe”,“Document Type”);

SalesLine.SETRANGE('Document No.",“No.”);

SalesLine.CALCSUMS(Quantity);

  1. Display the value of the field Directly in the section… in the source expression of the Textbox write-- SalesLine.Quantity.

Ths will solve ur purpose… :slight_smile:

/Anup

Hi Teresa,

Thanks for your reply, I’m working with a good old classic report though…[:D]

Thanks you very much Anup: works like a charm!