GroupTotal on Integer DataItem

jknauft

I'm modifying the standard Sales Order form. I need to sort by Item No. and then create a subtotal of quantity and line amount for each change of Item No. Due to the use of Variant codes there will be several lines on the Sales Order for each Item No.

I've created hundreds of reports before with group totals but never on an integer dataitem. Is this possible? What's the best way to accomplish this? Thanks.

Jon

awarn

Hi,

There are probably a number of ways to do this, but here is my first thought.

1 - Populate a temporary table on the Sales Line dataitem. This could possibly be a new temporary table with the fields Entry No. (PK), Item No., Quantity, Line Amount.

2 - Create a new dataitem for displaying the results of the temporary table. Start with showing each line of the temporary table in a Body section (to verify that everything is entered properly).

3 - Create a new Body section, to only show when you are on a totalling line. Here is where it might get a bit tricky. You have to only show this section when you have completed an item. I am not sure if there is a slicker method to do this that isn't as the below (which I call the 'Brute Force Method'), but I have done this kind of code before, and it does accomplish the task.

Example:
1, A, 4, 3.00
2, A, 6, 4.50
3, A, 4, 3.00
4, B, 1, 5.00
5, B, 1, 5.00
6, C, 3, 6.00

I would try to create two vairables for the temp table. The code would look something like this:

OnPreDataItem

tmpItems.reset;
SETRANGE(Number,1,tmpItems.count);
bFirst := true;


OnAfterGetRecord

if bFirst then begin
bFirst := false;
tmpItems.findfirst;
end else
tmpItems.next;

PrintGroupFooter := false;
tmpItems2 := tmpItems
if not tmpItems.next then
PrintGroupFooter := true
else if tmpItems.ItemNo <> tmpItems2.ItemNo then
PrintGroupFooter := true;

if PrintGroupFooter then begin
tmpItems2.reset;
tmpItems2.setrange("item No.",tmpItems."Item No.");
tmpItems2.CALCSUMS(amount,Quantity);
end;

//Now in the Integer dataitem, OnPreSection
currreport.showoutput := PrintGroupFooter;
//the textboxes on the forms should be based on the tmpItems2 vairable.

I hope this helps (and makes sense), the code is off the top of my head.

-a

Revolution1210

This would be a way to do it, using a look-ahead method to decide when to output the footer.

1. Create three new variables

SalesLine2 of type Record with a subtype of Sales Line
ShowFooter of type Boolean
FooterTotal of type Decimal

2. Add this code to the end of the RoundLoop OnPreDataItem() trigger:

SalesLine.SETCURRENTKEY(Type,"No.","Variant Code","Drop Shipment",
                          "Location Code","Document Type","Shipment Date");

This will give you the sorting you require. The key already exists on the Sales Line table.

3. Add this code to the end of the RoundLoop OnAfterGetRecord() trigger:

IF ShowFooter THEN
  FooterTotal := 0;

FooterTotal += SalesLine."Line Amount";

ShowFooter := TRUE;
SalesLine2.COPY(SalesLine);
SalesLine2.GET(SalesLine."Document Type",SalesLine."Document No.",SalesLine."Line No.");
IF SalesLine.Type = SalesLine.Type::Item THEN
  IF SalesLine2.NEXT <> 0 THEN
    ShowFooter := (SalesLine2."No." <> SalesLine."No.");

4. Add a new RoundLoop body section below the exisitng one which outputs line details. This will be the footer section.

Add this code to the OnPreSection() trigger of the new section:

CurrReport.SHOWOUTPUT(ShowFooter);

Add a field in the new section to display the FooterTotal field

This method will give you a sub total for each line type: Item, G/L Account, Resource etc.
If you only wanted sub totals for only Items then you could modify the code in step 3.

This is just a first attempt, so not optimized fully - it does work though and is reasonably low impact.

Hope this helps some

jknauft

Thanks for the suggestions. I'm working on this today and plan to use a combination of the two suggestions. I'll update and let you know what the code looks like so someone in the future can use it. Thanks again.

Jon