bankaccountstatement report

(0) Share

Report

Posted on by M_R

125

hi there, here i am going to create duplicate of bankaccountstatement report, which has the requirement of like this

In this description should come from customer payment journal and the credit and debit values should be from bankAccountTrans.AmountCur in which -ve and +ve values get divided into credit and the debit. Balance is the current balance here, in that credit - Prev banalce = current balance and debit + Prevbalance = current balance should be there, i have store current balance value as sthe Prevbalance in the code,

but i am unable to pick up the values, please help me out. here is my code, pls give some suggestions.

[
SRSReportQueryAttribute(queryStr(TSBankAccountStatementNew)),
SRSReportParameterAttribute(classstr(TSBankAccountStatementNewContract))
]
class TSBankAccountStatementNewDP extends SrsReportDataProviderPreProcessTempDB
{
AmountCur currentBalance;
TransDate fromDate;
TransDate toDate;
CompanyBankAccount bankAccount;
TSBankAccountStatementTmpNew bankAccountStatementTmp;
AmountCur PrevBalance;
//real currentBalance;

/// <summary>
/// Calculates the opening balance from fromDate.
/// </summary>
/// <returns>
/// The opening balance.
/// </returns>
private AmountCur calculateOpeningBalanceForBankAccount()
{
BankAccountTrans bankAccountTrans;

select sum(AmountCur), sum(AmountCorrect) from bankAccountTrans
where bankAccountTrans.AccountId == bankAccount
&& bankAccountTrans.TransDate < fromDate;

return bankAccountTrans.AmountCur + bankAccountTrans.AmountCorrect;
}

/// <summary>
/// Adds the records to the <c>BankAccountStatementTmp</c> table.
/// </summary>
private void createTSBankAccountStatementTmpNew()
{
BankAccountTable bankAccountTable;
BankAccountTrans bankAccountTrans;
LedgerJournalTrans ledgerJournalTrans;

insert_recordset bankAccountStatementTmp
(
FromDate,
ToDate,
CurrentBalance,
BankTransType,
TransDate,
Voucher,
DepositNum,
PaymReference,
PaymentMode,
AmountCur,
AmountCorrect,
AmountMST,
AmountMSTSecond,
AccountID,
Name,
CurrencyCode
//TSDescription
)
select
fromDate,
toDate,
currentBalance,
BankTransType,
TransDate,
Voucher,
DepositNum,
PaymReference,
PaymentMode,
AmountCur,
AmountCorrect,
AmountMST,
AmountReportingCurrency
from bankAccountTrans
where bankAccountTrans.AccountId == bankAccount
&& bankAccountTrans.TransDate >= fromDate
&& bankAccountTrans.TransDate <= toDate
join
AccountID,
Name,
CurrencyCode
from bankAccountTable
where bankAccountTable.AccountID == bankAccount;

// join
// Txt from ledgerJournalTrans
// where //bankAccountStatementTmp.TSDescription == ledgerJournalTrans.Txt;
//LedgerJournalTrans.PaymentAccount == bankAccount;

//select * from ledgerJournalTable where LedgerJournalTable.BankAccountId == BankAccountTable.AccountID;
//bankAccountStatementTmp.TSDescription = ledgerJournalTable.Name;
//bankAccountStatementTmp.insert();

select count(RecId) from bankAccountStatementTmp;

if (bankAccountStatementTmp.RecId == 0)
{
bankAccountTable = BankAccountTable::find(bankAccount);
bankAccountStatementTmp.AccountID = bankAccountTable.AccountID;
bankAccountStatementTmp.Name = bankAccountTable.Name;
bankAccountStatementTmp.CurrencyCode = bankAccountTable.CurrencyCode;
bankAccountStatementTmp.CurrentBalance = currentBalance;

bankAccountStatementTmp.insert();
}
else
{
if(bankAccountStatementTmp)
{
ttsbegin;
while select forupdate bankAccountStatementTmp
{
select * from bankAccountTrans where BankAccountTrans.AccountId == BankAccountTable.AccountID;
if(bankAccountStatementTmp.AmountCur>=0)
{
bankAccountStatementTmp.TSDebit = bankAccountTrans.AmountCur;
}
else
{
bankAccountStatementTmp.TSCredit = bankAccountTrans.AmountCur;
}
}

PrevBalance = bankAccountStatementTmp.TSCorrectedAmountCur;

if(bankAccountStatementTmp.TSCredit)
{
bankAccountStatementTmp.TSCorrectedAmountCur = PrevBalance - bankAccountStatementTmp.TSCredit;
}
else
{
bankAccountStatementTmp.TSCorrectedAmountCur = PrevBalance + bankAccountStatementTmp.TSDebit;
}

}

}
}

}

/// <summary>
/// Retrieves the temporary table for SQL Server Reporting Services reports.
/// </summary>
/// <returns>
/// The <c>BankAccountStatementTmp</c> temporary table.
/// </returns>
[SrsReportDataSetAttribute(tablestr(TSBankAccountStatementTmpNew))]
public TSBankAccountStatementTmpNew getTSBankAccountStatementTmpNew()
{
select bankAccountStatementTmp;

return bankAccountStatementTmp;
}

