Hi
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.
For example:
(DATA)
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 $65.00
BNKFEE TOTAL $143.00
The opening balance would be sum of Account Code Amount <{Start Date}.
Parameters {Start Date} and {End Date} would be required to total values for user defined period.
I have created a report with group#1 on COA, then applied a group filter to parameter nominated {?Start Date} and {?End Date} 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 <{?Start Date}, 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 <{Start Date} so they can be passed back to std report?
Sorry probably an obvious answer but am stuck.
Thanks for you help in advance
|