asked on

Crystal Reports - average of group total

I have a report with a total in GF#3 which is a forumula: ({#Original Const - Close}-{#Origininal Const - Start})+1

This formula reports a total for each separate job in a project. In this example, lets say there are 4 jobs. GF#3 is displayed 4 times with totals (100, 85, 95, 85).

I would like to take the average of those totals in GF#3 and display them in GF#1, then again in the Report Footer as company averages. Since they are formulas, I am unable to use them in a running total formula.

I had a similar issue where I used the following solution, but I have not been able to figure out a variation of this solution that works for my current issue.

This solution is from a similar question I posted:
In the report header add a formula to decalre the grand total variable
WhilePrintingRecords;
Global NumberVar GrandTotal;
""

You can modify your formula 2 to calculate the grand total
WhilePrintingRecords;
Global NumberVar GrandTotal;
Local NumberVar CurrentTotal;
Your formula 2

CurrentTotal := Your last line
GrandTotal := GrandTotal + CurrentTotal;
CurrentTotal

In the report footer add a formula to display the grand total
WhilePrintingRecords;
Global NumberVar GrandTotal;
GrandTotal

Mike McCracken

Try this

In the report header add a formula to decalre the grand total variable
WhilePrintingRecords;
Global NumberVar GrandTotal;
Global NumberVar Count;
""

You can modify your formula 2 to calculate the grand total
WhilePrintingRecords;
Global NumberVar GrandTotal;
Local NumberVar CurrentTotal;
Global NumberVar Count;

Your formula 2

CurrentTotal := Your last line
GrandTotal := GrandTotal + CurrentTotal;
Count := Count + 1;
CurrentTotal

In the report footer add a formula to display the average
WhilePrintingRecords;
Global NumberVar GrandTotal;
Global NumberVar Count;

GrandTotal / Count

mlmcc

GStoner

ASKER

Close. Your solution displays a Grand Total average in the report footer of the average of what is displayed in GF#1. The problem is that the number being displayed in GF#1 is not an average of the GF#3 numbers. It's simply displaying the last GF#3 number.

Mike McCracken

Are you resetting the totals anywhere in the report?

Can you upload the rpt file?

mlmcc

GStoner

ASKER

I've attached the rpt file as well as a details document that explains what I'm trying to do.
Details.pdf
Job-Schedule---Construction-Perf.rpt

Mike McCracken

You didn't modify the original calculation formula. You just created a formula to do the totaling but you put it in the group 1 footer. It should be in the section with the calculation thus the reason I said to change your formula

Do you need the average for each group and the overall report average?

I revised the report with the changes highlighted in RED

mlmcc
Job-Schedule---Construction-Perf.rpt

GStoner

ASKER

You are the man. Yes, I'd like the overall average as well, so how do I go about doing that?
Thanks for the revised report - it will help me learn.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GStoner

ASKER

Excellent work. This is the second time mlmcc has helped me out.
Thank you very much.

GStoner

ASKER

One more question on this. If I want to do the same thing with the the Actual Duration and the Duration Variance Columns, I'm assuming I can just create new formulas for those columns:

WhilePrintingRecords;
Global NumberVar GrandTotal2;
Global NumberVar Count;
GrandTotal := 0;
Count := 0;
""
---------------------------------
WhilePrintingRecords;
Global NumberVar GrandTotal2;
Local NumberVar CurrentTotal2;
Global NumberVar Count;

CurrentTotal2 := (replace this with formula for column 2) + 1;

GrandTotal2 := GrandTotal2 + CurrentTotal2;
Count := Count + 1;
CurrentTotal2;

----------------------------------
WhilePrintingRecords;
Global NumberVar GrandTotal2;
Global NumberVar Count;

GrandTotal2 / Count
---------------------------------

When I add the second formula, in GF#1, the values are wrong in the new formula, as well as the first formula in GF#1. I'm assuming it's because of the Global NumberVar Count or something else with the count, but it's not simply doubling the count. Do I have to do something different with the count to specify the second column, Actual Duration?

James0628

You're right, the count is a problem. It may not be the only problem, but it is a problem.

If you're actually counting the same things in the new formulas (so the final count should be the same as the one from the first formulas), then you don't need to include Count in the new formulas, except for the last one, where you use it to calculate the average. Then it will have the count from the first formulas.

If you need a different count, or maybe you're using it at a different point in the report, just change the Count variable in _all_ of the new formulas to some other name, like Count2, so the new formulas create a separate count, and the count in the first formulas won't be affected. It's exactly the same thing you did when you changed GrandTotal to GrandTotal2.

James

Mike McCracken

If they are going in the same sections you can actually do all the totaling in the same formula

mlmcc