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

(0) Share

Report

Posted on by M_R

125

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

All responses (5)

Answers (1)

Verified answer

M_R 125 on at

Like (0)

Report

how to get balance according to credit and debit in the bank statement 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")
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

how to get balance according to credit and debit in the bank statement 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?
M_R 125 on at

Like (0)

Report

how to get balance according to credit and debit in the bank statement 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?
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

how to get balance according to credit and debit in the bank statement 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?
Suggested answer

Giorgio Bonacorsi 487 on at

Like (1)

Report

how to get balance according to credit and debit in the bank statement 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