Customer Start Balance Report

Dynamics 365 Business Central/NAV Developers Forum
#1

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

#2

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

#3

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.

#4

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

#5

hi sindorela

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

#6

Try to use

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

#7

Hi sachin

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

#8

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;