Debit Credit Balance and opening Balance Calculation

0
votes

I have Sql database with following columns:

  • AcccountName
  • Debit
  • Credit
  • DR/CR

I set date range and account name criteria to generate the crystal report.

For example, if i select two date ranges from dtpickers and account name "CASH" from combobox present in crystal report form. it will generate me all cash transactions within selected date range.

What I want is while generating crystal report with one more columns containing running balance. what I have tried so far in formula field:

For debit credit calculation:

global numbervar Balance;
balance = 0;
if {Transactionss.DR/CR} = "DR" then
balance := balance +{Transactionss.Debit}-{Transactionss.Credit}
else  
balance := balance+{Transactionss.Credit}-{Transactionss.Debit}

But it is not working. Moreover i also want to bring previous balance and connect this opening balance with this debit credit calculation.

Sample image:

Sample Image

1
sqlvb.netcrystal-reports
Where is your SQL query? you can add running balance column within SQL query itself. – CleanBold
I can not do it in sql because there are diffrent account names. I need it it in Crystal report with particular account name i select through combobox. – Talat Farooq
Your code says "DR" and your pictures says 1 – James Z
Quite many SQL databases are capable of calculating running totals per account name without any problems. – James Z
I am sorry it should be Dr i forget to change before taking screen shot. But i am a learner. Could u pls explain the query here to get running total with date and account name critera sql database. – Talat Farooq

1 Answers

0
votes

I think you should use a running total field.

But if you want to continue in this way, I think you are having trouble with null values. So, try something like this:

global numbervar balance;
numbervar debit := 0;
numbervar credit := 0;
if not isnull({Transactionss.Debit}) then debit := {Transactionss.Debit};
if not isnull({Transactionss.Credit}) then credit := {Transactionss.Credit};
if {Transactionss.DR/CR} = "DR" then
  balance := balance + debit - credit
else  
  balance := balance + credit - debit

It may have some mistake, because I can't test this formula right now.

It may be necessary to start the balance variable somewhere before, because it's initial value may be is unknown. For example, put the following formula in report header.

global numbervar balance;
balance : = 0;