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.
Requirements
The report needed to:
- Display event context such as
Budget Status
(UDF) by text andEvent 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:
- 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.
- 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.