/// <summary>
/// Processes the business logic that is used to populate a <c>BankAccountStatementTmp</c> temporary
/// table.
/// </summary>
public void processReport()
{
TSBankAccountStatementNewContract contract = this.parmDataContract() as TSBankAccountStatementNewContract;

fromDate = contract.parmFromDate();
toDate = contract.parmToDate() ? contract.parmToDate() : dateMax();

bankAccount = contract.parmBankAccount();

currentBalance = this.calculateOpeningBalanceForBankAccount();

this.createTSBankAccountStatementTmpNew();
}

}

All responses (13)

Answers (1)

M_R 125 on at

Like (0)

Report

bankaccountstatement report

can we take instance of dp or inserting evnethandler of tmp table to calculate the balance in this report

which will pass the current balance value to the first row and calculate the balance

then this balance will be the current balance for the second row

pls suggest something for this
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

bankaccountstatement report

Hi M R,

You added a new reply with only the word "added" and marked it as a verified answer. Is your issue about empty debit, credit, and the description solved now?
If you solved your issue, can you provide details about what replies were helpful for you? Maybe you can also share your corrected coding?

Note that other persons with similar questions might come across this post and would like to learn from this experience. In that case, a verified answer with only "added" is not helpful for them. In addition, both Layan and I will wonder what answers helped you.
Verified answer

M_R 125 on at

Like (0)

Report

bankaccountstatement report

added SourceRecId to the temp table and wrote this query for txt in the report

while select forupdate bankAccountStatementTmp

            {

                select firstonly ledgerJournalTrans

                    where ledgerJournalTrans.RecId == bankAccountStatementTmp.SourceRecId;

                bankAccountStatementTmp.Txt = ledgerJournalTrans.Txt;

                bankAccountStatementTmp.update();

    }
Suggested answer

André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

bankaccountstatement report

Hi M_R,

It looks like you are ignoring my feedback. Selecting bank transactions based on only a bank account number will never give the correct result. You would need a voucher and transaction date. You are doing this for the debit and credit amounts, as well as getting a transaction text from a ledger journal trans record. Review your coding and use the correct where clauses to retrieve the correct records.
Layan Jwei 7,349 Super User 2024 Season 2 on at

Like (0)

Report

bankaccountstatement report

Hi,

Why would you insert then update directly, can you please share the correct code that you have.

When you debugged, did ledgerJournalTrans.Txt has a value?

Thanks,

Layan Jweihan

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
M_R 125 on at

Like (0)

Report

bankaccountstatement report

want description in bankAccountStatement report from customer and vendor payment,

i have tried with this code, but not giving description in the report.

////select * from ledgerJournalTable where ledgerJournalTable.BankAccountId== bankAccountTable.AccountID;

select * from ledgerJournalTrans where ledgerJournalTrans.PaymentAccount == bankAccountTable.AccountID;

bankAccountStatementTmp.Txt = ledgerJournalTrans.Txt;
bankAccountStatementTmp.insert();
bankAccountStatementTmp.update();

pls, suggest.
André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (1)

Report
bankaccountstatement report

.Hi M R,

Have you checked my reply below where I mentioned that you might retrieve a wrong bank account transaction?

select * from bankAccountTrans where BankAccountTrans.AccountId == BankAccountTable.AccountID;

This code retrieves a bank account transaction regardless of the voucher and transaction date. You can have thousands of records for the same AccountID. Then you are using the amounts from this table buffer for the debit and credit columns. I would check this part of your coding. If you are debugging and no data is selected, on each step you can check the intention of your coding and if it is getting the correct values in all variables. You can put a breakpoint on this line to check if you have the correct bank account transaction or not. My thought is that you don't need this table buffer, but can use the field bankAccountStatementTmp.AmountCur directly.
M_R 125 on at

Like (0)

Report

bankaccountstatement report

Have remaining 3 fields also,

join
AccountID,
Name,
CurrencyCode
from bankAccountTable
where bankAccountTable.AccountID == bankAccount;

when i debug the code, no data is being selected there and the debugger got aborted
Suggested answer

Layan Jwei 7,349 Super User 2024 Season 2 on at

Like (1)

Report

bankaccountstatement report

Hi M_R,

if you say that "Description/credit/debit values are not coming in the report through your code." Then yes, please debug and let us know if you can see a value for them -- if they are empty, then it means there is sth wrong with the code. if you can see a value, then maybe in the report designer you are pointing to a wrong field.

looking in general, i can see the fields you select are less than the fields you insert. Also which of those are the description field, because i can see one description field is commented out

and this piece of code is the one responsible for credit and debit so please debug

Thanks,
Layan Jweihan
Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
Suggested answer

Waed Ayyad 6,408 Super User 2024 Season 2 on at

Like (0)

Report

bankaccountstatement report

Hi @M_R,

Try to debug the code as mentioned below, so we can help you better. But from the code did you try to add the calculations as expressions on the report?

Thanks

Waed Ayyad

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future