Collection Period for all customers

qed · 2007-05-15

The entry statistics form shows you the average collection period for 1 customer. I would like to see the avg. collection period for all my customers in a given period.
I'm not a Navision wizard, but using the Designer I could see that there's a C/AL Symbol or something like that that's called avgdaystopay.
I think there's the key to a sollution, but I have absolutely no idea how to use that symbol/variable.

Anyone, please?

thnx a bunch.

ara3n · 2007-05-15

Hello Here is a report that prints for all customers.

OBJECT Report 50200 Avg Collection Period (days)
{
  OBJECT-PROPERTIES
  {
    Date=05/15/07;
    Time=[ 1:11:16 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table18;
        OnAfterGetRecord=BEGIN
                           CLEARALL;
                           FOR j := 1 TO 6 DO BEGIN
                             CustLedgEntry[j].SETCURRENTKEY("Document Type","Customer No.","Posting Date");
                             CustLedgEntry[j].SETRANGE("Document Type",j); // Payment,Invoice,Credit Memo,Finance Charge Memo,Reminder,Refund
                             CustLedgEntry[j].SETRANGE("Customer No.","No.");
                             IF CustLedgEntry[j].FIND('+') THEN
                               CustLedgEntry[j].CALCFIELDS(Amount,"Remaining Amount");
                           END;

                           CustLedgEntry2.SETCURRENTKEY("Customer No.",Open);
                           CustLedgEntry2.SETRANGE("Customer No.","No.");
                           CustLedgEntry2.SETRANGE(Open,TRUE);
                           IF CustLedgEntry2.FIND('+') THEN
                             REPEAT
                               j := CustLedgEntry2."Document Type";
                               IF j > 0 THEN BEGIN
                                 CustLedgEntry2.CALCFIELDS("Remaining Amt. (LCY)");
                                 TotalRemainAmountLCY[j] := TotalRemainAmountLCY[j] + CustLedgEntry2."Remaining Amt. (LCY)";
                               END;
                             UNTIL CustLedgEntry2.NEXT(-1) = 0;

                           DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],CustDateName[1],WORKDATE,0);
                           DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],CustDateName[2],WORKDATE,0);
                           DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[3],CustDateName[3],WORKDATE,-1);

                           FOR i := 1 TO 3 DO BEGIN
                             CustLedgEntry2.RESET;
                             CustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
                             CustLedgEntry2.SETRANGE("Customer No.","No.");

                             CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]);
                             CustLedgEntry2.SETRANGE("Posting Date",0D,CustLedgEntry2.GETRANGEMAX("Posting Date"));
                             DtldCustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date");
                             CustLedgEntry2.COPYFILTER("Customer No.",DtldCustLedgEntry2."Customer No.");
                             CustLedgEntry2.COPYFILTER("Posting Date",DtldCustLedgEntry2."Posting Date");
                             DtldCustLedgEntry2.CALCSUMS("Amount (LCY)");
                             CustBalanceLCY := DtldCustLedgEntry2."Amount (LCY)";
                             HighestBalanceLCY[i] := CustBalanceLCY;
                             DaysToPay := 0;
                             NoOfInv := 0;

                             CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]);
                             IF CustLedgEntry2.FIND('+') THEN
                               REPEAT
                                 j := CustLedgEntry2."Document Type";
                                 IF j > 0 THEN
                                   NoOfDoc[i][j] := NoOfDoc[i][j] + 1;

                                 CustLedgEntry2.CALCFIELDS("Amount (LCY)");
                                 CustBalanceLCY := CustBalanceLCY - CustLedgEntry2."Amount (LCY)";
                                 IF CustBalanceLCY > HighestBalanceLCY[i] THEN
                                   HighestBalanceLCY[i] := CustBalanceLCY;

                                 // Optimized Approximation
                                 IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
                                    NOT CustLedgEntry2.Open
                                 THEN
                                   IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
                                     UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date")
                                   ELSE
                                     IF CustLedgEntry2."Closed by Entry No." <> 0 THEN BEGIN
                                       IF CustLedgEntry3.GET(CustLedgEntry2."Closed by Entry No.") THEN
                                         UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date");
                                     END ELSE BEGIN
                                       CustLedgEntry3.SETCURRENTKEY("Closed by Entry No.");
                                       CustLedgEntry3.SETRANGE("Closed by Entry No.",CustLedgEntry2."Entry No.");
                                       IF CustLedgEntry3.FIND('+') THEN
                                         UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date");
                                     END;
                               UNTIL CustLedgEntry2.NEXT(-1) = 0;
                             IF NoOfInv <> 0 THEN
                               AvgDaysToPay[i] := DaysToPay / NoOfInv;
                           END;
                         END;

        ReqFilterFields=No.;
        GroupTotalFields=No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18429;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Customer;
                                                                    ESM=Cliente;
                                                                    FRC=Client;
                                                                    ENC=Customer] }
            { 1000000002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;16950;423  ;750  ;423  ;ParentControl=1000000004 }
            { 1000000006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000013;Label  ;1249 ;1252 ;3999 ;440  ;CaptionML=[ENU=Avg. Collection Period (Days);
                                                                    ESM=Periodo de pago (d¡as);
                                                                    FRC=P‚riode de recouvrement moyen (Jours);
                                                                    ENC=Avg. Collection Period (Days)] }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18429;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1500 ;846  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;1650 ;0    ;4500 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000014;TextBox;6286 ;0    ;2750 ;440  ;Focusable=No;
                                                         HorzAlign=Right;
                                                         Border=No;
                                                         SourceExpr=CustDateName[1] }
            { 1000000015;Label  ;9256 ;0    ;2750 ;440  ;HorzAlign=Right;
                                                         LeaderDots=No;
                                                         CaptionML=[ENU=This Year;
                                                                    ESM=A¤o actual;
                                                                    FRC=Cette ann‚e;
                                                                    ENC=This Year] }
            { 1000000016;Label  ;12226;0    ;2750 ;440  ;HorzAlign=Right;
                                                         LeaderDots=No;
                                                         CaptionML=[ENU=Last Year;
                                                                    ESM=A¤o anterior;
                                                                    FRC=Ann‚e pr‚c‚dente;
                                                                    ENC=Last Year] }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18429;
            SectionHeight=440;
          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000011;TextBox;1650 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Name }
            { 1000000000;TextBox;6310 ;0    ;2750 ;440  ;CaptionML=[ENU=Avg. Collection Period (Days);
                                                                    ESM=Periodo de pago (d¡as);
                                                                    FRC=P‚riode de recouvrement moyen (Jours);
                                                                    ENC=Avg. Collection Period (Days)];
                                                         DecimalPlaces=0:0;
                                                         SourceExpr=AvgDaysToPay[1] }
            { 1000000007;TextBox;9280 ;0    ;2750 ;440  ;CaptionML=[ENU=Avg. Collection Period (Days);
                                                                    ESM=Periodo de pago (d¡as);
                                                                    FRC=P‚riode de recouvrement moyen (Jours);
                                                                    ENC=Avg. Collection Period (Days)];
                                                         DecimalPlaces=0:0;
                                                         SourceExpr=AvgDaysToPay[2] }
            { 1000000010;TextBox;12250;0    ;2750 ;440  ;CaptionML=[ENU=Avg. Collection Period (Days);
                                                                    ESM=Periodo de pago (d¡as);
                                                                    FRC=P‚riode de recouvrement moyen (Jours);
                                                                    ENC=Avg. Collection Period (Days)];
                                                         DecimalPlaces=0:0;
                                                         SourceExpr=AvgDaysToPay[3] }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      CustLedgEntry2@1000000015 : Record 21;
      CustLedgEntry3@1000000014 : Record 21;
      DtldCustLedgEntry2@1000000013 : Record 379;
      DateFilterCalc@1000000012 : Codeunit 358;
      CustLedgEntry@1000000011 : ARRAY [6] OF Record 21;
      CustDateFilter@1000000010 : ARRAY [3] OF Text[30];
      CustDateName@1000000009 : ARRAY [3] OF Text[30];
      TotalRemainAmountLCY@1000000008 : ARRAY [6] OF Decimal;
      NoOfDoc@1000000007 : ARRAY [3,6] OF Integer;
      AvgDaysToPay@1000000006 : ARRAY [3] OF Decimal;
      DaysToPay@1000000005 : Decimal;
      NoOfInv@1000000004 : Integer;
      HighestBalanceLCY@1000000003 : ARRAY [3] OF Decimal;
      CustBalanceLCY@1000000002 : Decimal;
      i@1000000001 : Integer;
      j@1000000000 : Integer;

    LOCAL PROCEDURE UpdateDaysToPay@1(NoOfDays@1000 : Integer);
    BEGIN
      DaysToPay := DaysToPay + NoOfDays;
      NoOfInv := NoOfInv + 1;
    END;

    BEGIN
    END.
  }
}

Collection Period for all customers

Comments

Categories