how to get balance according to credit and debit in the bank statement report

(0) Share

Report

Posted on by M_R

123

Hello, here i am going to add balance according to the credit(-ve), debit(+ve) and current balance in bankAccountstatement report

for the first row balance should be calculated as current balance - credit or current balance + debit .

but from the second row balance of the previous row is considered as Prev balance, now the calculation will be PrevBalance +debit or PrevBalance - credit

in the code i have declared PrevBalance is

real PrevBalance;

the code is..........

while select forupdate bankAccountStatementTmp
{
select firstonly ledgerJournalTrans
where ledgerJournalTrans.RecId == bankAccountStatementTmp.SourceRecId;

bankAccountStatementTmp.Txt = ledgerJournalTrans.Txt;

bankAccountStatementTmp.XTSDebit = ledgerJournalTrans.AmountCurDebit;
bankAccountStatementTmp.XTSCredit = ledgerJournalTrans.AmountCurCredit;

    ttsbegin;

if(bankaccountstatementtmp.XTSCredit)
{
   bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance - bankAccountStatementTmp.XTSCredit;

}
else if(bankAccountStatementTmp.XTSDebit)
{
    bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance + bankAccountStatementTmp.XTSDebit;

}
else
{
bankAccountStatementTmp.XTSCorrectedAmountCur = PrevBalance - bankAccountStatementTmp.XTSDebit + bankAccountStatementTmp.XTSDebit;
}

PrevBalance = bankAccountStatementTmp.CurrentBalance - bankAccountStatementTmp.XTSCorrectedAmountCur;

bankAccountStatementTmp.update();
ttscommit;
}

But getting the outpur like this

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Giorgio Bonacorsi 928 on at

Like (1)

Report

Hello,

Can we have more information about the requirements? The bank account statement is performed using the BankAccountTrans.

So, I'm assuming, reading your code, that you talk about a statement of your customer/vendor's bank account. However, as a Finance consultant (I'm not a developer), I can try to give some tips:

It's not exit a transaction where it's not at least in debit or credit:

lll

Try to take the previous balance, as simpler as inheriting of the previous loop value:

PrevBalance = bankAccountStatementTmp.CurrentBalance

Thank you, I hope that was helpful for you.

Kind regards,

Giorgio Bonacorsi

Was this reply helpful? Yes No
André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

Like (0)

Report

Hi M_R,

When reading your question, I think this is a duplicate or continuation of your other question where we discussed already some details. Solved: bankaccountstatement report (dynamics.com)

Anyway, you marked the previous post as solved and it looks like you made some progress. Have you used the debugger to find out what exactly is being executed and what values are being filled when e.g. data is being returned from select statements? Have you verified if the field bankAccountStatementTmp.SourceRecId has a value and if it is the correct one to search for a related ledgerJournalTrans record?

Was this reply helpful? Yes No
M_R 123 on at

Like (0)

Report

Yes i have verified if the field bankAccountStatementTmp.SourceRecId, for credit and debit, but now facing issue with balance

can we do it through the expression if we could store the balance as current balance.

stilll trying with running value expression with current balance,

but we want running value of balance + debit - credit

not getting how it could be?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,035 Super User 2025 Season 2 on at

Like (0)

Report

Hi M_R,

Can you confirm if the column Balance is printed using the value in the field bankAccountStatementTmp.XTSCorrectedAmountCur?

Have you checked if the sorting of the while loop is the same as sorting of the output on the report?

From where do you get the Opening balance value? The first time in the loop, the PrevBalance variable might not be filled. At least, this is not provided in the part of the coding you shared in your question.

In your coding, the second else should never be hit. When would you expect that this would be the path and what is the purpose of adding and subtracting the same amount?

Was this reply helpful? Yes No
Verified answer

M_R 123 on at

Like (0)

Report

Thanks for your suggestions!

get balance through the expression of

=Fields!CurrentBalance.Value - RunningValue(Fields!Credit.Value,sum,Nothing) + RunningValue(Fields!Debit.Value,sum, "BankAccountStatementDS")

Was this reply helpful? Yes No