Earlier versions of Crystal Reports had the Arithmetic Mean and we have also had Median, Mode and Weighted Average available for a while now.

Tools for calculating means are in Crystal Reports, as are those for Calculating Averages in Crystal Reports.

But not all of the statistical tools you might need are in Crystal Reports. For example, how do you calculate the geometric mean?

This question arose on Tek-Tips. As this was a new concept to us, some research was required. Looks like they have been around for thousands of years as they are one of the three traditional Pythagorean Means (Arithmetic, Geometric, and Harmonic).

We all learn how to calculate an Arithmetic Mean (or traditional average). Add up the values for the field and divide by the number of values. In Crystal Reports, the Average does this for you but you could also calculate it using Count and Sum Functions.

A Geometric Mean is calculated by multiplying the numbers together and then taking the nth root of that product. So if there are only two values, then take the square root, or if there are four values take the 4th root.

A formula to calculate a Geometric mean could look like:

//{@reset} to be placed in the Report Header or Group Header section:
whileprintingrecords;
global numbervar x := 1;
global numbervar c := 0;
//{@accum} to be placed in the detail section:
whileprintingrecords;
global numbervar x := x * {table.qty};
global numbervar c := c + 1;
//{@result} to be placed in the Report Foorter or Group Footer section:
whileprintingrecords;
global numbervar x;
global numbervar c;
if c = 0 then 0 else x ^ (1/c)

You can use the formula above with {@reset} and {@result} in the Report Header and Footer, or in a matching pair of Group Header/Footer.

Make sure the Group is NOT set to “Repeat Header on New Page” as that will reset the counter and accumulator.

A Geometric Mean is calculated is particularly useful where the values follow an exponential series or there is some trend that means the values are accumulating or depreciating over a time series.

There is also the restriction that all the values must be above zero. A zero anywhere in the series would result in the accumulator being set to zero and all subsequent calculations would also stay at that value. A negative value would reverse the sign of the accumulator and while you can get the cube root of -64 you can’t get the square root of that number without invoking imaginary numbers.

At this stage we are not aware of how to calculate using imaginary values.

Another problem arises here, and you should be aware of it....

Numeric Overflow when calculating a Geometric Mean

If you have a large number of records or high field values in each group, you could end up with a numeric overflow. This means that the result of the multiplication inside the formula is too large to be stored in the binary registers inside the Crystal Reports application.

The solution to this is to rescale the field value. If the values are usually between 100 to 900 then divide the field value by 100 before you accumulate the multiple, and then multiply the value by 100 before you display the result.

The formulas then become:

//{@accum} to be placed in the detail section:
whileprintingrecords;
global numbervar x := x * {table.qty} / 100;
global numbervar c := c + 1;
Result formula in the group or report footer:
whileprintingrecords;
global numbervar x;
global numbervar c;
if c=0 then 0 else 100 * x ^ (1/c)

You could use any scale you like, but most would prefer 100, 1000 or some decimal power here.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.