[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Calculate a Sum for Report Footer

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
mattkovo
Asked:
mattkovo
1 Solution
 
dan_nealCommented:
Data source?  Staight table links or view or command?
0
 
mattkovoAuthor Commented:
dan neal: I don't understand your question. I am using straight table links I assume.
0
 
dcurry22Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mattkovoAuthor Commented:
dcurry....I'm running Crystal 2008

I get a really low number when I try that...
0
 
dcurry22Commented:
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
 
mattkovoAuthor Commented:
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
 
dcurry22Commented:
Right click on your running total and and click on Edit Running Total (RT)
Tell me the Evaluate setting and Reset option setting.  
From your description your evaluate option should be For each record
The Reset option should be Never.
Your RT should be in the Report footer.
0
 
mattkovoAuthor Commented:
Evaluate: Use a Formula - {REPORT_GROUP_SUMMARY.ID} = 1
Reset Option: Never

I do have the RT in the Report Footer.
0
 
dcurry22Commented:
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
 
mattkovoAuthor Commented:
I tried your second scenario but no luck.  what do you mean by "    !=0      "  ???
0
 
dcurry22Commented:
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
 
mattkovoAuthor Commented:
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
 
dcurry22Commented:
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
 
mattkovoAuthor Commented:
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
 
James0628Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now