Summing amounts with results corresponding to end of month-S

kolaboy · 2008-07-17

Hi Experts,

I and tracking arrears in a report. There is a minimum monthly payment amount one has to pay each months for a mortgage. If the person pays less than the minimum amount, the difference becomes an arrears with negative and if pay more the difference becomes an over payment with positive.

i want to accumulate this arrears and over payment column. I want the accumulate results be correspond to the end of the months. There can be more than one payment a month and the arrears should be added and the result be placed against the end/ or last payment date in the month and the other arrears payment for the other payment date in the month be zero.

This is the code that tracks the arrears:

  ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
  ArreasVarTotal:=ArreasVarTotal+ArreasVar;

Can someone help me on this please?
Thanks

kolaboy · 2008-07-17

Hi Experts,
Can someone help me with this problem.
If you need more explanation of this issue you can let me know, i will explain it in detail in such a way you can help me solve this.
Thanks

kolaboy · 2008-07-17

I am trying to sum amounts within a month. say

Date                    Amount          Acumulate Amout 

01/02/03               5000

05/02/03                700

28/02/03                600                            6300

 

04/03/03                  300

07/03/03                   200

31/03/03                  100                           600

The accumulated amount will correspond to the last day of the months as shown on the third column. Maybe this will help to bring out the picture.

Can someone help me with this please.
Thanks

kolaboy · 2008-07-19

I have tried iterating through but my problem is doing the accumulation of the amount in a month and the result place on the same line as the last date in the month or end of month.
Can someone help me please?
Thanks

kriki · 2008-07-20

It is easiest to put the total on each month. This way you can avoid code to search for the last of each month. This is quite complicated confronted to find the first record of each month.

So in the OnAfterGetRecord, you just need to put some code to blank the total of the month:

IF intOldMonth <> DATE2DMY("Posting Date",3) * 10000 + DATE2DMY("Posting Date",2) THEN BEGIN
  // I just started a new month
  intOldMonth := DATE2DMY("Posting Date",3) * 10000 + DATE2DMY("Posting Date",2);
  decAccumulatedAmount := 0;
END;

// Add current total to current month total
decAccumulatedAmount += Amount;

Sandeep_Prajapati · 2008-07-21

Hi kolaboy,

1) Put an Integer DataItem (Property maxIteration = 1) juat above the dataItem (say DataItem - A) for which you r printing the body section.
2) In Integer DataItem put all the controls that were in body of DataItem - A. and remove the controls from DataItem - A
3) In the Integer Body OnpreSection()

   AccumulatedAmt := 0; //Use it as SourceExprsn in Integer body-Accumulated Amt column
   If MonthEnds then Begin
     AccumulatedAmt := ArreasVarTotal;
     MonthEnds      := false;
     InitialiseTotal := true;
   End;

4) In DataItem - A OnAfterGetRecord()

// PrevMonth -- int -- global
// MonthEnds -- boolean -- global
// InitialiseTotal -- boolean -- global


  If InitialiseTotal then Begin
    InitialiseTotal := false;
    ArreasVarTotal := 0;
  End;

  If (PrevMonth <> DATE2DMY("Posting Date",2)) then  Begin
    MonthEnds := true;
    InitialiseTotal := true;
  End;
    
  PrevMonth := DATE2DMY("Posting Date",2);
  ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount; 
  ArreasVarTotal:=ArreasVarTotal+ArreasVar;

I hope it helps .. O:)

kolaboy · 2008-07-21

Kriki, i have tried your code but its not working. It is accumulating for each month. It can accumulate for each month but let the result for a particular month correspond to the last date of a particular months.
Right now how can i make the accumated values for months that are not the last month/ end of month be zero? See below:

Date                    Amount          Acumulate Amout

01/02/03               5000                           5000
 
05/02/03                700                            5700

28/02/03                600                             6300

 

04/03/03                  300                           300

07/03/03                   200                           500

31/03/03                  100                           600

hoe can i have it in the form below:

Date                    Amount          Acumulate Amout

01/02/03               5000                           0.00
 
05/02/03                700                            0.00

28/02/03                600                             6300

 

04/03/03                  300                           0.00

07/03/03                   200                           0.00

31/03/03                  100                           600

can anyone help me with this please?
Thanks

Xypher · 2008-07-21

Come on kolaboy,

No offense but I just get the feeling you're not putting a lot of effort towards the issues you are posting. Flex your brain muscle more! I see far too often you're coming back with easy questions I know you can answer.

As per this situation (to make the math process transparent) create another variable. So you have One variable used for doing the math, and Another acting as the end-of-month display.

You may have to just implement another IF statement; when the last line for that month will be displayed.

(We love to help, but not all of us particularly enjoy knowing we're doing your work for you [-(, or maybe it's just me.)

Sandeep_Prajapati · 2008-07-25

Hi Kolaboy, Just see this sample report for your solution :P

OBJECT Report 80000 Items Report 
{ 
  OBJECT-PROPERTIES 
  { 
    Date=07/25/08; 
    Time=[ 5:26:01 PM]; 
    Modified=Yes; 
    Version List=; 
  } 
  PROPERTIES 
  { 
  } 
  DATAITEMS 
  { 
    { PROPERTIES 
      { 
        DataItemTable=Table27; 
        DataItemTableView=SORTING(No.) 
                          ORDER(Ascending); 
        PrintOnlyIfDetail=Yes; 
        OnAfterGetRecord=BEGIN 
                                TotQuantity := 0; 
                         END; 

      } 
      SECTIONS 
      { 
        { PROPERTIES 
          { 
            SectionType=Header; 
            PrintOnEveryPage=Yes; 
            SectionWidth=18150; 
            SectionHeight=4230; 
          } 
          CONTROLS 
          { 
            { 1102753001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8; 
                                                         FontBold=Yes; 
                                                         CaptionML=ENU=Item } 
            { 1102753002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right; 
                                                         SourceExpr=FORMAT(TODAY,0,4) } 
            { 1102753003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME } 
            { 1102753004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page; 
                                                         SourceExpr=CurrReport.PAGENO } 
            { 1102753005;Label  ;16950;423  ;750  ;423  ;ParentControl=1102753004 } 
            { 1102753006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right; 
                                                         SourceExpr=USERID } 
            { 1102753009;Label  ;150  ;3807 ;1950 ;423  ;ParentControl=1102753008 } 
            { 1102753012;Label  ;3150 ;3807 ;3450 ;423  ;ParentControl=1102753011 } 
            { 1102753013;Label  ;12450;3807 ;3150 ;423  ;FontSize=9; 
                                                         FontBold=Yes; 
                                                         CaptionML=ENU=Month last Day Total } 
            { 1102753015;Label  ;10500;3807 ;1500 ;423  ;CaptionML=ENU=Quantity } 
            { 1102753017;Label  ;8100 ;3807 ;1650 ;423  ;CaptionML=ENU=Posting date } 
          } 
           } 
        { PROPERTIES 
          { 
            SectionType=Body; 
            SectionWidth=18150; 
            SectionHeight=846; 
          } 
          CONTROLS 
          { 
            { 1102753008;TextBox;150  ;423  ;2400 ;423  ;HorzAlign=Left; 
                                                         SourceExpr="No." } 
            { 1102753011;TextBox;3150 ;423  ;4500 ;423  ;HorzAlign=Left; 
                                                         SourceExpr=Description } 
            { 1102753016;Shape  ;0    ;0    ;18150;423  ;BorderWidth=Hairline; 
                                                         ShapeStyle=HorzLine } 
          } 
           } 
      } 
       } 
    { PROPERTIES 
      { 
        DataItemIndent=1; 
        DataItemTable=Table32; 
        DataItemTableView=SORTING(Item No.,Posting Date); 
        PrintOnlyIfDetail=No; 
        OnAfterGetRecord=BEGIN 
                           // write here your all logic  OnAfterGetRecord() Code...........starts 
                           // ... 
                           // ... 
                           // ...for this report.. 
                             TotQuantity := TotQuantity + Quantity; 

                           // write here your all logic OnAfterGetRecord() Code...........Ends 





                           //Determining End of the month........................starts 
                               ILE.RESET; 
                               ILE.COPY("Item Ledger Entry");  //copying current rec with filters 
                               IF ILE.NEXT = 0 THEN            //checking rec's date.......starts 
                                 MonthEnds := TRUE 
                               ELSE BEGIN 
                                 IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) = 
                                     (DATE2DMY(ILE."Posting Date",3) * 1000 + DATE2DMY(ILE."Posting Date",2))) THEN 
                                   MonthEnds := FALSE 
                                 ELSE 
                                   MonthEnds := TRUE;          //checking rec's date.........ends 
                               END; 
                           //Determining End of the month........................Ends 



                         END; 

        DataItemLink=Item No.=FIELD(No.); 
      } 
      SECTIONS 
      { 
        { PROPERTIES 
          { 
            SectionType=Body; 
            SectionWidth=18150; 
            SectionHeight=423; 
            OnPreSection=BEGIN 
                           // write here your all logic -Body -OnPreSection() Code...........starts 
                           // ... 
                           // ... 
                           // ... 
                           // 
                           // write here your all logic -Body -OnPreSection() Code...........Ends 



                           // if month ends then show the total else dont.....start 
                              IF MonthEnds THEN 
                                 ShowQuantity := FORMAT(TotQuantity) 
                              ELSE 
                                 ShowQuantity := ''; 
                              MonthEnds := FALSE; 
                           // if month ends then show the total else dont.....ends 
                         END; 

          } 
          CONTROLS 
          { 
            { 1102753000;TextBox;5100 ;0    ;2100 ;423  ;SourceExpr="Item No." } 
            { 1102753010;TextBox;7650 ;0    ;2400 ;423  ;SourceExpr="Posting Date"; 
                                                         Format=<Month Text> <Closing><Day>, <Year4> } 
            { 1102753014;TextBox;10500;0    ;1500 ;423  ;SourceExpr=Quantity } 
            { 1102753007;TextBox;12900;0    ;1500 ;423  ;FontSize=8; 
                                                         FontBold=Yes; 
                                                         SourceExpr=ShowQuantity } 
          } 
           } 
      } 
       } 
  } 
  REQUESTFORM 
  { 
    PROPERTIES 
    { 
      Width=9020; 
      Height=3410; 
    } 
    CONTROLS 
    { 
    } 
  } 
  CODE 
  { 
    VAR 
      ShowQuantity@1102753000 : Text[5]; 
      ILE@1102753001 : Record 32; 
      MonthEnds@1102753002 : Boolean; 
      TotQuantity@1102753003 : Integer; 

    BEGIN 
    END. 
  } 
}

kolaboy · 2008-07-25

I have used the below code but its not coming out the way i want it.
It there any problem with it?

    CLE.RESET;
    CLE.COPY("Cust. Ledger Entry");  //copying current rec with filters
    IF CLE.NEXT = 0 THEN            //checking next rec's date.......starts
      MonthEnds := TRUE
    ELSE BEGIN
      IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) =
          (DATE2DMY(CLE."Posting Date",3) * 1000 + DATE2DMY(CLE."Posting Date",2))) THEN BEGIN
          ArreasVarTot := 0;
        MonthEnds := FALSE
      END ELSE
      MonthEnds := TRUE;     //checking next rec's date.........ends
      ArreasVarTot:= ArreasVarTot + ArreasVar;

      END;

Thanks

Sandeep_Prajapati · 2008-07-26

kolaboy wrote:

I have used the below code but its not coming out the way i want it.
It there any problem with it?

    CLE.RESET;
    CLE.COPY("Cust. Ledger Entry");  //copying current rec with filters
    IF CLE.NEXT = 0 THEN            //checking next rec's date.......starts
      MonthEnds := TRUE
    ELSE BEGIN
      IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) =
          (DATE2DMY(CLE."Posting Date",3) * 1000 + DATE2DMY(CLE."Posting Date",2))) THEN BEGIN
          ArreasVarTot := 0;
        MonthEnds := FALSE
      END ELSE
      MonthEnds := TRUE;     //checking next rec's date.........ends
      ArreasVarTot:= ArreasVarTot + ArreasVar;

      END;

Thanks

ArreasVarTot := 0; is the problem.
your ArreasVarTot is cumulative sum. whether month ends or not dont make it zero. In the section you need to print this cumuative sum if month ends otherwise not. Use another varible for that, as in the above sample report.

kolaboy · 2008-07-27

Thank you very much Sandeep Prajapati, its working now.

Sandeep_Prajapati · 2008-07-28

you are most welcome O:)

kolaboy · 2008-07-28

one thing left is to have it as shown below:

Date                    Amount          Acumulate Amout

01/02/03               5000                           0.00
 
05/02/03                700                            0.00

28/02/03                600                             6300

 

04/03/03                  300                           0.00

07/03/03                   200                           0.00

31/03/03                  100                           600

right now the monthly totals are adding up as shown below:

Date                    Amount          Acumulate Amout

01/02/03               5000                           0.00
 
05/02/03                700                            0.00

28/02/03                600                             6300

 

04/03/03                  300                           0.00

07/03/03                   200                           0.00

31/03/03                  100                           6900

How can i have it as shown above?
thanks

Xypher · 2008-07-28

No offense but I am pretty sure you can get this done with a lot cleaner/easier way.

As far as your code is concerned... have you tried the following?

CLE.RESET;
CLE.COPY("Cust. Ledger Entry");  //copying current rec with filters
IF CLE.NEXT = 0 THEN            //checking next rec's date.......starts
  MonthEnds := TRUE
ELSE BEGIN
  IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) =
          (DATE2DMY(CLE."Posting Date",3) * 1000 + DATE2DMY(CLE."Posting Date",2))) THEN BEGIN
    IF MonthEnds THEN
      CLEAR(ArreasVarTot);
    MonthEnds := FALSE
  END ELSE
    MonthEnds := TRUE;     //checking next rec's date.........ends
    ArreasVarTot:= ArreasVarTot + ArreasVar;
END;

Sandeep_Prajapati · 2008-07-29

1) Remove monthEnds := false from your code from body Presection().
2) In the onAfterGetRecord()

If monthEnds then
  CLEAR(ArreasVarTot);

monthEnds := false;

CLE.RESET;
CLE.COPY("Cust. Ledger Entry");  //copying current rec with filters
IF CLE.NEXT = 0 THEN            //checking next rec's date.......starts
  MonthEnds := TRUE
ELSE BEGIN
  IF ((DATE2DMY("Posting Date",3) * 1000 + DATE2DMY("Posting Date",2)) =
          (DATE2DMY(CLE."Posting Date",3) * 1000 + DATE2DMY(CLE."Posting Date",2))) THEN BEGIN
    IF MonthEnds THEN     
    MonthEnds := FALSE
  END ELSE
    MonthEnds := TRUE;     //checking next rec's date.........ends
    ArreasVarTot:= ArreasVarTot + ArreasVar;
END;

kolaboy · 2008-07-29

Thanks everyone. Its working now.
I found out the problem.

Howdy, Stranger!

Quick Links

Categories

Summing amounts with results corresponding to end of month-S

Comments