Crosstab - Data Display Problem 1

[govmule]

Hello,

I'm using Crystal Reports 8.5 and Crystal Enterprise 8.0 hitting a MSSQL 2005 database (I know, we're in the process of purchasing CR 2008).

I have a crosstab summerizing numerical data with a Performance Measure as the Row and the data date as the column. Here is an example:

7/10 8/10 9/10 10/10
System Up Time 98.8 96.7 99.8 99.4
Customer Satisfaction 4 4 4 4
Help Desk Response Time .99 .96 .99 .93

My problem is this - some of the measure are quarterly, semi-annual or annual. The measure doesn't even show up until there is data entered for these measures, like in December, for a semi-annual one. This makes the report look incomplete. Is there any way to trick it into displaying all measures each month and having the value be blank if there is no data?

I hope this makes sense. I'm certainly no expert at this.

Thank you in advance.

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of DATE set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that any selection on the second table must do an IsNull test first, otherwise you'll not get the dates with no records, null value for the second record.)

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP