Midnite_oracle
asked on
Creating a crosstab that displays empty rows and columns
Hi,
I am very new at crystal reports.
I have created a crosstab in crystal reports. I am trying to report on the severity of incidents occuring for each building between a certain time period.
I have used the section expert to set the date range (from the incident table).
I also use the section expert to say if the building name is xxx then only show the data for that building.
The cross tab SHOULD show the data as follows:
BUILDING xxx
jan06 feb06 mar06 apr06 may06
Insignificant 1 3 1 3 5
Low 4 3 1 2 4
Moderate 5 1 1 1 3
High 1 1 1 0 2
Extreme 0 0 0 0 0
but instead shows:
jan06 feb06 mar06 apr06 may06
Insignificant 1 3 1 3 5
Low 4 3 1 2 4
Moderate 5 1 1 1 3
High 1 1 1 0 2
ie it excludes the "extreme" row.
I am using 3 tables for this cross tab - tblIncidents, tblRatings, tblBuildings.
the tables are linked as follows:
tblIncidents.buildingref -> tblBuildings.buildingID
tblIncidents.ratingref -> tblRatings.RatingID
And i am using the following fields:
tblIncidents.ReportedbyDat eTime
tblBuildings.BuildingName
tblRating.RatingName
Is there a way to make the table show a row (in this case "Extreme") even if the row is empty?
And if there were no incidents for a single month, is there a way to display that month with a column of zeros?
and finally, once this has been done, how can i create a chart that graphs the zero rows/columns?
Thanks in advance and please let me know if you need any more information.
Thanks
Midnite..
I am very new at crystal reports.
I have created a crosstab in crystal reports. I am trying to report on the severity of incidents occuring for each building between a certain time period.
I have used the section expert to set the date range (from the incident table).
I also use the section expert to say if the building name is xxx then only show the data for that building.
The cross tab SHOULD show the data as follows:
BUILDING xxx
jan06 feb06 mar06 apr06 may06
Insignificant 1 3 1 3 5
Low 4 3 1 2 4
Moderate 5 1 1 1 3
High 1 1 1 0 2
Extreme 0 0 0 0 0
but instead shows:
jan06 feb06 mar06 apr06 may06
Insignificant 1 3 1 3 5
Low 4 3 1 2 4
Moderate 5 1 1 1 3
High 1 1 1 0 2
ie it excludes the "extreme" row.
I am using 3 tables for this cross tab - tblIncidents, tblRatings, tblBuildings.
the tables are linked as follows:
tblIncidents.buildingref -> tblBuildings.buildingID
tblIncidents.ratingref -> tblRatings.RatingID
And i am using the following fields:
tblIncidents.ReportedbyDat
tblBuildings.BuildingName
tblRating.RatingName
Is there a way to make the table show a row (in this case "Extreme") even if the row is empty?
And if there were no incidents for a single month, is there a way to display that month with a column of zeros?
and finally, once this has been done, how can i create a chart that graphs the zero rows/columns?
Thanks in advance and please let me know if you need any more information.
Thanks
Midnite..
ASKER
this may be a silly question, but when i type in the formula it says that a string is required here (highlights the 0)..
how do i get around that?
how do i get around that?
It means that the field you have in the formula is not a number, maybe a string or something of the sorts.
Try '0'.
Spykair.
Try '0'.
Spykair.
ASKER
ok i have tried this but its still not working.
It continues to exclude the extreme category.
COuld it be because there is no reference to the extreme category in the incidents for that building?
It continues to exclude the extreme category.
COuld it be because there is no reference to the extreme category in the incidents for that building?
Ah, yes. I think that's why. Sorry.
Spykair
Spykair
ASKER
Is there a way i can include a static row populated with zeros for "extreme" or for any rating that has a zero row?
Let me try a couple of things and I'll get back to you.
Spykair.
Spykair.
The only way, i know, is to ensure a row is included for each type for each building
Similarly ensure there is data for each month.
mlmcc
Similarly ensure there is data for each month.
mlmcc
ASKER
Is there a way to insert static row and column headings in a cross-tab?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glaad i could help
mlmcc
mlmcc
Try using a formula:
If {MyTable.MyField} <> 0 Then
{MyTable.MyField}
Else
0
Hth,
Spykair