12 month at-a-glance calendar

[WPSAnalyst]

I'm using Crystal Reports XI and have been asked to determine if it's possible to present data from our database in a one-page 12-month at-a-glance calendar. I've done a lot of searching and have been to Mike Lewis' site (article by Linda Bass), but all I can find is either bi-weekly or monthly calendars. The requirement is to present 12 months of sick time usage per employee on one page for managers to spot check trending "at-a-glance". The hope is that the calendar days when sick time was used would be turned to another colour (red). No other data is required. I'm not even sure if I should just be pulling the sick usage data from CR and then export it to Excel to further manipulate. Any help would be appreciated!

 

[NickieP]

I created a table in excel"ALLDATES", listed every date by putting earliest date in, adding 1 to row below.Takes seconds to create a decade.

Left outer join this xls table to other DB tables (from Access or ODBC) on the date field
Now all dates are in the report, even if no-one is sick that day

insert a group on ALLDATES.date, by year
in the header for year, create a cross tab
months as column headings
people/depts/row headings
result: number of sick days per person per month in a year, summed by dept.


OR

group on employee
month numbers as col headings
year as row heading
to see if Person A has 5 sick days off every August, year after year


result: number of sick days per person per month in a year, summed by dept.
use highlight on CT field to highlight if sum of sick days >0 with a solid red background


if it is slow when running, save table in an access database and swap the source to table in access