Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Cumulative Balance/Running Total Post Reply Post New Topic
Author Message
abing430
Newbie
Newbie
Avatar

Joined: 16 Jan 2011
Online Status: Offline
Posts: 5
Quote abing430 Replybullet Topic: Cumulative Balance/Running Total
    Posted: 16 Jan 2011 at 5:58pm
hello all,

i have this 3 fields, and from this 3 fields i want to calculate a running cumulative balance.

this are the ff. fields

1. Opening Balance
2. Debit
3. Credit

the formula is Opening balance + debit - Credit. this is my actual CR formula

({OpeningBalance.Open Balance} + {BP_AccountBalance;1.Credit}) - {BP_AccountBalance;1.Debit}

but using the above formula does not get the desired result.

please help.

thanks.

Fidel
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 17 Jan 2011 at 3:35am
shared numbervar dBalance;
dBalance := dBalance + {BP_AccountBalance;1.Credit} - {BP_AccountBalance;1.Debit} ;
dBalance  //display the running balance;
 
this would be in the detail section.
 
in the group header
shared numbervar dBalance := {OpeningBalance.Open Balance};
"" // hide the output
 
group footer
shared numbervar dBalance
 
HTH
IP IP Logged
abing430
Newbie
Newbie
Avatar

Joined: 16 Jan 2011
Online Status: Offline
Posts: 5
Quote abing430 Replybullet Posted: 18 Jan 2011 at 4:56pm
hello lockwelle,

thanks a lot to your response. you formula works fine. but i have problem with duplicated rows in the debit side. i just used the suppress if duplicated in the format editor. how will i say to crystal report that only the first record from the duplicated rows will be minus in {dBalance} + (Credit).

please see my screen shot

http://i264.photobucket.com/albums/ii184/abing430/CRDuplicatedRows.png

http://i264.photobucket.com/albums/ii184/abing430/CRDuplicatedRows-1.png
please help

best regards,

Fidel

Edited by abing430 - 18 Jan 2011 at 6:35pm
IP IP Logged
abing430
Newbie
Newbie
Avatar

Joined: 16 Jan 2011
Online Status: Offline
Posts: 5
Quote abing430 Replybullet Posted: 20 Jan 2011 at 10:51pm
hi,

since i have a problem with duplicated rows, please see my screen shot above. i tried grouping it by Ref(field) and now place the detail formula in the Group Footer, the Duplicated rows is not a problem anymore, but when i check the 2nd page of the report, instead of carrying the running total from 1st page to the second page, it does not. but it get the Value of OpeningBalance like what it did in the 1st page.

ive been working this in the last few days, and im stack with this.
please help...

Fidel
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Jan 2011 at 10:19am
do you have a reset formula in the group header or the pager header.  How does it get the opening balance, does getting the balance override / reset the variable.
 
I haven't looked at the picture, but if the shared/global variable is working on the page, the page change must be affecting it in some way.
IP IP Logged
abing430
Newbie
Newbie
Avatar

Joined: 16 Jan 2011
Online Status: Offline
Posts: 5
Quote abing430 Replybullet Posted: 24 Jan 2011 at 2:37am
Hello Lockwelle,

im very thankful with you. i have manage to finished my report with your help.

with the problem reseting the OpenningBalance i use this formula in the page header

whileprintingrecords;
if pagenumber = 1 then Shared NumberVar OB := {OpeningBalance.Open Balance}
else
0

anyway, how can i reward you point for helping me.

many thanks.

Fidel
IP IP Logged
shiloh
Newbie
Newbie


Joined: 13 Oct 2011
Online Status: Offline
Posts: 26
Quote shiloh Replybullet Posted: 16 Oct 2011 at 11:49pm
Originally posted by lockwelle

shared numbervar dBalance;

dBalance := dBalance + {BP_AccountBalance;1.Credit} - {BP_AccountBalance;1.Debit} ;

dBalance  //display the running balance;

 

this would be in the detail section.

 

in the group headershared numbervar dBalance := {OpeningBalance.Open Balance};

"" // hide the output

 

group footer

shared numbervar dBalance

 

HTH

hello lockwelle,

I try to apply your formula into my CR report,

I face this problems:
"A running total cannot refer to a print time formula detail @BFAmount"

I set the formula as per your guide but I do add the running total tool too.

Should I take out the running total too?

Thanks for you help
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 17 Oct 2011 at 12:10pm
alas, I don't use Running Totals, so I do not know.  DBlank is the master of the running total.
 
I am sure that both methods can be used at the same time, as they do basically the same thing, but as to how they play together, I don't know.
IP IP Logged
shiloh
Newbie
Newbie


Joined: 13 Oct 2011
Online Status: Offline
Posts: 26
Quote shiloh Replybullet Posted: 17 Oct 2011 at 11:36pm
After take out the running tool, my report is running correctly.

But another question raise now.
I have a b/f value, but not transaction for that accounts/product. Then the whole detail is not shown up.

How to solve this problems? Any suggestion on this.

Other all work perfectly.
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.