Solved

Creating a crosstab that displays empty rows and columns

Posted on 2006-06-18
11
635 Views
Last Modified: 2008-02-01
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.ReportedbyDateTime
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..


0
Comment
Question by:Midnite_oracle
  • 4
  • 4
  • 3
11 Comments
 
LVL 10

Expert Comment

by:Spykair
ID: 16932050
>>Is there a way to make the table show a row (in this case "Extreme") even if the row is empty?
Try using a formula:
If {MyTable.MyField} <> 0 Then
  {MyTable.MyField}
Else
  0

Hth,
Spykair
0
 

Author Comment

by:Midnite_oracle
ID: 16932092
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?
0
 
LVL 10

Expert Comment

by:Spykair
ID: 16932137
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.
0
 

Author Comment

by:Midnite_oracle
ID: 16932233
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?
0
 
LVL 10

Expert Comment

by:Spykair
ID: 16932266
Ah, yes. I think that's why. Sorry.

Spykair
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Midnite_oracle
ID: 16932289
Is there a way i can include a static row populated with zeros for "extreme" or for any rating that has a zero row?
0
 
LVL 10

Expert Comment

by:Spykair
ID: 16932306
Let me try a couple of things and I'll get back to you.

Spykair.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16938891
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
0
 

Author Comment

by:Midnite_oracle
ID: 16989731
Is there a way to insert static row and column headings in a cross-tab?
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 16989799
Not that I am aware of.  A cross-tab is built from the data as it exists.

The only thing that might work is to create 2 tables.  One with all months and the other with all the types then cross join them.  Use the cross joined table as the master and left join it to the data where the month/year and the types are equal.  

That will ensure rows exist in the datasource for all months and types.  Not sure it is possible.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 17029463
Glaad i could help

mlmcc
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now