Solved

# Calculate a Sum for Report Footer

Posted on 2009-04-20
760 Views
I am trying to calcuate the sum of {REPORT_GROUP_SUMMARY.AMOUNT} where {REPORT_GROUP_SUMMARY.ID} = 1. I am then trying to calculate the average so I can do some conditional formatting for the font color.

Now here is my problem: I can't use the "Insert > Summary" option and put it on the Report Footer because I can't supply a "where {REPORT_GROUP_SUMMARY.ID} = 1" clause.  I can't use a Running Total because it is just that..a running total so the Average {REPORT_GROUP_SUMMARY.AMOUNT}  is different depending which row you look at.

Any ideas? I need a static Average so I can make the font a different color.  Getting a straight up average that I put on the bottom of the report is no problem.
0
Question by:mattkovo

LVL 9

Expert Comment

Data source?  Staight table links or view or command?
0

Author Comment

dan neal: I don't understand your question. I am using straight table links I assume.
0

LVL 1

Expert Comment

Can you try the following steps and see if they  work for you?  You can try this on both Summary and Running Total fields.
1) Insert a Summary field and select Average
2) Right Click on the Summary Field
3) Click on Select Expert
4) Click on Desired Field for where clause REPORT_GROUP_SUMMARY.ID  and click OK
5) Click on the dropdown and select is equal to and type in  1
and click OK
0

Author Comment

dcurry....I'm running Crystal 2008

I get a really low number when I try that...
0

LVL 1

Expert Comment

I'm running CR 2005.
You didn't say if the low  number was right or wrong.  Being that you are getting a result is a positive. You may have to tweak this, move it to a different footer or section.  If you used Running total Review the reset options, etc...
I'm assuming you have already added up the ID = 1 numbers with a calculator and average them to see what you get?  Could it be a formatting display issue?
0

Author Comment

The number was way low.  I tried using the Running Total but the running total changes after each change of the group.  I need use and compare the final total to determine the true average for the date range I'm using...
0

LVL 1

Expert Comment

Right click on your running total and and click on Edit Running Total (RT)
Tell me the Evaluate setting and Reset option setting.
The Reset option should be Never.
Your RT should be in the Report footer.
0

Author Comment

Evaluate: Use a Formula - {REPORT_GROUP_SUMMARY.ID} = 1
Reset Option: Never

I do have the RT in the Report Footer.
0

LVL 1

Accepted Solution

I have Evaluate set to never and used the Select Expert referenced above.
I ran your scenario using field one = 1.0 and averaging a field two.  I got a low number also.
I then had to add to my formula where second field  != 0 as that affects the average.
I did this using both summary and running total.  My only other suggestion is to break this into
three parts Part I.  Get your total Amount Part 2;  Get your Record count Part 3 summary field and perform the averagingusing part I and II
Other than these change, mine scenario works.  Maybe I'm understanding the initial problem.
0

Author Comment

I tried your second scenario but no luck.  what do you mean by "    !=0      "  ???
0

LVL 1

Expert Comment

You don't want to include records in your average calculation  that do not have a value, are zero, null or empty string.  Including these "empty" value records directly affects the average computation, making it smaller.
Ex.  3. + 3. + 4  = 10 / 3 = 3.33
3. + 3. + 4. + 0 = 10/4 = 2.5
0

Author Comment

I guess I am not explaining myself properly.  Here is my scenario.  Thanks for sticking with me.
Date         Amount    Average (Needs to display Avg from Report Footer - Not running Avg)
4/1/2009      72.91       72.91
4/2/2009      65.42       69.17
4/3/2009    101.37       79.90
4/4/2009      78.90       79.65
4/5/2009      89.39       81.60
4/6/2009      58.43       77.74
4/7/2009      55.93       74.62

Total          522.35
Avg             74.62
0

LVL 1

Expert Comment

Q1. Tell me what you are using a Summary field or a Running Total?
Q2.  In the calculate portion of your summary are you telling the report to Sum or Average?
Q3. Is the Average column above displaying and you don't want it to, or is
your issue only the Avg field in the footer?  How is the Average column being generated SQL or a report field?  Do you want it to display?  Your summary field should be on Amount correct?

This is generally very straight forward, I can't figure out where your error might be.

0

Author Comment

Q1. Running Total
Q2. Average
Q3. Each row is a group. I'm only displaying the Average column to help describe the problem. The Average needs to be 74.62 all the way down. If I can figure out how to do that then I'm all set. I've tried generating the Average by Running Total and by Formula with a Summary. Summary field should be on Amount.
0

LVL 34

Expert Comment

I've kind of skimmed through the messages here, but putting those aside and going back to your original post.

I think you want the total of AMOUNT from the records where ID = 1, divided by the number of records where ID = 1.  Correct?

Create a new formula (in the "Field Explorer", right-click on "Formula Fields" and select New), call it ID1_total, and put the following in it:

if {REPORT_GROUP_SUMMARY.ID} = 1 then
{REPORT_GROUP_SUMMARY.AMOUNT}
else
0

Create a new formula like the following and call it ID1_count:

if {REPORT_GROUP_SUMMARY.ID} = 1 then
1
else
0

Create a new formula like the following and call it ID1_average:

if Sum ({@ID1_count}) <> 0 then
Sum ({@ID1_total}) / Sum ({@ID1_count})

Put ID1_average in your group footer and see what that gives you.

ID1_total only outputs AMOUNT when ID = 1, so a total of that is a total of just those amounts.
ID1_count only outputs 1 when ID = 1, so a total of that gives us a count of those records.
ID1_average uses the totals of those two formulas to calculate the average.

James
0

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!