Create a calendar-style report

Need a report that looks like a calendar or month planner? Crystal can do it.

By Linda Bass

A question that often comes up in Crystal Reports forums and newsgroups is: How can I create a calendar-style report? Typically, the requirement is to create a report showing appointments or bookings on a day-by-day basis, just as they would appear in a calendar or month planner. Figure 1 shows a typical example. This has a square for each day, with each month on a separate page.

Figure 1: The report mimics a conventional calendar.

In this article, I'll explain how to create this type of report. I'll also give you a link for downloading a sample report that illustrates the technique.

The calendar table

In order to create this type of report, you will need to add a simple calendar table to your database. As a minimum, this table consists of one column, with a row for each day of the year to which the calendar will apply. The rows contain consecutive dates within the year.

If you are unable to add tables to your main database, don't worry. You can easily set up the table in Excel. To do so, enter the date of the first day of the year in cell A1. Then drag the fill handle (the small square in the bottom right corner of the cell) downwards until you have created a range of dates for the entire year. Figure 2 shows how this might look.

Figure 2: Creating the calendar table in Excel

The appointment table

You will also need a table containing the appointments, bookings or other data that you want to appear in the calendar. This table will need a date column, plus a column containing the text that you want to display in the calendar. There is no problem in having more than one appointment for a given day, provided there is enough space in the day square (in this article, I will refer to the data as appointments, but the technique will work equally well with room bookings, rental reservations, or anything similar.)

Start a new report

Having identified your tables, the next step is to start a new report. Add the date table to the report. Then proceed as follows:

1. Insert a group based on the date field. In the control labeled 'This section will be printed for', pick 'For each month'. Check 'Use customized group name based on a formula' and enter the following formula (substituting your own table and field name):

Monthname(Month({xl.date}))

2. Insert a new Page Header section, and drag the group name to the Page Header_a section. Open the Section Expert, select the Group Header #1 section, and check Suppress. Then select the Group Footer #1 section, and check New Page After. In the formula area, enter this formula:

Not OnLastRecord

3. Create a formula called {@Weeknumber}:

DatePart("ww",{xl.date})

4. Insert a new group (Group #2), based on the {@Weeknumber} formula. Open the Section Expert, select the Group Footer #2 section, and check Suppress.

5. Create seven formulas, one for each day of the week. Name them {@Sun}, {@Mon} and so forth. The {@Sun} formula should look like this:

if DayOfWeek({xl.date}) = 1 then Day({xl.date})

For the other six formulas, change the '1' to '2' (Monday), '3' (Tuesday), '4' (Wednesday), etc.

6. Add each of these seven formulas to the Details section. Arrange these in a horizontal row, starting with the formula for Sunday and continuing across the page in sequence.

Note: If you want another day to be the first of the week, you will need to add the FirstDayOfWeek argument wherever the DayOfWeek and DatePart functions are used in both the main and subreports.

7. For each of the formulas, insert a summary, based on the Maximum function. Drag the summaries to the Group Header #2 section, positioning them equidistant across the page. Drag the lower boundary of the header downward until it is the size that you want to allocate to the day square.

8. Suppress the Report Header, Details, Page Footer, and Report Footer sections.

9. To eliminate results of '0,' go to the Format Editor of each of the summaries in turn. On the Number page, choose Customize and check Suppress if Zero.

Create the grid

10. Next, place a box over Page Header_b and extend this downwards to the bottom of the Group Header #2 section. Then add vertical lines extending from the top of the box to the bottom to divide the days, and add a horizontal line along the top of Group Header #2 to divide the weeks.

11. For both the box and the vertical lines, open the Format Editor and check the box labeled 'Extend to Bottom of Section when Printing.'

12. Left-justify and reposition the summaries (in Group Header #2) so they are in the upper left corner of each day box, as you would normally see them in a calendar. In Page Header_b, position the column labels ('Sun', 'Mon', etc.) so that they are centered above each square.

Add the subreports

At this point, you should see an empty calendar, with one month per page. So far, the steps I have described have been completely generic. You can use them to create the basic calendar grid for a given year, regardless of the data that you want to appear within the calendar.

For the next steps, we will add the actual data. To do this, we need to create a series of subreports.

13. Create a subreport called 'Sun' (choose Insert / Subreport / Create a Subreport). Select the appointment table (or whatever other table(s) hold your data). In the sample report accompanying this article, I used the Orders, Order Details and Product tables from the Xtreme database.

14. Add the field(s) containing the descriptions that you want to appear in the calendar. You can add as many fields as you like here, provided you have enough space. For example, you might add one field containing a description of the appointment, and another holding the time when the appointment is to take place. Add these fields to the Details section of the subreport, and suppress all other sections.

15. Still in the subreport, create a formula called {@WeekNumber}:

DatePart("ww",{Appointments.Appointment Date})

16. In the record selection formula of the subreport, add the following:

Year({Appointments.Appointment Date}) = 2004 
and DayOfWeek({Appointments.Appointment Date}) = 1 
// 1 = Sunday

The year in the first line of this formula should match the year of the calendar.

17. Link the subreport to the main report using the {@Weeknumber} formula from each. Then place the subreport in the Sunday square of the Group Header #2, resizing it to fit.

18. In the main report, select the subreport and open the Format Editor. On the Common page, enter this formula against the Suppress checkbox:

Maximum({@Sun},{@Weeknumber}) = 0

This formula will prevent duplicate entries (belonging to other months) from appearing in the blank squares at the start and end of the month.

19. Repeat steps 13 to 18 for each of the other days of the week, taking care to substitute the correct day names and day numbers where appropriate.

20. Finally, make any cosmetic changes that are required. For example, you might add further horizontal or vertical lines or adjust the fonts or colors.

Download an example

The download files accompanying this article include an Excel spreadsheet containing the calendar table and a calendar report showing orders from the Xtreme database. Because these orders date back to 1997, that is the year for which I created the Excel calendar.

To download the files, click on the link below. Unzip the resulting files into a folder of your choice. Then just run the report. The total download size is 132 KB.

Download now

Linda Bass works in workforce development in Cambridge, MA, and is a regular contributor to Tek-Tips. If you have any feedback on this article or questions on the calendar report, please post them to the Crystal Reports 4 forum on Tek-Tips.

Mike Lewis Consultants Ltd. March 2004. 

More articles | Crystal Reports training | Crystal Report consultancy | Contact us

Articles, tips and FAQs for Crystal Reports
Brought to you by Mike Lewis Consultants Ltd

Home page

More articles

Crystal Reports training

Crystal Report consultancy

Contact us

Recommended reading on Crystal Reports

Our top recommendation:

Crystal Reports XI: The Complete Reference 
 by George Peck. 

Best bet for beginners:

Crystal Reports: A Beginner's Guide
by David G. McAmis

These articles are provided by Mike Lewis Consultants Ltd as a service to Crystal Reports users. Feel free to download and use any sample reports or other material, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).

The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.

© Copyright Mike Lewis Consultants Ltd