How to sum all Customers arrears by Customer Posting Group

kolaboy

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

Savatage

not sure but perhaps this can put on the right track
http://savatage99.googlepages.com/50038 ... yGroup.txt

kolaboy

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

kolaboy

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

Savatage

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.

kolaboy

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

kolaboy

Can someone help me here please?
Thanks

DenSter

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?

kolaboy

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

DenSter

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.

Albertvh

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

kolaboy

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]

Savatage

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.

kolaboy

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