We help IT Professionals succeed at work.

Crystal Reports 2008 - Subtotal the Sum of an Average

bruno71
bruno71 asked
on
I have a report for machine downtime in our company.  On the details line is a database field with an integer for # of minutes.  My Group # 3 is by Shift.  Group # 2 is by Date. Group # 1 is by Machine.

So far, I can insert a summary into Group # 3 Footer to get an Average of the # minutes for the Shift.  Now I want to add those averages together for each Shift to get a sum total number of minutes for the Date.  And then I will add up the daily totals for a total number of minutes for each machine.  And then a grand total for the report.  However, I can't seem to create a sum total of the average.  It will only let me summarize the original database field.

Here is an example of what I'm looking for...

Detail - 480
Detail - 510
Detail - 480
GF3 -  Shift 1 - 490
Detail - 480
Detail - 480
Detail - 480
GF3 - Shift 2 - 480
GF2 - 6/9/10 - 970
...
...
GF2 - 6/10/10 - 985
GF1 - Machine # 3 - 1955
...
...
Report Footer - Grand Total - 6250


How can I trick CR2008 into giving me a total of an average?  
Thanks.

~bruno71

Comment
Watch Question

You don't need to add averages, you can just create average summaries at each group level.  Use a different summary for sum of minutes.  
Averaging before accumulating reduces accuracy anyhow so it is not a good idea.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
In Crystal, summary values cannot be summarized.

I agree, you really just need to add the average of the field in each group.

mlmcc

Author

Commented:
Here's the thing...
Ideally, each detail record should be the same in each shift (ie. they should all be 480).  However, I'm trying to account for users not entering the data consistently.  Like in the example, some entered 480...some entered 510.  I want to take the average to account for inconsistencies....the average for the shift is 490.  Then add up each shift for the day...two shifts > 480+490 = 970.  Then add up each day for the machine.

I can't just add the average to each group footer because that would not give me the sum total.  Each group would have an average around 480...not like the example with an average of 490 for the shift, and a total of 970 for the day...total of 1955 for the machine.  I need a sum total for each group based on the average of the details.

~bruno71

Add average summaries AND SUM summaries.  You're not limited to one summary per field.

Author

Commented:
I know...but I want a sum of the average.  Group Footer 3 should have an average.  GF2, GF1, and the Report Footer should have a sum of the average in GF3.

In the example above...
GF3 - Shift 1 Average is 490...sum would be 1470
...
GF2 - 6/9/10 Sum of the Averages is 970...sum would be 2910...avgerage would be 485

~bruno71

Will you try something?
Add an Average summary and a Sum summary to all group levels and report back with the values you get in each group footer.  

Author

Commented:
Here is a small sample from the report.  I added the summaries for sum, average, and what I need.

Detail - 480
Detail - 480
Detail - 480
[GF3] Shift 1 - sum=1440  avg=480  need=480

[GF2] 6/8/2010 - sum=1440  avg=480  need=480

Detail - 480
Detail - 600
Detail - 600
Detail - 480
Detail - 480
Detail - 540
[GF3] Shift 1 - sum=3180  avg=530  need=530

Detail - 420
Detail - 420
Detail - 420
[GF3] Shift 2 - sum=1260  avg=420  need=420

[GF2] 6/9/2010 - sum=4440  avg=493  need=950(530+420)

Detail - 480
Detail - 480
[GF3] Shift 1 - sum=960  avg=480  need=480

[GF2] 6/10/2010 - sum=960  avg=480  need=480

[GF1] Machine 1 - sum=6840  avg=489  need=1910(480+950+480)



~bruno71
[GF2] 6/9/2010 - sum=4440  avg=493  need=950(530+420)
This is where accumulating averages decreases accuracy.
The 9 totals for 6/9/2010 average 493 but when you average them before adding, you get an average of 475.  Isn't that a problem to lose 18 minutes?
There are twice as many records for shift 1 but you are treating it as equal to the 3 records for shift 2 because of the average.  
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The averages being calculated are accurate.  How can an average be larger than any of the values used?  That isn't an average.

In the report header add a formula

WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Global NumberVar Group2TotalAverage;
Global NumberVar TotalAverage;
''

In each group header add a formula that resets the group total average to 0
For instance in the GH1
WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Group1TotalAverage := 0;
''

In each group footer add a formula to accumulate the averages for the next group up
For instance in GF3
WhilePrintingRecords;
Global NumberVar Group2TotalAverage;
 Group2TotalAverage := Group2TotalAverage + Average({YourField},{Group3Field});
''

In the group footers add a formula to display the average for that group
For example in group footer 2
WhilePrintingRecords;
Global NumberVar Group2TotalAverage;
Group2TotalAverage

Similarly in each header and footer and the report footer

mlmcc

Author

Commented:
The numbers I'm showing are for "Scheduled Uptime" (ie. the length of the shift).  Shift 1 might be 8 hours and shift 2 could be 10 hours.  Ideally, the entries should be all the same for each shift (all 480, or all 600, etc.)  The entries are from each work order that they complete...some machines will complete more work orders in a shift than others.

