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
etc..
Any help is greatly appreciated
crosstab-Example.bmp
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
etc..
Any help is greatly appreciated
crosstab-Example.bmp
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Does this happen often?
You might have to use a manual crosstab
mlmcc