Report Footer Average...

[RichCha]

Crystal Reports - Version 10.

Hello everyone... I need to create a Average Formula that will display my results in the report footer. I have two fields {DISTRICT_STATE} and {DISTRICT_CITY}. I'm grouping by {DISTRICT_STATE} and then listing the cities {DISTRICT_CITY}. I've created a running total that counts the total number of cities per state in the group footer. I need to find the average number of each group footer... see example below, I need the total average for 4, 2, 2.

State: Texas
Dallas
Houston
Austin
Ft. Worth
Totals: 4

State: California
San Fran.
Los Angles
Totals: 2

State: Florida
Tampa
Miami
Totals: 2

Report Footer: Average (should be 3)


Thanks...

 

[synapsevampire]

Average would be 2.66666.

To get the average, use a formula in the group footer:

whileprintingrecords;
numbervar Cities;
Cities:=Cities+
distinctcount({table.city},{table.state});
distinctcount({table.city},{table.state})

To get the average in the report footer use:

whileprintingrecords;
numbervar Cities;
Cities/distinctcount({table.State})

-k

-k

 

[TripperDay]

I think the average should be 2.67.

I'd do it with a formula dividing the count (or distinct count) of the of the cities by a distinct count of the states.

 

[TripperDay]

Not trying to improve on synapsevampire's suggestion, just a simultaneous post.

 

[synapsevampire]

Tripper: Since they wanted the avaerage of the cities for each group, a distinctcount of all the cities isn't the same thing (per your suggestion), but you may be correct in that they don't know this and asked for the wrong thing.

-k