Calculating Opening Balance for amounts less than ?StartDate

[Bhuven]

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

 

[lbass]

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

 

[Bhuven]

Hi, thank you for your response

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

 

[lbass]

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

 

[Bhuven]

Thank you, will try

 

[Bhuven]

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?

 

[Bhuven]

Thank you LB, works perfectly, much appreciated