Link to home
Start Free TrialLog in
Avatar of DarthCook

asked on

Calculate average in cross tab even if column is zero

A have a database which records incidents on three sites, i want to be able to display a cross tab in crystal reports XI showing the  total number of incidents by site grouped by month and the average. Only problem is the average column doesn't calculate properly here is an example of my cross tab with the summarised columns.

See example of cross tab attatched
This is what I get when looking at 3 sites over a 2 month period, in December 07 the average should be 5 ( total of 15 / 3 sites) but as site B didn't have any incidents in December it looks like the report isn't actually counting site b to calculate the average I'm not sure if I can show this using a cross tab?

My database query returns the information for the report in the following format.

ID        Date              Site
1        01/12/2007    site A
2        03/12/2007    Site B


Any help is greatly appreciated

Avatar of Mike McCracken
Mike McCracken

That is the way Crystal calculates an average since to it it only had 2 sites in Dec 07.

Does this happen often?

You might have to use a manual crosstab

Avatar of DarthCook


Yeah it happens quite often when you start looking at lots of sites over a longer time periods, I did think that maybe it would require going down the manual route but was hoping there may be some way to get round it and preserve the formatting of my reports?

Avatar of Mike McCracken
Mike McCracken

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial