Average of Average

[basingstokett]

I have a report that has a break. In the footer of the break I calculate an average. At the end of the report I have also want to insert an average which is an average of the averages. When I insert the final average the calculation is not based on the break averages but on the whole report at a line by line level.

How can I create an average of an average?

Example:

Team A Player 1 15
Player 2 10
Player 3 5
Ave 10

Team B Player 1 25
Player 2 40
Player 3 25
Ave 30

Overall Ave 20 i.e. 120/6

I want 20 based on (10+30)/2 i.e 2 teams


Not sure of the formula syntax to do this.
I realise in my example the result is the same but it isn't in my real report!

 

[blom0344]

The trouble is that you cannot use an aggregate function on the outcome of a calculation. Which is what you want in this particular case.

The solution is to use a second dataprovider which brings in the group-averages as dataprovider objects.

Like:

SELECT TEAM, AVG(<MEASURE>) FROM TABLE
GROUP BY TEAM

Now use the BO average function on these measures and display the outcome at the bottom of the report.

Ties Blom