Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Summing up a calculated sum field Post Reply Post New Topic
Author Message
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Topic: Summing up a calculated sum field
     Posted: 26 Jun 2014 at 5:55am
I have a report that I need to sum up. The field I am summing is a field that is multiplying a percentage against a summed field.
 
EX:
 
1.25
.50
____
1.75 * 90% = 1.575
 
1.50
.85
____
2.35 * 40% = .94
 
 
I need to sum up 1.575 + .94 = 2.52
 
This is the formula I am using for my individual line totals called Comm Amt 2:
 
({@% paid of amt due}*Sum ({@Comm Amt}, {CASH_RECEIPT_LINE.CHECK_ID}))/100
 
Next for the grand total (Comm Total)  I have:
 
whileprintingrecords;
shared numbervar CommTotal;
CommTotal:= CommTotal +{@Comm Amt 2};
CommTotal;
 
This works fine until the very last record. It adds it in again. Using the example above it would be:
 
1.575 + .94 + .94 = 3.455
 
I have a reset formula set up called: CommTotalReset:
 
whileprintingrecords;
shared numbervar CommTotal;
CommTotal=0;
CommTotal;
 
I have tried putting this at every level and it doesn't seem to make a difference.
 
 
How do I correct this? Any suggestions? I feel like I'm missing some small thing and just can't quite figure it out.
 
Thank you in advance for any help you can provide.
 
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Jun 2014 at 6:05am

In general you need another formula as a display formula that you place in the footer so it does not do another calculation for the last record (calculated in the footer too).

basically 3 formula
1 as your reset in the header
2 as your addition in the detail section
3 as your footer display
 
Note it will include suppressed rows unless your calculation code excludes them.


Edited by DBlank - 26 Jun 2014 at 6:06am
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 26 Jun 2014 at 6:19am
That makes sense. I thought that's what I was doing with Comm Total formula. How would I make the formula you are suggesting?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Jun 2014 at 7:34am
I believe you just need this for your footer
 
whileprintingrecords;
shared numbervar CommTotal;
CommTotal;
 
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 26 Jun 2014 at 7:40am
You are a Genius!!! Thank you sooo much it works perfectly!! I knew it was something simple. Just couldn't remember. This website is the best.
Thanks again! Have a great day/night!
IP IP Logged
cheryla
Groupie
Groupie


Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
Quote cheryla Replybullet Posted: 03 Sep 2014 at 8:05am
Here I am again still having an issue with this report. I thought it was fixed and was running smoothly (as noted in my previous post). Well, it does run fine for most salespeople but not for all. So now what can I do? It is still doubling the last $ figure in the final total but again not for every salesperson. Any suggestions as we are finally ready to implement this report and I have to get it fixed.
Any help is appreciated. TYTYTY!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Sep 2014 at 10:08am

Usually this has to do with placing the calculation formula in both the detail and footer. It runs the calculation each time it displays. So shared Variable formulas usually have 3 formulas in volved.

1- placed in a header to set a value to 0,
2- one in the detail section to run the calculation
3- a third placed in a footer for the purpose of display of a final tally.
 
If you were to not use the display formula (3rd) in the footer but rather put the calculation formula (2nd) in both the detail and footer sections it will double the values from the last row because it calculates once in the last detail row and then a second time in the footer (a footer has the same data values as the last row before it) thereby doubling the last set of values.


Edited by DBlank - 03 Sep 2014 at 10:11am
IP IP Logged
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