Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculating Opening Balance for amounts less than ?StartDate

Status
Not open for further replies.

Bhuven

Programmer
Apr 16, 2007
11
ZA
thread767-1767157

Please assist me with this:

I am printing a ledger report where the user enters a start and end date. I want to be able to add all (debit - credit) transactions where the transaction date is less than the ?startdate to get the opening balance for each account in the ledger. I have tried several methods without success, and then used the suggestion referenced in this thread, but get a zero amount when printing the report.

1. I created this formula:
if {Transaction.TransactionDate} < {?Start Date} then {Transaction.TransactionDebit} else 0. (Was going to do a another for credits and subtract)
2. I added the formula to the detail line and SUM then added SUM to header and suppressed the deail. Still prints zero.
3. l also want to be able to show the balances for accounts that donot have transactions for the selected periods

I would be most appreciative for assistance with a solution, spent nearly two weeks on this,
Many thanks
bhuven


 
Have you excluded dates earlier than {?StartDate} in your record selection formula? Your technique should work unless you have restricted the report as a whole to a defined start date. What is your record selection formula?

-LB
 

Hi, thank you for your response

I have:
{Transaction.TransactionDate} in {?StartDate} to {?End Date} and I sort the date in ascending order

 
Remove the record selection formula. The earlier transactions have to be “IN” the report in order to contribute to the opening balance. Then go to report->selection formula->GROUP and enter:

{transaction.transactiondate}>={?StartDate}

This will allow only the transactions within the time period to display.

Then use the formulas you mention earlier to get the opening balances. If you group on account, you would insert the sum on your formulas at the group level.

Similarly, to get summaries during the current period, use formulas like this:

If {transaction.transactiondate}>={?startdate} then {transaction.transactiondebit}

Then insert at the group level as before.

-LB
 
Thank you so much, I am getting figures coming through and need to check balances etc.
To get the end date will I have to do the same like the start date above?
 
Thank you LB, works perfectly, much appreciated

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top