How to calculate accumulated balance on report

victoria

Hi all,

I've created a simple report based on table 17 - G/L Entry. I added "Debit Amount" and "Credit Amount" fields and now want to create a field called "Balance" (or "Accumulated Balance") which will be calculating accum. balance for the lines.

On G/L Entry - OnAfterGetRecord() I added the following code:

AccBalance := AccBalance + Amount;

AccBalance is now appearing under "Balance" heading in my report (see picture).

When I run a report for a few G/L Accounts, I can see that my accum. balance for the second account is calculating accum. balance incorrectly - it takes an accumulated balance from the 1st account and adds up debit/credit amounts, which it shouldn't do, it should reset the value somehow.

Could you suggest how I can modify the code to resolve this problem?

Many thanks.

matttrax

Well, every time the G/L Account number changes you need to reset your value to zero, which you've already stated. Think about what you need to get that. I'll give you two options and let you go from there.

1) You can have a G/L Account data item in your report
2) You can have a variable that keeps track of the last G/L Account that was displayed

So, knowing that, what should come next?

victoria

Hi mattrax,

I've added G/L Account data item in my report, if you could suggest what is the next step?
Sorry I am not a developer (I am a NAV consultant) so my questions could sound odd to you, just have started learning the reports.

Many thanks for your help.

Victoria

matttrax

Which report trigger fires when you get a new record from the database? Your code should go in a similar place as the code you are using to sum up the amount. But instead of summing you want to reset it to zero.

David_Singleton

Why write code? Just use the wizard to create a report with group totals.

victoria

David,

I used a wizard to create this report, which is fine. Group totals are ok, the issue is with the calculation of accumulated balance for each line (the similar way as on a standard Customer Statement report, where Balance column has accumulated balances).

victoria

matttrax wrote:

Which report trigger fires when you get a new record from the database? Your code should go in a similar place as the code you are using to sum up the amount. But instead of summing you want to reset it to zero.

G/L Entry data item. I calculate accum. balances in G/L Entry - OnAfterGetRecord() section. Do I need to use RESET function or just use something similar to AccBalance := 0; ?

Maria-S

Hello Victoria,
I suggest to press F9 on the section that shows your "Total for GL Account" and add code on the OnPostSection trigger: AccBalance := 0;

Albertvh

Hi Victoria,

in the OnPreDataitem add code there
CurrReport.Createtotals(Accum);

Albert

victoria

Maria-S wrote:

Hello Victoria,
I suggest to press F9 on the section that shows your "Total for GL Account" and add code on the OnPostSection trigger: AccBalance := 0;

Hi Maria,

I did as you suggested and it resolved the issue, thank you very much! Now my report displays accum. balances correctly.

Thanks!