Creating an Event Calendar in Crystal Reports

Introduction

Our recent project involved developing a Crystal Report for use with the event management software, Momentus, aimed at displaying booked events within a specified date range in a monthly/yearly calendar format.

This report provides a clear overview of their availability at a glance. The following details the steps and considerations involved in achieving this dynamic solution.

Event calendar example

Requirements

The report needed to:

  • Display event context such as Budget Status (UDF) by text and Event Manager by colour.
  • Include shading for weekends, bank holidays, and dark days.
  • Feature a colour key for event managers and other event shading.
  • Allow filtering by Event Status and date range.
  • Represent both the open and build days of the event.
  • Make Event Manager colours configurable within Momentus.
  • Allow for overlapping events.

Approach

To build the calendar, we used two arrays:

  1. Numeric Array: This reserves slots in the calendar’s layout. Each event block is assigned to the array based on its duration, determining the next available cell.
  2. String Array: This stores event attributes such as display text (for example, Event Description, Budget Status, Project Manager), colour, event duration (block length), and modifiers for open days (offset and length).

Events are placed in the array based on their start position and length. To represent events spanning multiple days, the horizontal width of a day block, denoted in twips, is multiplied by the required number of days.

Handling Multi-Month Events

The event block length is determined by the Event In and Event Out dates. For events spanning multiple months, we handle this within the SQL by generating a row for each month.

For example, an event from June 27th to July 3rd becomes two rows: June 27th to 30th and July 1st to 3rd, allowing for correct array placement.

Representing Open Days

Open days, denoted by the Event Start and Event End dates, are represented as secondary event blocks beneath their primary blocks.

The length of the open day and a horizontal offset, calculated from its original position, are applied to the secondary event block.

Event Manager Colours

Event Managers can be assigned colours by populating an account user-defined field (UDF) with an RGB value, such as 255,0,0 for red.

This RGB value is passed to the report’s array and applied to associated event blocks.

Colour assignment follows these conditions:

  • Custom event manager colour if set.
  • Default event manager colour if no custom colour is set.
  • Colour based on Budget Status (UDF) if no event manager is assigned.
  • Default colour for events awaiting allocation if none of the above are applicable.

Generating the Colour Key

A subreport generates the colour key by fetching custom Event Manager values via SQL and including hardcoded values such as Dark Day and Awaiting Allocation.

Shading for Special Days

Underlaid shading in a subreport denotes weekends, bank holidays, and dark days.

This is achieved by checking each day against the master calendar in Momentus. Colours are stored in configurable formula fields for these special days.

Performance Considerations

Using arrays to populate the calendar allows us to fetch event data with a single SQL query, ensuring the report executes quickly, and avoiding the need for separate queries for each day of a month.

Display Configuration

The current setup displays five rows per month, allowing for a maximum of five overlapping events.

This fixed number of rows reflects the maximum overlapping events possible. Handling this dynamically is a potential future enhancement.

Conclusion

Creating a dynamic event chart in Crystal Reports involved detailed planning and precise implementation of arrays to manage event blocks and shading.

The resulting report provides a clear and customisable overview of availability, accommodating various event statuses and manager configurations.

Future iterations may enhance this dynamic capability to handle displaying other event information.

Download

Acknowledgements

This report is based on and inspired by the original design created by Ken Hamady. We extend our sincere gratitude to Ken for his innovative work and contributions to the field of Crystal Reports.