Crystal Reports 2008 - Subtotal the Sum of an Average

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.

In Crystal, summary values cannot be summarized.

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

mlmcc

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.

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.  

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.  

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

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.

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

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

mlmcc

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

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

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

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

mlmcc

Here is the report...It is Crystal Reports 2008
Company-Downtime.rpt

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