Forcing column in cross-tab to appear even when there's no data for it

[Jniland]

Hi, I'm trying to make a cross-tab chart for a weekly help desk report. The rows are technician names, the columns are work order types. There are 5 work order types, but some weeks there are no tickets for one of the types. I'd like for the cross-tab to still show that type as an empty column rather than omitting it entirely. Is this possible?

I'm using Crystal 8.5, the database is SQL

Thanks!

 

[IanWaterman]

You can not do this you have to build a manual cross tab using formulae for each of your technicians.

Ian

 

[GJParker]

To include all of the types in a chart then they must appear in the crosstab. how is the information set out in the database If the work order types is in a different table to the metrics data then use a OUTER JOIN to include all values from teh work order type table.

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[Jniland]

Thanks for the replies

I think I may have miscommunicated, I said "crosstab chart" but I really just meant crosstab, I'm not making a seperate chart.

The information is all in one table, eg:
Technician, ElapsedMinutes, WorkOrderNumber, type, etc..
Bob, 784, 12345, Project, etc..
Bob, 12, 12346, Request, etc..


There is also another table that lists types/subtypes/subsubtypes, is there a way I can use that?

That table looks like:
Idnumber, label, parentId
1, Project, 0
2, Request, 0
3, Web Page enhancement, 1
4, Dirty Pictures, 3
5, Question, 0
etc..

 

[GJParker]

link the 2 tables together on Table1.Type to Table2.Label and change the join to LEFT OUTER JOIN from table2 to table 1

then replace table1.type field in your report with table2.label

HTH

Gary Parker
MIS Data Analyst
Manchester, England