mattkovo
asked on
Calculate a Sum for Report Footer
I am trying to calcuate the sum of {REPORT_GROUP_SUMMARY.AMOU
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.AMOU
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.
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.AMOU
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.
dan_neal
Data source? Staight table links or view or command?
ASKER
dan neal: I don't understand your question. I am using straight table links I assume.
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
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
ASKER
dcurry....I'm running Crystal 2008
I get a really low number when I try that...
I get a really low number when I try that...
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?
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?
ASKER
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...
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.
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.
ASKER
Evaluate: Use a Formula - {REPORT_GROUP_SUMMARY.ID} = 1
Reset Option: Never
I do have the RT in the Report Footer.
Reset Option: Never
I do have the RT in the Report Footer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried your second scenario but no luck. what do you mean by " !=0 " ???
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
Ex. 3. + 3. + 4 = 10 / 3 = 3.33
3. + 3. + 4. + 0 = 10/4 = 2.5
ASKER
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
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
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.
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.
ASKER
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.
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.
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.AMOU
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
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.AMOU
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