Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Summing problem Post Reply Post New Topic
Page  of 2 Next >>
Author Message
grego
Newbie
Newbie
Avatar

Joined: 22 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 16
Quote grego Replybullet Topic: Summing problem
    Posted: 20 Mar 2009 at 4:03am
I have a crystal XI report where I want to see the a field that I have in a group footer summed in the report footer
 
The report is grouped by {CPARTY.CODE}.
 
The field that I want summed is a formula called Jet Fuel which I have added to the details section of the report.  The formula is as follows:
 
 
if {CPARTY.CODE} = "CHAS GP" then -34.1 else
  if {CPARTY.CODE} = "MSLN" then -28.1 else
   if {CPARTY.CODE} = "GSIL GP" then -46.1 else
    if {CPARTY.CODE} = "BARC GP" then -4.6 else
     if {CPARTY.CODE} = "CALY GP" then -1.6 else
        if {CPARTY.CODE} = "SCBL" then -1.5 else
           if {CPARTY.CODE} = "BNPA GP" then -0.9 else
 
 
I then have a formula called new 4 in the group footer with the following formula {@Jet Fuel} (could probably have just put the original Jet Fuel formula in here!
 
I then want the sum of all the numbers in the jet fuel total to be shown in the group footer (ie 116.9).  However when I insert a summary field on the new 4 field and add this to the Group footer it produces a number which bares no resemblance to what I am expecting!
 
any ideas?Smile
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 20 Mar 2009 at 6:33am
You need a couple more formulas and a shared variable.
 
Since you want the sum of these values, not the last one as you are probably getting, what I would do is modify your Jet Fuel formula to something like:
shared numbervar totalJetFuel;
local numbervar thisJet;
 
if {CPARTY.CODE} = "CHAS GP" then thisJet := -34.1 else
  if {CPARTY.CODE} = "MSLN" then thisJet := -28.1 else
   if {CPARTY.CODE} = "GSIL GP" then thisJet := -46.1 else
    if {CPARTY.CODE} = "BARC GP" then thisJet := -4.6 else
     if {CPARTY.CODE} = "CALY GP" then thisJet := -1.6 else
        if {CPARTY.CODE} = "SCBL" then thisJet := -1.5 else
           if {CPARTY.CODE} = "BNPA GP" then thisJet := -0.9 else
 
totalJetFuel := totalJetFuel + thisJet
 
thisJet
 
this will display the same amount as before, but will add tot he running total as it goes.
 
where you want to display the total(in the footer) the formula looks like:
shared numbervar totalJetFuel
 
in the header for the group (probably) you would want to set the total to 0 otherwise you just keep adding and your number make no sense, and that formula would look like:
shared numbervar totalJetFuel := 0;
""   //I place the double quotes to suppress a 0 floating on the report for now reason.
 
Hope this helped.
IP IP Logged
grego
Newbie
Newbie
Avatar

Joined: 22 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 16
Quote grego Replybullet Posted: 20 Mar 2009 at 9:21am
Thanks for the help.  Unfortunately I am still not getting the number I expect.  I am getting a zero in the sum in the report footer (this is where I put the shared numbervar totalJetFuel bit)
 
I amended the jet fuel formula which is in the details section - so that it now looks like this:
 
shared numbervar totalJetFuel;
local numbervar thisJet;
if {CPARTY.CODE} = "CHAS GP" then thisJet := -34.1 else
  if {CPARTY.CODE} = "MSLN" then thisJet := -28.1 else
   if {CPARTY.CODE} = "GSIL GP" then thisJet := -46.1 else
    if {CPARTY.CODE} = "BARC GP" then thisJet := -4.6 else
     if {CPARTY.CODE} = "CALY GP" then thisJet := -1.6 else
        if {CPARTY.CODE} = "SCBL" then thisJet := -1.5 else
           if {CPARTY.CODE} = "BNPA GP" then thisJet := -0.9 else
totalJetFuel := totalJetFuel + thisJet
 
 
There are two groups on the report.  I put the
shared numbervar totalJetFuel := 0;
""
in a formula in the Group header that groups by {CPARTY.CODE}
 
Is there anything else I should change/check
 
thanks for your help
IP IP Logged
JohnT
Groupie
Groupie
Avatar

Joined: 20 Jan 2008
Online Status: Offline
Posts: 92
Quote JohnT Replybullet Posted: 20 Mar 2009 at 2:57pm
Your problem might be the else on the last if statement.  The
'totalJetFuel := totalJetFuel + thisJet' statement probably isn't even being executed.  Try taking the last else off.
 
shared numbervar totalJetFuel;
local numbervar thisJet;
if {CPARTY.CODE} = "CHAS GP" then thisJet := -34.1 else
  if {CPARTY.CODE} = "MSLN" then thisJet := -28.1 else
   if {CPARTY.CODE} = "GSIL GP" then thisJet := -46.1 else
    if {CPARTY.CODE} = "BARC GP" then thisJet := -4.6 else
     if {CPARTY.CODE} = "CALY GP" then thisJet := -1.6 else
        if {CPARTY.CODE} = "SCBL" then thisJet := -1.5 else
           if {CPARTY.CODE} = "BNPA GP" then thisJet := -0.9
totalJetFuel := totalJetFuel + thisJet
 
IP IP Logged
grego
Newbie
Newbie
Avatar

Joined: 22 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 16
Quote grego Replybullet Posted: 23 Mar 2009 at 1:32am
Thanks for the suggestion.  I removed the else and when I try to save the formula down I get
"The remaining text does not appear to be part of the formula." on the totalJetFuel := totalJetFuel + thisJet line.
 
Any other ideas?
 
thanks for your help
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Mar 2009 at 6:21am
Silly me.  Yes JohnT is right, the variable ThisJet, is basically 0, until assigned a value, so the last else was incorrect (should have checked as I just cut and paste)
 
To correct the error, add a semicolon to the end of the last if statement ';'
 
since there are multiple lines in the formula, you need to put in a line terminator.  It is required on all lines--except the last line.
IP IP Logged
grego
Newbie
Newbie
Avatar

Joined: 22 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 16
Quote grego Replybullet Posted: 23 Mar 2009 at 6:35am
This does correct the 'remaining text does not appear to be part of the formula' problem but the value that is returned from the sum in the group footer is still 0.00 (instead of -116.9)
 
The jet fuel formula now looks like this:
 
shared numbervar totalJetFuel;
local numbervar thisJet;

if {CPARTY.CODE} = "CHAS GP" then thisJet := -34.1 else
  if {CPARTY.CODE} = "MSLN" then thisJet := -28.1 else
   if {CPARTY.CODE} = "GSIL GP" then thisJet := -46.1 else
    if {CPARTY.CODE} = "BARC GP" then thisJet := -4.6 else
     if {CPARTY.CODE} = "CALY GP" then thisJet := -1.6 else
        if {CPARTY.CODE} = "SCBL" then thisJet := -1.5 else
           if {CPARTY.CODE} = "BNPA GP" then thisJet := -0.9;

 

totalJetFuel := totalJetFuel + thisJet

 
 
 
 
and the group footer formula (sum of Jet) = shared numbervar totalJetFuel;
 
With
 
shared numbervar totalJetFuel := 0;
""
entered into a formula in one of the two Group Headers (the one containing CPARTY.CODE
 
 
All very confusing!
 
Any other ideas
 
thanks for your help on this
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Mar 2009 at 6:42am
When you look at the detail section of the report is your totalJetFuel number changing as it should be?  If the details are suppressed you might try showing them to help you debug.  Since the last line in the jet fuel formula is the assignment, it should display the results, just like the formula in group footer.
 
is cparty.code a group inside of another group?  If it is, it will constantly be resetting your total to zero, which might be one of the issues.
IP IP Logged
grego
Newbie
Newbie
Avatar

Joined: 22 Feb 2008
Location: United Kingdom
Online Status: Offline
Posts: 16
Quote grego Replybullet Posted: 23 Mar 2009 at 7:06am
Aha - Yes it is a group within a group - I have grouped by cparty name first and then by counterparty code
 
Group Header 1 Cparty Name
Group Header 2 Cparty Code - Where the reset to 0 formula is
Details - Where the jet fuel formula is
Group Footer 2 Cparty Code
Group Header 2 CParty Name
Report Footer  - Where the Sum of Jet formula is
 
Do I need to reposition where the formulas are?  Or do I need to fundamentally re-design the layout!?
 
thanks again
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Mar 2009 at 1:30pm
without knowing the data, I don't know...I am always for moving formulae first.  Can a Name have multiple codes?  It would assume so, from the grouping, so to start I would move the reset to the Name header.  I would move the sum from the report footer to the Name footer, and see if that gives numbers that make sense. 
 
If you want a total for the report, I would add another shared variable to update it either anytime you update totalJetFuel or just when you report on it in the group footer (fewer updates but probably doesn't matter as a performance issue.)
 
Hope this helps
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.031 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.