Display rows with no data

[Cardstang]

I've tried using a crosstab, groupings, and running totals and I can't seem to get this right.

I'm using a formula to determine the time frame of when a record is created. I then want to show a summary of those time frames. Here is what I'm trying to get to:

Time Frame        # of Records
00:00 - 00:29          5
00:30 - 00:59          9
01:00 - 01:29          0
01:30 - 01:59          1
02:00 - 02:29          0
02:30 - 02:59          6

But my results are like this:

Time Frame        # of Records
00:00 - 00:29          5
00:30 - 00:59          9
01:30 - 01:59          1
02:30 - 02:59          6

I'd like to have the report show me a count of 0 even if there are no records to count in that time frame.

Any help or suggestions is greatly appreciated.

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of DATE/timeslots 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'. This 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. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.


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