How to calculate opening balance
Posted: 07 May 2013 at 2:00pm
Using Crystal 2011. Connecting to Pervasive 11 database via ODBC.
The database holds only transaction details and values.
Upon report request (system not Crystal) it literally calculates all balances from the date of database inception to dates less than period nominated by user.
e.g. Opening balance calc for user nominating period 1/7/12 - 31/7/12 would be 1/1/2003 (database inception) to 30/6/12.
I want to create a report that lists each chart of account - an opening balance, the transactions within the user nominated period and then calculates the closing balance being the sum of opening balance + or - transactions for period.
ACC CODE DATE REF AMOUNT
ACCFEE 1/7/10 888 $55.00
ACCFEE 1/7/11 458 $60.00
ACCFEE 30/6/12 871 $99.00
BNKFEE 1/9/10 789 $78.00
BNKFEE 30/6/12 875 $65.00
REQUIRED REPORT SAMPLE -
ACCFEE OPENING BAL $55.00
ACCFEE PERIOD TRAN TOTAL $159.00
ACCFEE TOTAL $214.00
BNKFEE OPENING BAL $78.00
BNKFEE PERIOD TRAN TOTAL
BNKFEE TOTAL $143.00
The opening balance would be sum of Account Code Amount <
I have created a report with group#1 on COA, then applied a group filter to parameter nominated and This grabs the correct transactions however I am having issue in calculating the opening balance. I have tried a running total with formula of Sum on Amount for dates >= 1/1/2003 (database inception) and <, reset on each group change but have no values returned. Clearly this is not the correct option.
How do you suggest this is calculated? I am thinking a subreport but how do I tell this subreport to calculate balances <
Sorry probably an obvious answer but am stuck.
Thanks for you help in advance