Presenting in Calendar Format
The Problem
Over the past several years, we have had a number of customers inquire about a method to deliver real-time scheduling information in a common calendar format. Typically this requires the ability to schedule events around or related to other events, update dates in a separate application, and view the resulting calendar information immediately. The need for real-time information eliminates any possibility for caching or warehousing data which might be utilized to stage and provide performance improvements. Fortunately the amount of event data displayed on the calendar is often fairly small, and we can provide drill-down for specific events on the calendar, to more detailed information on lower-level reports. Additionally, other specific filters can be utilized to narrow the types of events to display within the calendar. This article describes one particular case of calendar reporting.
The Solution
To tackle this report, we divided the issues into two areas: the layout of the calendar itself, and the daily information within each date “cell”.
Setting the grid
The general approach to the calendar structure was simple, a 7 X 6 repeater grid allowing for 7 days per week, and a potential for up to 6 rows, in cases with a 31 day month beginning on a Saturday. The driving table for this query was a date dimension table (1 record per day) which included two month options, an actual month and a display month. The purpose of the two fields is to compensate for the first week of the month which mostly did not start on Sundays. If the month started on Wednesday for example, the Display Month included three days of the prior month to fill in the gap, thus ensuring that each week started on a Sunday. The filter included an OR condition to select either the actual or display months matching the prompted month value. There are various conditional formatting options available for those prior month days, such as shading the display background, to distinguish them from the actual selected month.
Additionally, we displayed the date value at the top of the cell in an attractive date format with shading, to clearly label the date cell.
Finally a toolbar at the top of the report displayed a 12 month bar, showing the 6 prior and 5 subsequent months relative to the selected month, to provide simple month navigation.
The main body of the final report. This version is filtered by one event type.
Close up of a date value, showing additional mouseover information. The events are color-coded to match the filters.
The list as it appears in Report Studio inside the repeater cell. HTML objects and javascript code were used extensively here, for mousover information and color coding.
Master Detail
Each day “cell” was constructed as a simple list, with a Master-Detail relationship to the parent date cell, which was linked by the date. An obvious concern in a case like this is that there can be a large set (potentially 31) of detail queries for each month. We tested a number of other options (such as conditionally formatting, and aligning an entire week) and found that the Master-Detail approach worked best for our intended use, given the environment and anticipated number of records. While not the snappiest of reports, it does perform reasonably well.
Here’s the Master-Detail connection between the date query and the list within the date cell. Additional filters still apply to the list to control types of events displayed.
Also, due to the small amount of space within a cell, we made good use of color to convey additional information (color coding event types) about the events. The cells are fixed size by width, and have a minimum size by height. If there are many events within a cell, the weekly row will expand to display all the events.
Environment/Caveats
In order for this report to work reasonable well, we often work with a DBA to optimize the data structures. Primarily, we have used the two-key approach to the date dimension table, and made sure that the event detail records were indexed by the date values used in the Master-Detail relationships.
