Average in group footer 1

[FionaCondon]

I have a report grouped by school (CR 8.5). The group footer which shows:
@OnRollDate1
sum({@CountOnRollFirstDate},{tblSCHOOL.SCH_NAME})
- @CountOnRollFirstDate is 1 or 0 for each pupil depending on whether they were on roll at the time

NetCapacity
a database field

@Surplus
{tblSCHOOL.NetCapacity} - {@OnRollDate1}

@PercentageSurplus
({@Surplus}/{tblSCHOOL.NetCapacity})*100

I need to show the average percentage for all schools in the footer for each school. I have tried using:
average({@PercentageSurplus})
but I get the message 'The summary / running total field could not be created'. Is this because I am putting it in the group footer and the PercentageSurplus fields for all the schools haven't yet been calculated, in which case what should I do? Or is it something else?

Fiona

 

[bdreed35]

To put it simply, you can't sum a sum.

It can try putting all the logic into your @PercentageSurplus formula like this:

(({tblSCHOOL.NetCapacity}-(sum({@CountOnRollFirstDate},{tblSCHOOL.SCH_NAME})))/{tblSCHOOL.NetCapacity})*100

Its not as convenient to maintain but it will solve your problem.

~Brian

 

[FionaCondon]

Thanks Brian, but I can't quite see how this helps. Am I missing something? I would still have to average the sum which, from your reply, is what I can't do.
Fiona

 

[lbass]

You could try the method as outlined in thread149-793980, substituting your formula for the minimum formula in that thread. In addition to those two formulas, you would need to add a reset formula to be placed in the group header:

whileprintingrecords;
numbervar min := 0;
numbervar counter := 0;
numbervar ave := 0;

-LB

 

[bdreed35]

Did lbass's solution get you what you needed?

To clarify my point, if you have a formula that is already performing a summary function, you cannot create another summary off of that.

You can however use multiple summaries in the same formula since it is all being executed at the same time.

~Brian

 

[FionaCondon]

Well I've got a little closer.

Using lbass's suggestion, the first formula averages the surpluses for all the schools printed so far, but what I need is the average of all the schools in each group footer. I'm not sure which variable it ends up displaying.

The second formula gives me zero every time. I'm not sure how the ave variable gets out of the first formula and into the second, if you see what I mean. I have tried putting shared numbervar in it but that didn't help.

Thanks
Fiona

 

[lbass]

I don't remember why I thought you needed a reset formula. Please try the following which works when tested here:

First save your report and then create two formulas:

//{@accum} to be placed in the group footer:
whileprintingrecords;
numbervar sumsum := sumsum + {@PercentageSurplus};
numbervar counter := counter + 1;
numbervar ave := sumsum/counter;

//{@ave%surplus} to be placed in the report footer:
whileprintingrecords;
numbervar ave;

Suppress all sections of the report except the report footer.

Now save the report under a new name and close it.

Reopen the earlier version of the report and go to insert->subreport->choose a report->select the new version of the report which contains the average value. Place this in the group footer of your original report. This will display the report average for %surplus in each group footer.

-LB

 

[FionaCondon]

Thanks - I got there!