Crystal Report 2011 Calculate Opening balance 1

[icerubes]

Hi
Using Crystal 2011. Connecting to Pervasive 11 database via ODBC.

The database calculates Chart of Accounts (COA) opening / closing values each and everytime user nominates a period. It DOESNT hold any values other than at transaction level . It literally calculates the balance from the date of database inception to dates less than period nominated but 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 for each chart of account - an opening balance, the transaction within the user nominated period and then calculates the closing balance but the sum of opening balance + or - transcations for period.

e.g
ACCOUNT
BANK opening balance 5500.00
1/7/2012 Tran #1 -100.00
3/7/2012 Deposit 900.00
closing balance 6300.00

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.

Sorry probably an abvious answer but am stuck.

Thanks for your help.

 

[Madawc]

Where are your totals? Running totals accumulate as the data is printed. Summary totals come direct from the data and so can be shown in a group header.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[icerubes]

Sorry I didn't explain very well

There are no totals. That is what I am trying to calculate.

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.

What I need help in calculating is the opening balance. How do you suggest this is calculated? I am thinking a subreport but how do I tell this report to calculate balances <{Start Date}?

Thanks for you help in advance

 

[pmax9999]

It looks to me like you are limiting your record selection to only those transactions within the user defined period.

As you need all transactions from inception to the user defined "Start" date so as to be able to calculate the opening balance, you will need to include every transaction in the database up to the user defined "End" date.

The calculation for opening balance will be the sum of the transactions from inception up to the day prior to the "Start" date, and the closing balance will be the sum of all transactions up to the "End" date.

If you want to see only the transactions that occur during the user defined period, simply suppress those where the transaction date is prior to the "Start" date.

Hope this helps.

Cheers
Pete

 

[icerubes]

Hi Pete
Thanks for response.

Totally agree with your comments re: requirement of every transaction in the database.

I created the below formula to calculate the opening balance (including the grouping per account code) else the whole of all transaction values are totalled rather than per account code as required.

if
({COATRAN2.DATE} <{?Start Date}) then
sum({COATRAN2.AMOUNT}, GroupName({COATRAN2.ACCOUNT_CODE})) else 0

However I am receiving the error (highlighting the GroupName({COATRAN2.ACCOUNT_CODE}) - "this field cannot be used as a group condition field"

Appreciate your thoughts.

Thanks for your assistance.

 

[pmax9999]

Add a formula as follows:

If	{COATRAN2.DATE} < {?Start Date}
Then	{COATRAN2.AMOUNT}

Do a SUM on this formula at the {COATRAN2.ACCOUNT_CODE} group level and move it to the Group Header. This will represent the Opening Balance for that Account.

The SUM of all {COATRAN2.AMOUNT} will represent the Closing Balance.

Hope this helps

Cheers
Pete

 

[icerubes]

Awesome!! Thank you so much!
I knew I was making this much harder than it actually was