Months as rows in a crosstab

[inspi]

I'm using CR 10 and SQL server.
I have a table with employee information- Employee name, address and EmployeeLeavedays
I want data in this format:
EA EB EC ED EE Total
Jan 1 2 0 0 3 6
Feb 0 0 0 0 0 0
Mar 0 0 1 1 0 2
Apr 0 0 0 0 0 0
May 1 1 1 1 1 5
Jun 0 1 2 1 0 4
Jul 0 0 0 0 1 1
Aug 0 3 0 3 1 7
Sep 0 0 0 0 0 0
Oct 2 2 1 2 1 8
Nov 0 5 4 0 0 9
Dec 2 2 1 1 2 8

Above crosstab would help to know the number of leave of absence for each employee in each month.

I tried to use in-built crosstab functionality and since no one took any leave in the months of Feb, Apr and Sep CR is deleting the 3 rows entirely and displayng the remaining rows.

I want to show all the months even though there is no data for those months.

Could anyone help me with this?

Thanks in advance.

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of month slots 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 LEFT OUTER does not work if you also do a selection on the table receiving the LEFT OUTER link.)

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.

Each running total will count the record if it was within the criteria - in your case, month.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10