report change needed. need help!!

johnsogj

hi. i built this report over the sales invoice line table and it summarizes a bunch of information based on shipment date (when the user runs the report he selects the shipment date as a filter). I've attached a copy of the output. however, ideally, I'd like to use "Posting Date" from the sales invoice header table as that filter date. is this possible? Can anyone provide me with any suggestions? I've copied the current code below. how would I modify this to basically run off of the sales invoice header's "Posting Date"?


SalesInvLine.RESET;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
TotalLinesDay := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPiecesDay := SalesInvLine.Quantity;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
TotalLines := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPieces := SalesInvLine.Quantity;

SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
IF SalesInvHead.FINDSET THEN
TotalInvoicesDay := SalesInvHead.COUNT;

SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
IF SalesInvHead.FINDSET THEN
TotalInvoices := SalesInvHead.COUNT;

AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

AvgLines := TotalLines/TotalInvoices;
AvgPieces := TotalPieces/TotalInvoices;

DaveT

Hi John,

Yes is can be done but will not be as efficient as you will not be able to use the calcsums inside a loop. A sample of what you need to do

SalesInvHead.SETRANGE("Posting Date", "Posting Date");
IF SalesInvHead.FINDSET THEN
   begin
     repeat
          SalesInvLine.RESET;

           SalesInvLine.SETRANGE("Document No.",SalesInvHead."No.");
           SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
           TotalLinesDay += SalesInvLine.COUNT;
           SalesInvLine.CALCSUMS(Quantity);
           TotalPiecesDay += SalesInvLine.Quantity;

     until SalesInvHead.NEXT = 0;
   end;

One observation - what about credit memos posted to cancel/re-invoice a shipment?

johnsogj

thank you. I'm in the process of trying your suggestion. however, if "Sales Inv Line" is still my main data item, how can I use "Posting Date" (from the sales inv header) as a report filter?

DaveT

Hi,

You will probably have to re-structure the report but on the face of it you only print one section so you can use the invoice header as the dataitem.

johnsogj

so would I have the sales inv header as the main data item and the sales inv line as a nested data item?

DaveT

Hi,

The key question is how many times you need to print the sections. It looks to me as if you print a summary for one day so even an integer dataitem with filter Number = const 1 would do as you are calculating all the information using record variables.