Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Calculate Opening Balance Post Reply Post New Topic
Author Message
Banana
Newbie
Newbie


Joined: 07 May 2013
Online Status: Offline
Posts: 1
Quote Banana Replybullet Topic: Calculate Opening Balance
    Posted: 07 May 2013 at 2:00pm
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
IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet Posted: 08 May 2013 at 3:06am
Just thinking this through...
create variables OpenBal and TransBal and FinalBal
Note that txdate is transaction date and txamt is transaction amount in your DATA file

Define the variables
For OpenBal, if (txdate < StartDate) then txamt
For TransBal, if (txdate >= StartDate) and (txdate <EndDate) then txamt
For FinalBal, if (txdate < EndDate) then txamt

Detail line (suppressed)
Account   OpenBal   TransBal   FinalBal
Group line (on Account)
Account   Sum(OpenBal)   Sum(TransBal)   Sum(FinalBal)

Your summary is rough and on one line. From there, you could play with the Group footer line to convert that one line sample into the three lines you actually want.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.