Customer Start Balance Report

Hello,

I am trying to create a report to calculate the start balance for each customer(SUM of Amount till the date I filter). How can I do that?

Please help me…

Best,

Sindorela

Refer the Report # 10072 - Customer Statement in NA Version. You can reuse that report if you it matches your requirement.

I don’t have in my database the report with that ID( I am using NAV 2015 Developer Environment). Actually I did the code below:

Customer - OnAfterGetRecord()

CustLedgEntry.RESET;
CustLedgEntry.SETCURRENTKEY(“Customer No.”,“Posting Date”);
CustLedgEntry.SETRANGE(“Customer No.”,“No.”);
COPYFILTER(“Date Filter”,CustLedgEntry.“Posting Date”);
IF CustLedgEntry.FIND(’-’) THEN BEGIN
REPEAT
CustLedgEntry.CALCFIELDS(Amount);
StartBalance:= StartBalance + CustLedgEntry.Amount;
UNTIL CustLedgEntry.NEXT = 0;
CustLedgEntry.RESET;
END;

But it didn’t work.

Report # 116 - Statement. It’s available in World Wide Version. Let me know which country version of db you are using.

hi sindorela

you mean to say opening balance of each customer before the date filter

Try to use

CustLedgEntry.SETFILTER(“Date Filter”,’…%1’,InputDate);
CustLedgEntry.CALCFIELDS(Amount);
StartBalance := CustLedgEntry.Amount;

Hi sachin

Yes, Opening Balance. So I need the Opening Balance for each Customer.

hi sindorela use following code
CustLedgEntry.RESET;
CustLedgEntry.SETCURRENTKEY(“Customer No.”,“Posting Date”);
CustLedgEntry.SETRANGE(“Customer No.”,“No.”);
CustLedgEntry.SETFILTER(CustLedgEntry.“posting date”,’…%1’,startdate);
if CustLedgEntry.find(’-’) then begin
Repeat
CustLedgEntry.CALCFIELDS(Amount);
StartBalance:= StartBalance + CustLedgEntry.Amount;

UNTIL CustLedgEntry.NEXT = 0;
END;

or find my below customization where i have given option on page customer ledger entry to show opening and closing balance of customer

TotAmt:=0;
OpeningBal:=0;
StartDate:=010101D;
EndDate:=CALCDATE(’-1D’,010110D);
“Cust.Ledg.Entry”.RESET;
“Cust.Ledg.Entry”.SETCURRENTKEY(“Cust.Ledg.Entry”.“Customer No.”,“Cust.Ledg.Entry”.“Posting Date”);
“Cust.Ledg.Entry”.SETRANGE(“Cust.Ledg.Entry”.“Customer No.”,“Customer No.”);
“Cust.Ledg.Entry”.SETFILTER(“Cust.Ledg.Entry”.“Posting Date”,’%1…%2’,StartDate,EndDate);
IF “Cust.Ledg.Entry”.FINDFIRST THEN BEGIN
REPEAT
“Cust.Ledg.Entry”.CALCFIELDS(“Cust.Ledg.Entry”.“Amount (LCY)”);
TotAmt :=TotAmt+“Cust.Ledg.Entry”.“Amount (LCY)”;
OpeningBal:=TotAmt;
UNTIL “Cust.Ledg.Entry”.NEXT=0;
END;

EndDate1:=CALCDATE(’-1D’,010121D);
“Cust.Ledg.Entry1”.RESET;
“Cust.Ledg.Entry1”.SETCURRENTKEY(“Cust.Ledg.Entry1”.“Customer No.”,“Cust.Ledg.Entry1”.“Posting Date”);
“Cust.Ledg.Entry1”.SETRANGE(“Cust.Ledg.Entry1”.“Customer No.”,“Customer No.”);
“Cust.Ledg.Entry1”.SETFILTER(“Cust.Ledg.Entry1”.“Posting Date”,’%1…%2’,010110D,EndDate1);
IF “Cust.Ledg.Entry1”.FINDFIRST THEN BEGIN
REPEAT
“Cust.Ledg.Entry1”.CALCFIELDS(“Cust.Ledg.Entry1”.“Amount (LCY)”);
“Cust.Ledg.Entry1”.“Opening Balance”:=OpeningBal;
“Cust.Ledg.Entry1”.“Closing Balance”:=“Cust.Ledg.Entry1”.“Opening Balance”+“Cust.Ledg.Entry1”.“Amount (LCY)”;
“Cust.Ledg.Entry1”.MODIFY;
OpeningBal:=“Cust.Ledg.Entry1”.“Closing Balance”;
UNTIL “Cust.Ledg.Entry1”.NEXT=0;
END;