Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Calculating Running Total Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Topic: Calculating Running Total
    Posted: 17 Sep 2009 at 2:22am
Hi all,
I have developed a report that will display a customer statements. In this statement first i want to display the opening balance. For that i have the following formula called runningBal.
 
numberVar runningBal:= {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-{RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT};
 
And i want to show the each transaction and balance based on debit and credit of the customer account. For that i need to do the running total.
 
For example my opening balance = 1000, and my account has been credited by rs 100, then i want to show the balance as 1100 (1000+100) and if rs 100 is debited the i want to show 1000 (1100 - 100). This is need show in the detail section.
 
The following is the logic,
 
Logic:

1. Running Bal_0 = runningBal Calculated above

2. Running Bal_1 = Running _Bal_0+ [CR_AMT_1- DR_AMT_1]

3. Running Bal_2 = Running _Bal_1+ [CR_AMT_2- DR_AMT_2]

4. Running Bal_3 = Running _Bal_2+ [CR_AMT_3- DR_AMT_3]

5. Running Bal_4 = Running _Bal_3+ [CR_AMT_4- DR_AMT_4]

.

.

Please help me for doing running total. That is want to take the last balance for calculate the next running balance as per the above logic.

Edited by Shivacon - 17 Sep 2009 at 2:25am
Thanks,
Shiva
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 17 Sep 2009 at 6:11am

you could do it with shared variables, like you have already done.  Just write a new formula to update the balance and place it on each detail line.  If you want to reset the balance, call your formula in the appropriate group header.  If you want the balance to continue, don't reset it...ie place the formula that you have in the Report header where it will only be executed once.

 
HTH
IP IP Logged
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Posted: 17 Sep 2009 at 9:48pm
The following are the formulas i am using for the calculation.
 
Formula 1
 
numberVar Opening Balance := {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-{RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT};
 
The below formula for each transaction. This i have placed in the detail section. The below formula should hav work as per the below logic.
 
Logic:

1. Running Bal_0 = Opening Balance Calculated above

2. Running Bal_1 = Running _Bal_0+ [CR_AMT_1- DR_AMT_1]

3. Running Bal_2 = Running _Bal_1+ [CR_AMT_2- DR_AMT_2]

4. Running Bal_3 = Running _Bal_2+ [CR_AMT_3- DR_AMT_3]

5. Running Bal_4 = Running _Bal_3+ [CR_AMT_4- DR_AMT_4]

 
Formula 2
 
 
 
For that above logic i am using Formula 2. But, to calculate Running_Bal_2 i need Runnig_Bal_1 but it is taking Running_Bal_0.
 
Help me to resolve this..
Thanks,
Shiva
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 17 Sep 2009 at 10:42pm

Try like this

Formula 1

numberVar Opening Balance;

IF OnFirstRecord THEN
Opening Balance := {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-{RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT};

ELSE
Opening Balance:= Opening Balance+ [CR_AMT- DR_AMT]


Place it in Details no need for formula 2

HTH,
Jyothi
IP IP Logged
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Posted: 17 Sep 2009 at 11:19pm
Thanks Jyothi. Its working. But one more issue is there. I have done this report for customer account statement grouping by account number.
But the opening balance is keep on adding for all the accounts. But i want to reset the opening balance at the end of the particular account statement and need to start fresh calculation for the next account.
Thanks,
Shiva
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 17 Sep 2009 at 11:48pm
in that case 2 formulas are required


Formula 1: Place it in group header

Shared NUMBERVAR bal;
IF OnFIRSTRECORD OR GroupName({ACNo})<> CSTR(Previous({ACNo})) THEN
bal:= {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-   {RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT}


Formula 2: Place it in Details

shared numberVar bal;
bal:= Bal+ [CR_AMT_4- DR_AMT_4]


HTH,
Jyothi      
IP IP Logged
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Posted: 18 Sep 2009 at 12:05am
Thanks a lot. Its working fine....:-)
from last 3 days onwards i am trying this. Now i got the solution.
Thanks u very much Jyothi. Hope will meet you on next issue....:-)
Thanks,
Shiva
IP IP Logged
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Posted: 23 Sep 2009 at 8:51am
Dear Jyothi,
 
I have one more issue on calculating the running balance. I have designed an another report for gl account statement. This report has two group, first group is currency and another one is for account number. I want to sumup the balance for both groups. For account i have done the running balance as per your earlier suggestion. For doing running balance for currency group i want to sumup the balance calculated for the all the account which having the same currency. That is i need to calculate the running balance for the same currency accounts and place that on the currency group footer.
 
I have tried but bal amount(calculated in the formula 2) is no more available...
 
please help me for this.
 
Thanks,
Shiva
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 23 Sep 2009 at 9:11pm
as we are resting the bal in group header..bal amount will not be available for the next group(Account)...

1. Change the previous formula...(AC NO GROUP HEADER)

Shared NUMBERVAR bal;
Shared NUMBERVAR curamt;

IF OnFIRSTRECORD OR GroupName({ACNo})<> CSTR(Previous({ACNo})) THEN (
curamt:= curamt + bal;
bal:= {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-   {RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT}
)

2. Place this in Currency group header
Shared NUMBERVAR curamt;
IF OnFIRSTRECORD OR GroupName({Currency})<> CSTR(Previous({Currency})) THEN
    curamt :=0;
    

Though its not tested ..Hope this gives you some idea...

Jyothi
IP IP Logged
Shivacon
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: India
Online Status: Offline
Posts: 26
Quote Shivacon Replybullet Posted: 23 Sep 2009 at 10:31pm
Dear Jyothi,
It is working partially. The problem is, not sumup the last account balance in the currency group. For example if i have 5 accounts under currency GBP, the above suggested formula is calculating the sum for first four account and missed the 5th account balance.
 
I am trying to findout this. Let me know if any other solution...
 
 
Dear Jyothi,
 
I have tried like the following. It seems like working...
 
I have one more formula on that i am storing the bal amount(Particular account amount) into currency amount instead of storing in the account group header formula. The following are the formulas.
 
Formula 1:(Account group header will be same. No changes)
 
Shared NUMBERVAR bal;

IF OnFIRSTRECORD OR GroupName({ACNo})<> CSTR(Previous({ACNo})) THEN (
bal:= {RPT_DP_CUSTOMER_STMT_SP.CLEARED_BAL}+{RPT_DP_CUSTOMER_STMT_SP.TOTAL_DR_AMT}-   {RPT_DP_CUSTOMER_STMT_SP.TOTAL_CR_AMT}
)
 
Formula 2: (For storing currency amount )
 
shared numberVar bal;
shared numberVar currencybal;
currencybal:=currencybal+bal;
bal;
 
Formula 3: (currency header which you have suggested)
 
shared numberVar currencybal;
if OnFIRSTRECORD OR GroupName({RPT_GL_ACCOUNT_STMT_SP.CRNCY_CD_ISO})<> CSTR(Previous({RPT_GL_ACCOUNT_STMT_SP.GL_ACCT_NO})) THEN
currencybal:=0;
 
Thanks Jyothi.... :-)


Edited by Shivacon - 24 Sep 2009 at 12:05am
Thanks,
Shiva
IP IP Logged
Page  of 2 Next >>
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.027 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.