A Crystal Reports calendar for multi-day events

Tuesday 1 April 2014 @ 2:38 pm

Creating a calendar in Crystal Reports poses some special challenges. There may not be an event for every day or every week, so a method is needed for filling in the gaps. And there may be a long list of events on any given day which means finding a way to stretch the boxes. But a recent request form a customer forced me to find a completely different approach. They showed me a calendar for multi-day events that they were creating in a scheduling application. It looked like the (intentionally blurry) image below.


My first reaction was that this isn’t the kind of report that would work in CR.   There were 6 key challenges:

1) The colored box for each event stretches from the event start date to the event end date.
2) Events that cross weeks need a box for each week with the same text in each box.
3) The box must grow vertically so there is room for all the text of the box, which means short events will have taller boxes.
4) Events in the same week should make room for each other, although they can share the same row if they both fit.
5) The box color is defined by the type of event and can be specified by the user.
6) Part of the text in the box needs to be in bold.

But the customer was willing to invest some time and asked me to see how close I could come.  After a few hours, a couple of arrays, some looping logic, a quick review of twips, several fun date formulas and a cross-join …. I had it.  My initial POC uses the orders table from the Xtreme sample database and  I have posted the report on-line, along with two variations, for you to download.  The reports uses feeder formulas instead of referring directly to the database fields. This makes it relatively simple to point the tables/fields to your own database.

If you find them helpful, drop me a line.  Or, if you need help deploying a report, call to schedule a short consult.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro