Separating Finance Change from payment in report

kolaboy · 2007-11-21

Hi Experts,
I have created a report from the Cust. Ledger Entry table and i want to separately display the payments and the finance Charges on the report. i am able to display only one of these. Here is the report:

OBJECT Report 50000 Mortgate repmt
{
OBJECT-PROPERTIES
{
Date=21/11/07;
Time=14:25:53;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table21;
DataItemTableView=SORTING(Document Type,Customer No.,Posting Date,Currency Code);
OnPreDataItem=BEGIN
LastFieldNo := FIELDNO("Document Type");
END;

OnAfterGetRecord=BEGIN
TotAmt += Amount;
TotRemAmt += "Remaining Amount";
TotFinchPaid := TotAmt - TotRemAmt;
END;

ReqFilterFields=Document Type,Customer No.;
TotalFields=Amount,Remaining Amount,Original Amount,Amount to Apply;
GroupTotalFields=Document Type;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
}
CONTROLS
{
{ 1000000001;Label ;0 ;0 ;7500 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=ENU=Cust. Ledger Entry }
{ 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 }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1269;
}
CONTROLS
{
{ 1000000012;Label ;0 ;0 ;1800 ;846 ;ParentControl=1000000011;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000015;Label ;1800 ;0 ;3600 ;846 ;ParentControl=1000000014;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000018;Label ;5400 ;0 ;1800 ;846 ;ParentControl=1000000017;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000021;Label ;7650 ;0 ;1800 ;846 ;ParentControl=1000000020;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000024;Label ;9900 ;0 ;1800 ;846 ;ParentControl=1000000023;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
{ 1000000027;Label ;12150;0 ;1800 ;846 ;ParentControl=1000000026;
VertAlign=Bottom;
FontSize=8;
FontBold=Yes;
MultiLine=Yes }
}
}
{ PROPERTIES
{
SectionType=GroupHeader;
SectionWidth=18150;
SectionHeight=0;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT := FooterPrinted;
FooterPrinted := FALSE;
END;

}
CONTROLS
{
}
}
{ PROPERTIES
{
SectionType=GroupHeader;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Document Type");
END;

}
CONTROLS
{
{ 1000000008;TextBox;3000 ;0 ;4500 ;423 ;HorzAlign=Left;
SourceExpr="Document Type" }
{ 1000000009;Label ;0 ;0 ;3000 ;423 ;ParentControl=1000000008 }
}
}
{ PROPERTIES
{
SectionType=GroupHeader;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = LastFieldNo;
END;

}
CONTROLS
{
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=18150;
SectionHeight=423;
}
CONTROLS
{
{ 1000000011;TextBox;0 ;0 ;1500 ;423 ;HorzAlign=Left;
SourceExpr="Customer No." }
{ 1000000014;TextBox;1650 ;0 ;3600 ;423 ;HorzAlign=Left;
SourceExpr="Document Type" }
{ 1000000017;TextBox;5550 ;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr=Amount }
{ 1000000020;TextBox;7950 ;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Remaining Amount" }
{ 1000000023;TextBox;10200;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Original Amount" }
{ 1000000026;TextBox;12300;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Amount to Apply" }
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
IF NOT FooterPrinted THEN
LastFieldNo := CurrReport.TOTALSCAUSEDBY;
CurrReport.SHOWOUTPUT := NOT FooterPrinted;
FooterPrinted := TRUE;
END;

}
CONTROLS
{
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=18150;
SectionHeight=1269;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT :=
CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Document Type");
END;

}
CONTROLS
{
{ 1000000028;TextBox;0 ;0 ;5700 ;423 ;SourceExpr=TotalFor + FIELDCAPTION("Document Type") }
{ 1000000029;TextBox;5550 ;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr=Amount }
{ 1000000030;TextBox;7950 ;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Remaining Amount" }
{ 1000000031;TextBox;10200;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Original Amount" }
{ 1000000032;TextBox;12300;0 ;1800 ;423 ;HorzAlign=Right;
SourceExpr="Amount to Apply" }
{ 1000000000;Label ;0 ;423 ;4500 ;423 ;FontSize=9;
FontBold=Yes;
CaptionML=ENU=Total Finance Charge Paid }
{ 1000000007;TextBox;5700 ;423 ;2400 ;423 ;HorzAlign=Center;
FontSize=8;
FontBold=Yes;
SourceExpr=TotFinchPaid }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
LastFieldNo@1000000000 : Integer;
FooterPrinted@1000000001 : Boolean;
TotalFor@1000000002 : TextConst 'ENU="Total for "';
TotFinchPaid@1000000003 : Decimal;
TotRemAmt@1000000004 : Decimal;
TotAmt@1000000005 : Decimal;

BEGIN
END.
}
}

can anyone help me with this issue Please.
Thanks

Savatage · 2007-11-21

Seperate how? like this?

Finance Charge
Finance Charge
Finance Charge
<total>
Payment
Payment
Payment
<Total>

How are you setting your filter on the report?
Document Type:Payment|Finance Charge (your using the bar "|" right?)

?

kolaboy · 2007-11-21

I wanted them in Column form ie:

Customer No. Fincharge Payment

AsA123 3990 4567
bt232 5431 5090

Any ideas Please
Thanks

Savatage · 2007-11-21

How I would total the two number seperately is to create two variables.

Each assigned to total up the amount when the document type matches and then add them to the CLE footer based on cust no.

(if I'm getting you right???)

OnPreDataItem()

CurrReport.CREATETOTALS(vFinanceCharge,vPayment);

OnAfterGetRecord()
//Create A total for payments

CASE "Document Type" OF
  "Document Type"::Payment:
     vPayment := Amount;
  ELSE 
     vPayment := 0;
END;

//Create A total for Finance Charges

CASE "Document Type" OF
  "Document Type"::"Finace Charge":
     vFinanceCharge := Amount;
  ELSE 
     vFinanceCharge := 0;
END;

Add two text boxes to your report (Cust No Group footer)
with vPayment & vFinanceCharge as their SourceExp
Both Variables are type Decimal.

I hope this is what you mean

Savatage · 2007-11-21

Additional Note if you are using the sourcetableview and setting the filter
Document Type=Finance Charge|Payment (so you only get those two types)

you could

CASE "Document Type" OF
  "Document Type"::"Finance Charge":
     vFinanceCharge := Amount;
  ELSE 
     vPayment := Amount;
END;

*Amount is just an example - you can use whatever field you need

kolaboy · 2007-11-21

Thanks Harry, its almost what i want. I just need to take the difference of vPayment and vFinanceCharge and show the result in another column;
Here is the report as at now:

OBJECT Report 50000 Mortgate repmt
{
  OBJECT-PROPERTIES
  {
    Date=21/11/07;
    Time=21:47:26;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table21;
        DataItemTableView=SORTING(Document Type,Customer No.,Posting Date,Currency Code)
                          ORDER(Ascending)
                          WHERE(Document Type=FILTER(Payment|Finance Charge Memo));
        OnPreDataItem=BEGIN
                        LastFieldNo := FIELDNO("Document Type");
                        LastFieldNo := FIELDNO("Customer No.");
                        CurrReport.CREATETOTALS(vFinanceCharge,vPayment);
                        PrinPaid :=0;
                      END;

        OnAfterGetRecord=BEGIN
                           TotAmt += Amount;
                           TotRemAmt += "Remaining Amount";
                           TotFinchPaid := TotAmt - TotRemAmt;
                           //PrinPaid :=0;
                           //Create A total for payments
                           CASE "Document Type" OF
                             "Document Type"::Payment:
                                vPayment := Amount;
                             ELSE
                                vPayment := 0;
                           END;

                           //Create A total for Finance Charges
                           CASE "Document Type" OF
                             "Document Type"::"Finance Charge Memo":
                                vFinanceCharge := Amount;
                             ELSE
                                vFinanceCharge := 0;
                           END;

                           PrinPaid := vPayment - vFinanceCharge;
                         END;

        ReqFilterFields=Document Type,Customer No.;
        TotalFields=Amount,Remaining Amount,Original Amount,Amount to Apply;
        GroupTotalFields=Document Type;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=19500;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Cust. Ledger Entry }
            { 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 }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=19500;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000012;Label  ;0    ;0    ;1800 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;1800 ;0    ;3600 ;846  ;ParentControl=1000000014;
                                                         VertAlign=Bottom;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000018;Label  ;5400 ;0    ;1800 ;846  ;ParentControl=1000000017;
                                                         VertAlign=Bottom;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000021;Label  ;7650 ;0    ;1800 ;846  ;ParentControl=1000000020;
                                                         VertAlign=Bottom;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000024;Label  ;9900 ;0    ;1800 ;846  ;ParentControl=1000000023;
                                                         VertAlign=Bottom;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000007;Label  ;12000;0    ;3300 ;846  ;HorzAlign=General;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Total Finance Charge }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=19500;
            SectionHeight=0;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT := FooterPrinted;
                           FooterPrinted := FALSE;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=19500;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Document Type");
                         END;

          }
          CONTROLS
          {
            { 1000000008;TextBox;3000 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document Type" }
            { 1000000009;Label  ;0    ;0    ;3000 ;423  ;ParentControl=1000000008 }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=19500;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = LastFieldNo;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=19500;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000011;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Customer No." }
            { 1000000014;TextBox;1650 ;0    ;3600 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document Type" }
            { 1000000017;TextBox;5550 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Amount }
            { 1000000020;TextBox;7950 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Remaining Amount" }
            { 1000000023;TextBox;10200;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Original Amount" }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=19500;
            SectionHeight=423;
            OnPreSection=BEGIN
                           IF NOT FooterPrinted THEN
                             LastFieldNo := CurrReport.TOTALSCAUSEDBY;
                           CurrReport.SHOWOUTPUT := NOT FooterPrinted;
                           FooterPrinted := TRUE;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=19500;
            SectionHeight=2115;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Document Type");
                         END;

          }
          CONTROLS
          {
            { 1000000028;TextBox;0    ;0    ;5700 ;423  ;SourceExpr=TotalFor + FIELDCAPTION("Document Type") }
            { 1000000029;TextBox;5700 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Amount }
            { 1000000030;TextBox;7950 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Remaining Amount" }
            { 1000000031;TextBox;10200;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr="Original Amount" }
            { 1000000000;Label  ;0    ;423  ;4500 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Total Payment }
            { 1000000010;TextBox;5700 ;423  ;1650 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=vPayment }
            { 1000000013;TextBox;12600;423  ;2250 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=vFinanceCharge }
            { 1000000016;TextBox;16050;423  ;1500 ;423  ;SourceExpr=PrinPaid }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1000000000 : Integer;
      FooterPrinted@1000000001 : Boolean;
      TotalFor@1000000002 : TextConst 'ENU="Total for "';
      TotFinchPaid@1000000003 : Decimal;
      TotRemAmt@1000000004 : Decimal;
      TotAmt@1000000005 : Decimal;
      vFinanceCharge@1000000006 : Decimal;
      vPayment@1000000007 : Decimal;
      PrinPaid@1000000008 : Decimal;

    BEGIN
    END.
  }
}

Any idea on the Difference?
Thanks
[/code]

Savatage · 2007-11-21

Ok are the two fields Working for you??

I still see your code here, is TotAmt being used somewhere?

BEGIN
TotAmt += Amount;

do you want to change the case statement to use TotAmt instead of Amount?

Anyway if Finance Change and Payment are different signs (+ & -)
you can just total the "Amount" filed to get the difference of the two.

If they are the same you could change one by doing the vairable thing again

CASE "Document Type" OF 
  "Document Type"::"Finance Charge": 
     vDifference := Amount; 
  ELSE 
     vDifference := Amount * -1; 
END;

VDifference Type Decimal - don't forget to add it to your CREATETOTALS line. This is only necessary if the signs are the same.

You don't have to paste the entire report again - LOL
I'm not importing it - just throwing ideas your way. I'm sure there are cleaner ways of writing this code but I was just trying to make it visibly understandable too

kolaboy · 2007-11-21

Harry, what are you subtracting in vDifference. Are you subtracting the vFinanceCharge and vPayment. ie Is
vDifference := vPayment - vFinanceCharge . This is actually what i what but its not working with the Case statement of the vDifference.
Thanks

Savatage · 2007-11-21

I'm not subtracting anything. If you read the post above all you need is to have the Amount field have different signs (positive & negative) for Finance Charge & payment and then total the field by customer!

I don't use Finance Charges so I'm not sure if they are different signs to begin with, so I provided a solution in case they are.

You already are filtering all the entries so just Finance Charges & Payment appear. So a totaling of the Amoutn will give you a difference.

DataItemTableView=SORTING(Document Type,Customer No.,Posting Date,Currency Code) ORDER(Ascending)WHERE(Document Type=FILTER(Payment|Finance Charge

Type Payment = Amount -1000
Type Finance Charge = Amount +10
when you total the AMOUNT field you will get the difference automatically
-900

Hope that makes sence

kolaboy · 2007-11-21

Thanks Harry, the difference is coming now. One more thing is how to Summaries this by only displaying the the Financ Charge totals and the payment total for in a list form for each customer. ie

Cust no. Finance charge total Payment Total

cust1 1000 4555
cust2 9000 2000
cust3 4000 3220
ETC..
In a column form.
Thanks

Savatage · 2007-11-21

One of the fields in the Cust Legder Entries is Cust No. You are going to have to create a Group Total based on Customer No.

An easy way to do this is use the Wizard (New Report) add the fields you need When it asks you what fields to total you use Amount when it asks how you wan to sort and/or group you choose cust no.

save the report as is - reopen it and add the codes as stated above.

Createtotals
Dataitemtableview
& Case statements.

Now view the sections and put 3 text boxes on the last line changing the source exp to what is stated above. Close up any other body sections the wizard might create. You can remake this report of your in about 3 minutes. Unfortunately I'll be gone till monday so I hope this is enuf to get you started in the right direction.

Separating Finance Change from payment in report

Comments

Categories