Sometimes it is easier to visualize the activity related information in a calendar view. Here are a few scenarios where Calendar style report provides a better view of information than a standard table or list.
- number of outbound telephone calls by salesperson or department in a telemarketing company
- billable and non-billable hours by consultant/department in a consulting company
- Internet sales on the e-Commerce web site
You can create a calendar style report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.
In this article, I will explain the steps required to create such a report from AdventureWorksDW database (available for download on Codeplex). I have also provided a link to download the report definition (RDL) file below.
Here are the key points to note for creating a calendar style report using SSRS.
1. Dates or Calendar table: We will use this table to include date, week number and day of the week information in the data set. This table contains all dates in each calendar year you will be reporting for. The AdventureWorksDW has the DimDate table that contains this information. If you do not have such as table in your database, you can create a temp table and use it in the query.
2. Outer Join: We will need to use the Outer Join between the dates table and the fact table so that all dates are included in the data set whether there is data or activity for that date or not.
3. Week Number: We will need this field to generate the matrix. This field has erroneous data in the AdventureWorksDW database. Therefore, I had to create a calculated field to calculate this value. Please see below for details.
4. Use Matrix to create the Calendar view. The Matrix has the WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. These fields are used only for grouping and should be hidden in the final report design. The WeekNumber can be any number from 1 through 53. The DayNumberOfWeek can be any number from 1 through 7 – 1 being Sunday and 7 Saturday.
5. The matrix can have from 4 to 6 rows depending on the number of weeks in the month and the day on which the month starts. It always has 7 columns – one for each day of the week.
The screen shot below shows the query used for the data set used in the matrix. Note the LEFT OUTER JOIN between DimDate and FactInternetSales tables.
As mentioned earlier, the week number field in the DimDate table in AdventureWorksDW has erroneous data. Therefore, I created a calculated field WeekNumber as follows:
=DatePart(DateInterval.WeekOfYear, CDate(Fields!FullDateAlternateKey.Value))
The following screen shot shows the report in the design mode. As you will notice, the matrix uses WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. EnglishDayNameOfWeek is used to display the days – Sunday through Saturday.
The following screen shot shows the sample output for the month of June 2007. This month has 5 rows representing 5 weeks and 7 columns representing Sunday through Saturday.
The following screen shot shows the sample output for the month of September 2007. Note that this month has 6 rows representing 6 weeks.
