Technical Questions: Summing problem

Print Page | Close Window

Summing problem

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5833
Printed Date: 02 May 2024 at 4:27am

Topic: Summing problem

Posted By: grego
Subject: Summing problem
Date 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 mailto:%7b@Jet - {@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?

Replies:

Posted By: lockwelle
Date 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.

Posted By: grego
Date 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;

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

Posted By: JohnT
Date 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;

totalJetFuel := totalJetFuel + thisJet

Posted By: grego
Date 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

Posted By: lockwelle
Date 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.

Posted By: grego
Date 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;

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

Posted By: lockwelle
Date 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.

Posted By: grego
Date 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

Posted By: lockwelle
Date 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

Posted By: grego
Date Posted: 24 Mar 2009 at 1:49am

After checking the report I decided that the grouping by counterparty name was not needed so I deleted it. So I have kept the reset in the group header, the jet fuel formula in the details and the sum of jet fuel in the report footer.

You mentioned adding another shared variable - I'm not very good with these variable things and don't really understand how it all works I'm afraid! Where would I add this extra shared variable and what would it look like

Thanks for your patience and help on this!

Posted By: lockwelle
Date Posted: 24 Mar 2009 at 6:23am

The easiest way would be to add a another line in the formula in the detail section, say groupJetFuel, whatever you add to totalJetFuel add to groupJetFuel.

Change the reset formula to refer to groupJetFuel, create a new formula to display the groupJetFuel variable, and place it in the group footer.

Since you want to see totalJetFuel in the report footer, what happens if you reset the value to 0 in the group header and the variable is not updated in the details? The totalJetFuel as it stands right now, is constantly being reset to 0 and so is not the total of the report, just of the last group...which wouldn't seem correct for where it is placed. The changes described, allow the total by group to be displayed corrected and since the totalJetFuel := 0 is gone (replaced by groupJetFuel) the value at the end of the report is the totalJetFuel for all details on the report.

Does that make sense? Hopefully it does;)

Posted By: grego
Date Posted: 20 Apr 2009 at 2:21am

Thanks for your help on this - I've been away on holiday and am back in trying to sort this problem again! I think I must be missing something though!

I've added in the additional shared variable - so that the jet fuel formula now looks like this:

shared numbervar totalJetFuel;
shared numbervar GroupJetFuel;
local numbervar thisJet;

totalJetFuel := totalJetFuel + thisJet;
GroupJetFuel:= totaljetfuel + thisJet;

I have added a formula to the group footer that has the following in it:

shared numbervar GroupJetFuel;

I have changed the totaljetfuel reset formula in the group header to reference GroupJetFuel as follows:

shared numbervar GroupJetFuel := 0;
""

(NB there is now no reset for totalJetFuel)

The number that is returned in the group footer for the sharednumbervar GroupJetFuel isn't correct.

Against {CPARTY.CODE} = "BARC GP" then thisJet := -4.6 - this is the first counterparty alphabetically that has jet fuel - so I would expect the sharednumbervar GroupJetFuel to = -4.6 - it equals -1,504.2 and I can't figure out where this has come from!

Also if if I want to actually show the jetfuel total for each counterparty in the group footer I thought all I would need to do is add a formula with the following:

local numbervar thisJet;

However when I do this I just get a 0.00 value returned against all counterparties. I thought I might need to make the variable a shared one in the jetfuel formula to pick it up - but this didn't work either - do I need to explicitly state that I want the thisjet bit of the jetfuel formula? and if so how do I do this?!

thanks for continued help on this!

Posted By: lockwelle
Date Posted: 20 Apr 2009 at 6:23am

the formulas listed look right. rather than looking at the group footer, I would look at value of jet fuel formula in the detail, and at the same time the group footer of GroupJetFuel. By doing this, you can see what the values are being set to, and if they agree with what you expect.

The local variables, would have to be declared in that formula, the rest of Crystal can't see them. Since thisJet is local in the formula, it is 0 as a shared variable...you would need to set them as shared in both formulas

Print Page | Close Window