Options

How to sum all Customers arrears by Customer Posting Group

kolaboykolaboy Member Posts: 446
Hi
I am trying to sum customers total balances by Posting Group. Here is a code i wrote but it not working. can someone help me with this.
Code:


IF Customer."Customer Posting Group" = 'BAK' THEN BEGIN
IF Customer."Monthly Install" <> 0 THEN BEGIN
//BAKTotalCustBal := "Detailed Cust. Ledg. Entry"."Amount (LCY)";
BAKCustBal1 := "Cust. Ledger Entry". Amount;
BAKArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
END;
END;
BAKArreasVarTotal:=ArreasVarTotal+BAKArreasVar;
BAKTotCustBal1 += BAKCustBal1;
END;


I need it like this
BAK
20000 i.e the total arrears for all the customers with Posting group of BAK
BRDAL
48090
BTU......................32090 ans so on

I to create a report the display the information as above.
Can somone help me Please.
Thanks

Comments

  • Options
    SavatageSavatage Member Posts: 7,142
  • Options
    kolaboykolaboy Member Posts: 446
    I am using the Customer table and cust. ledger entry tables as dataitems. I also need to show grand totals of the arrears. Your sample report is good beacuse it is capturing the balances but how about the arrears. I wanted the output to be in the format i suggested above.There is a minimum amount of money called monthly install that is paid by each customer every month. This is repayment. the report is for mortgage customers who pay monthly install every month.This monthly install varies depending on the agreement, so the Amount is different for each customer. So the report is to track balances and arrears for each customer.

    How can i do this?
    Thanks
  • Options
    kolaboykolaboy Member Posts: 446
    I am still having problems on this post. Can someone help me please.
    Savatage thanks for the sample report. It lacks the arrear bit of it and the layout is not according to what i suggested above. can you modify it to suit my description please.
    Thanks
  • Options
    SavatageSavatage Member Posts: 7,142
    this report is custom to you I have nothing like that in my system (arrears).
    if you want to change the look of the report the view the sections and tighten it up.

    Look a the code behind the sections and the dataitems to see how the grouping is taking place.

    That's the best that I can do since I'm unfamiliar with tables and output needed.
  • Options
    kolaboykolaboy Member Posts: 446
    Hi
    Can someone help me with this problem please.
    I tried to modify savatage's sample report bit i can't get it like the way i suggested.
    I want the balances and arrears to be displayed in one report for all the customer posting groups. Can someone help me with this.
    For the arrears there is a field created for monthly installment and every months customers has to pay the monthly installment amount. If anyone defaults the amount becomes an arrears and if the customer pays less than the monthly installment, the difference becomes an arrears.
    can some help me please!!!!!!!!!!!!!!!
    Thanks
  • Options
    kolaboykolaboy Member Posts: 446
    Can someone help me here please?
    Thanks
  • Options
    DenSterDenSter Member Posts: 8,304
    I think many people here would love to help you, but don't understand your problem.

    Let me first ask: Do you know how to keep track of totals, and how to group, and how to display them in a report? So if I would ask you to create a simple report from the customer ledger, grouping the records by customer number, and totalling the Amount field for each customer, would you know how to do that?
  • Options
    kolaboykolaboy Member Posts: 446
    Yes,i will be able to do that.
    My problem is how to group by a variable called ArreasVar. This is not a field in the database, i defined it as a global variable that stores arrearsVar from:
    ArreasVar:= -Customer."Monthly Install" - "Cust. Ledger Entry".Amount;
    
    The other issue is how to get the total ArrearsVar and total balances by Posting Group. For example

    Customer Posting Group Total Balance Total Arrears
    TTTTT 490876 40876
    BNNT 89765 21344

    NB: TTTT is the posting group for customer in TTTT and it's Total Balance = 490876, Total Arrears= 40876. The same thing for BNNT.

    It this explanation helpful?
    Can someone help me on this Please
    Thanks
  • Options
    DenSterDenSter Member Posts: 8,304
    As far as I know you can only group by a field that is in the table, and that is also part of a key. You can keep totals for variables, but for grouping it must be part of the table.
  • Options
    AlbertvhAlbertvh Member Posts: 516
    If you have your GroupTotalFields with Posting Group you could in the OnPreDataItem use

    CurrReport.CREATETOTALS(MyVar);

    MyVar must be a Decimal variable.

    Also remeber that you mast do a "Cust. Ledger Entry".CALCFIELDS(Amount) before doing your calculation


    Albert
  • Options
    kolaboykolaboy Member Posts: 446
    Oh, Ok SO one cannot be to group by a variable. So what is the alternative to
    to group by a variable, because i need to group by the variable
    Albert, Is the GroupTotalFields with Posting Group on the Cust.Ledger Entry Dataitem property.
    Also remeber that you mast do a "Cust. Ledger Entry".CALCFIELDS(Amount) before doing your calculation
    I quess Amount is the flowfied for the balance, How about the variable ArrearsVar? can "Cust. Ledger Entry".CALCFIELDS(ArrearsVar) be possible?
    Thanks
    [/code]
  • Options
    SavatageSavatage Member Posts: 7,142
    kolaboy wrote:
    Oh, Ok SO one cannot be to group by a variable. So what is the alternative to to group by a variable, because i need to group by the variable

    As Daniel says it needs to be a field in the table you can make it a calculating field on the table & make it a key.

    or

    Other report writers such as Crystal Reports, Jet Reports, etc lets you filter & group by anything.
  • Options
    kolaboykolaboy Member Posts: 446
    you can make it a calculating field on the table & make it a key.
    How can i do the above? Can you explain in step form for me. I think this will be the best alternative for me.
    Thanks
Sign In or Register to comment.