Minimum/Maximum/Median/Average Count

[cswords]

This is probably simple to do, but I'm having problems!

I have a report which displays the number of detail records in the group footer (using a running total). Now, in the report header, I want to display the overall minimum / maximum / median and average count of details records.

For example, assume I have 3 records in the first group, 12 in the second and 9 in the third.

I want to display the minimum count (3), maximum count (12), median count (9) and average count (8).

Any suggestions?

 

[lbass]

One approach is to create a SQL expression like {%count}:

(select count(AKA.`groupfield`) from Table AKA where
AKA.`groupfield` = Table.`groupfield`)

You would replace "Table" with your table name and "groupfield" with the field name you are grouping on.
Leave "AKA" as is, since it is an alias table name. If you have other record selection criteria, these might need to be built into the expression also.

Place only your "group" field and {%count} in the detail section of your report. Format the report to "select distinct records" by going to database->select distinct records. This will cause the count summary to appear only once per field you would ordinarily be grouping on. Do not actually insert a group on this field. Now you can either right click on {%count} and insert grand totals (minimum, maximum, median, average) to be displayed in the report footer, or create formulas like:

average({%count})

...to be displayed in the report header.

-LB

 

[Madawc]

Summary totals offer some of the functions you mentioned, and can be put in the header, because they derive directly from the data.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total. Or else use the Field Explorer, the icon that is a grid-like box.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Grand totals are much like summary totals, but for the whole report rather than groups. Note that summary totals include an option to have a grand total calculated on the same basis.


Madawc Williams (East Anglia, UK)