~bruno71

Author

Commented:
Thanks mlmcc.  We're getting closer.  With your formulas, here is what I get...

Detail - 480
Detail - 480
Detail - 480
[GF3] Shift 1 - formula=0 (ok...I can add an avg summary to display here)

[GF2] 6/8/2010 - formula=480 (correct!)

Detail - 480
Detail - 600
Detail - 600
Detail - 480
Detail - 480
Detail - 540
[GF3] Shift 1 - formula=0

Detail - 420
Detail - 420
Detail - 420
[GF3] Shift 2 - formula=0

[GF2] 6/9/2010 - formula=950 (correct!)

Detail - 480
Detail - 480
[GF3] Shift 1 - formula=0

[GF2] 6/10/2010 - formula=480 (correct!)

[GF1] Machine 1 - formula=1453 (what?!?...should be 1910)


It doesn't look like the Machine total averages are adding up... and the grand total average does not add up to the total machine averages.

~bruno71
Grand total average will not match total of machine averages.  That's how math works.  That is why you don't add averages.

Author

Commented:
I don't want a Grand Total Average...

I want a  Sum Total of the Averages.

The only "average" should be in GF3 - Shift.  Every other footer should be a sum total of those numbers.

~bruno71
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you show the formulas you added?
Also where you put them

mlmcc

Author

Commented:
Sorry I was out for the past week. Here is what I've got...

Report Header:
WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Global NumberVar Group2TotalAverage;
Global NumberVar Group3TotalAverage;
Global NumberVar TotalAverage;


Group Header 1 (machine):
WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Group1TotalAverage := 0;


Group Header 2 (date):
WhilePrintingRecords;
Global NumberVar Group2TotalAverage;
Group2TotalAverage := 0;


Group Header 3 (shift):
WhilePrintingRecords;
Global NumberVar Group3TotalAverage;
Group3TotalAverage := 0;


Details:
{SF_DOWNTIME.scheduled_uptime}


Group Footer 3 (shift):
WhilePrintingRecords;
Global NumberVar Group2TotalAverage;
Group2TotalAverage := Group2TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.shift});
-------------------------------------
WhilePrintingRecords;
Global NumberVar Group3TotalAverage;
Group3TotalAverage


Group Footer 2 (date):
WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Group1TotalAverage := Group1TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.date});
-------------------------------------
WhilePrintingRecords;
Global NumberVar Group2TotalAverage;
Group2TotalAverage



Group Footer 1 (machine):
WhilePrintingRecords;
Global NumberVar TotalAverage;
TotalAverage := TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.machine});
-------------------------------------
WhilePrintingRecords;
Global NumberVar Group1TotalAverage;
Group1TotalAverage



Report Footer:
WhilePrintingRecords;
Global NumberVar TotalAverage;
TotalAverage
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The group 3 footer average should be the average summary not the variable. You don't need the group 3 total since there are no inner groups.

Use a formula like
Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.machine})

mlmcc

Author

Commented:
OK...I changed the GF3 formula and we're getting closer.  All the GF3 and GF2 "average totals" are correct.  However, the GF1 totals by machine are only correct if there is only 1 shift...example:

Shift 1 - 420
6/14/10 - 420

Shift 1 - 600
6/15/10 - 600

Shift 1 - 600
6/16/10 - 600

Shift 1 - 600
6/17/10 - 600

Machine 1 - 2220  (correct!)

Shift 1 - 564
Shift 2 - 600
6/14/10 - 1164

Shift 1 - 585
Shift 2 - 600
6/15/10 - 1185

Shift 1 - 510
Shift 2 - 600
6/16/10 - 1110

Shift 1 - 560
6/17/10 - 560

Machine 2 - 2253.86  (???)


I still haven't figured out what it's trying to do.
~bruno71
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report?  No data is needed but it would be helpful if it isn't a security/company issue

mlmcc

Author

Commented:
Here is the report...It is Crystal Reports 2008
Company-Downtime.rpt
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Since I have an older version of Crystal the data is discarded since a formula doesn't compile.  Not sure which one.  I checked all the formulas and they don't get errors.

Try this

Unsurpress the group footer formulas
Change them to be
Group Footer 1 (machine):
WhilePrintingRecords;
Global NumberVar TotalAverage;
TotalAverage := TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.machine});
''

mlmcc

Author

Commented:
Alright...I finally got it!  

I changed the GF2 formula from...
Group1TotalAverage := Group1TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.date});
...to...
Group1TotalAverage := Group1TotalAverage + {@Group2TotalAvg}

And I changed the GF3 formula from...
TotalAverage := TotalAverage + Average({SF_DOWNTIME.scheduled_uptime},{SF_DOWNTIME.machine});
...to...
TotalAverage := TotalAverage + {@Group1TotalAvg}

...that corrected the Machine totals and the Grand Total.  Thanks for all your help.  Now I can successfully get a sum of the averages :-)

~bruno71