How to calculate opening balance

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.

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 <.

Parameters and 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 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 < so they can be passed back to std report?

Sorry probably an obvious answer but am stuck.

Thanks for you help in advance

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19513 how to calculate opening balance

Source: www.crystalreportsbook.com

Category: Forex

Similar articles: