Paginated Reports eBook Introduction, Resource and Sample Data Downloads

Although there no built-in component for visualizing dates in a calendar, it is achievable using a matrix. I’ve approached calendar and time reporting a few different ways in the past and the solution is generally accomplished through the presentation of the data to the report, rather than using a feature of the reporting tool. Brian Larson, author and long-time community contributor, demonstrated the technique shown in this recipe at the PASS Global Summit and I was thoroughly impressed with his approach. He was kind enough to share this code for inclusion as a report recipe. This report produces a very functional monthly calendar with navigation controls, conditional color-coding, and drill-down details. Brian’s technique is preserved in this recipe. I have updated the sample data and added some commentary.

Recipe Ingredients

Product Versions (one of the following):

• SQL Server Reporting Services 2000 or newer
• Power BI Report Server
• Paginated Reports in Power BI or Power BI Desktop

What You’ll Need

• Azure SQL, or SQL Server 2005 or newer
• A functional knowledge of database programming objects and TSQL query design
• An understanding of groups used in report design
• A table or matrix report
• Conditional expressions

Difficulty Level: 300

Contributed by Paul Turley, Brian Larson

Designing the Report

In this recipe, you examine a completed report and dissect all of its working components. The magic behind this reporting technique is really in the database query. The rest is fairly straightforward report design. This recipe does not included step-by-step instructions to build the report from scratch, but should include sufficient details for you to re-construct a similar report from a working example. Figure 1 shows the finished report.

Figure 1

The PaginatedReportData database includes a table named Appointments, which only has two columns: AppointmentDateTime and AppointmentDescr. Figure 2 shows a sampling of records in this table.

Figure 2

Let’s think about how the data in these two columns could be transformed in to a calendar view. What is a calendar, really?

It is a report consisting of one page per month. This means that we will need to insert a page break after each month.

For each month, it is a matrix of seven columns and five or six rows.

Columns represent the days of the week beginning on a specified weekday, for our purposes: Sunday through Saturday.

Rows in the matrix represent the weeks of a month. There could be five or six rows in the calendar matrix for a given month.

The AppointmentDateTime column contains enough information to extrapolate all the components needed to arrange events into a calendar grid, including the Year, Month, Week number and Day of the Week.

To populate a matrix with each day of the month, a query must return each date as a separate record. A table-valued function is used to return every date for a given month and year by generating these rows in a loop without having to store these rows in a physical table. The function defined with the following script has already been added been added to the PaginateReportData sample database and provided here for reference. The function returns one row for each day of the month.

CREATE OR ALTER FUNCTION dbo.fn_GenerateMonth(@MonthNum int, @Year int)
RETURNS
@Month TABLE
(
-- Add the column definitions for the TABLE variable here
MonthDate datetime,
DayNumber int,
DayName varchar(12),
DayOfWeek int,
WeekOfMonth int,
MonthName varchar(12)
)
AS
BEGIN
DECLARE @MonthDate datetime
DECLARE @WeekOfMonth int
DECLARE @WeekFillDate datetime
DECLARE @MonthName varchar(12)
SET @WeekOfMonth = 1
-- Find the first day of the month and the month name.
SET @MonthDate = RIGHT('0' + CONVERT(varchar(2), @MonthNum),2) + '/01/'
+ CONVERT(char(4), @Year)
SET @MonthName = DATENAME(mm, @MonthDate)
-- Back up to the first day of the week containing the first day of the month.
SET @WeekFillDate = @MonthDate
WHILE DATEPART(dw, @WeekFillDate) > 1
BEGIN
SET @WeekFillDate = DATEADD(dd, -1, @WeekFillDate)
INSERT INTO
@Month (MonthDate, DayNumber, DayName, DayOfWeek, WeekOfMonth, MonthName)
VALUES (@WeekFillDate, DAY(@WeekFillDate), DATENAME(dw, @WeekFillDate),
DATEPART(dw, @WeekFillDate), @WeekOfMonth, @MonthName)
END
WHILE MONTH(@MonthDate) = @MonthNum
BEGIN
IF DATEPART(dw, @MonthDate) = 1 AND DAY(@MonthDate) > 1
BEGIN
SET @WeekOfMonth = @WeekOfMonth + 1
END
INSERT INTO
@Month (MonthDate, DayNumber, DayName, DayOfWeek, WeekOfMonth, MonthName)
VALUES (@MonthDate, DAY(@MonthDate), DATENAME(dw, @MonthDate),
DATEPART(dw, @MonthDate), @WeekOfMonth, @MonthName)
SET @MonthDate = DATEADD(dd, 1, @MonthDate)
END
-- Finish up the week containing the last day of the month.
SET @WeekFillDate = DATEADD(dd, -1, @MonthDate)
WHILE DATEPART(dw, @WeekFillDate) < 7
BEGIN
SET @WeekFillDate = DATEADD(dd, 1, @WeekFillDate)
INSERT INTO
@Month (MonthDate, DayNumber, DayName, DayOfWeek, WeekOfMonth, MonthName)
VALUES (@WeekFillDate, DAY(@WeekFillDate), DATENAME(dw, @WeekFillDate),
DATEPART(dw, @WeekFillDate), @WeekOfMonth, @MonthName)
END
RETURN
END

We can see the output from the function by running a simple test query in SQL Server Management Studio, executing this command, passing 5 for the month and 2010 for the year.

SELECT * FROM dbo.fn_GenerateMonth(05, 2010)

The nested loop logic in the user-defined function works through several objects in the month hierarchy. First, the function logic determines the date that should appear in the top-left-most cell, which is the Sunday on or before the first day of the month. In other words, if the first of the month starts on Wednesday, that means there are three days from the prior month that must be included. Likewise, unless the end of the month falls on a Saturday, dates from the following month must be included to fill the end of the grid.

With all this information, the function is able to assign the DayName, DayOfWeek, and WeekOfMonth column values to every row—even for those that belong to adjacent months sharing the first and last weeks with the month in question. Figure 3 shows these column values. Note that the MonthName column is the same for every row and doesn’t change for rows representing the prior and subsequent month dates (for instance, April 25 through 30 in the case of the first five rows). This column is only used in the report header title.

Figure 3

Take a close look at these column values. You now have the ability to separate row groups and column groups in a matrix based in the WeekOfMonth and the DayOfWeek values.

Joining this set of rows with the actual appointment records provides all the information necessary to populate the calendar grid in a matrix report data region.

The data source references the Calendar database using the appropriate user or application credentials. The dataset contains a left outer join between the table-valued function and the Appointments table data filtered by the month and a year.

-- Declare variables to test the query in Management Studio
-- These variables are query parameters in the report dataset
-- Omit these variable declaration lines from the report dataset query:
declare @Year smallint = 2010
declare @MonthNum smallint = 5
-- Report dataset query:
SELECT
Dates.MonthDate
, DayNumber
, Dates.DayName
, Dates.DayOfWeek
, Dates.WeekOfMonth
, Dates.MonthName
, Appointments.AppointmentDateTime AS ScheduledDate
, SUBSTRING(CONVERT(char(19)
, Appointments.AppointmentDateTime, 100),13,7)
AS ScheduledTime
, Appointments.AppointmentDescr
FROM
dbo.fn_GenerateMonth(@MonthNum, @Year) AS Dates
LEFT OUTER JOIN
Appointments ON Dates.MonthDate = CONVERT(datetime, CONVERT(char(10),
Appointments.AppointmentDateTime, 101))
ORDER BY
Dates.MonthDate

When you run the query, you’ve substituted 5 for the @Month parameter and 2010 for @Year. Note that the first two rows shown in Figure 4 are for the last two days in the previous month.

Figure 4

Take a close look at the matrix data region in design view, shown in Figure 5. The design is quite simple. Rows are grouped on the WeekOfMonth field. Columns are grouped first on the MonthName. This is just to create a header for the month. All rows returned by the query will have the same MonthName. The significant column grouping is on the DayOfWeek number, which will produce seven columns labeled with the DayName field.

Figure 5

Note the month navigation cells in the matrix header rows. The matrix detail cell contains an embedded table. The table is used to manage multiple appointment items for each date.

The matrix data region will produce a column for each distinct DayOfWeek value and will produce a new row for each distinct WeekOfMonth value.

Some of the subtle and optional adornments are enabled through simple expressions. These include:

Showing the date number for the previous and following month in gray and the current month dates in black.

The current date cell is displayed with a khaki-shaded background.

The description text for each appointment is hidden by default and can be displayed using the time as a toggle item to expand or collapse the description text.

A quick review of these features reveals properties that use simple expressions. The DayNumber textbox Color property is set using an expression that compares the Month value for each cell to the MonthNum parameter value. If the cell is within the selected month, the text is Black. Otherwise, it is Gray.

=IIF(Month(Fields!MonthDate.Value) = Parameters!MonthNum.Value, “Black”, “Gray”)

The background fill color for each cell is controlled by the BackgroundColor property of the embedded table. This property is set using an expression that compares the date value for the cell to the current date value returned by the Visual Basic.NET Now() function. If the dates are the same (and the cell represents the date for today) the background color is set to Khaki. Otherwise, it is White.

=IIF(Format(CDate(Max(Fields!MonthDate.Value)), "MMddyyyy")

= Format(Now(), "MMddyyyy"), "Khaki", "White")

To navigate backward and forward using the double arrow “buttons” in the header, two expressions are used to alter the MonthNum and Year parameters passed in a report action. While navigating backward, it’s a relatively simple task to subtract one from the current value of the MonthNum parameter, until you get to January. Likewise, navigating forward works well until you encounter December, which requires the Year parameter value to be conditionally modified as well.

For the left-most textbox, used to navigate to the previous month, the following two expressions are used:

Parameter	Value
MonthNum	=IIF(Parameters!MonthNum.Value=”01″, “12”, Right(“0” & CStr(CInt(Parameters!MonthNum.Value)-1), 2))
Year	=IIF(Parameters!MonthNum.Value=”01″, CStr(CInt(Parameters!Year.Value)-1), Parameters!Year.Value)

For the right-most textbox, used to navigate to the next month, the following two expressions are used:

Parameter	Value
MonthNum	=IIF(Parameters!MonthNum.Value=”12″, “01”, Right(“0” & CStr(CInt(Parameters!MonthNum.Value)+1), 2))
Year	=IIF(Parameters!MonthNum.Value=”12″, CStr(CInt(Parameters!Year.Value)+1), Parameters!Year.Value)

In preview, the report looks—well, it looks just like a calendar! (See Figure 6.) For demonstration purposes, I have changed the system date on my computer to May 11, 2010. As you can see, the “current date” is highlighted and I have a school event to attend at 4:00 PM.

Figure 6

This recipe is an excellent example of the principle I call “compounded simplicity.” The techniques used to build this calendar report are not all that complicated—it’s just that there are a few layers of simplicity all piled on top of each other. You might enjoy this demonstration because it exposes the raw capability of Reporting Services in an application that it isn’t specifically designed to perform.

Final Thoughts

The most complex component of this recipe was performed by SQL Server in a table-valued user-defined function. Based on a user-specified month and year parameter, the function returns a set of rows representing all of the dates that should be included in the calendar grid. This includes the dates from the prior and subsequent months needed to fill in the ends of the calendar grid. A matrix data region groups the data on week days and week numbers to create the month grid. An embedded table displays multiple appointments per day cell. Expressions are used to conditionally format the dates that fall within the specified month and the current date cell. Navigation arrows are actually textboxes used to perform a report action and use expressions to navigate backward and forward by calculating the month and year parameter values.

Credits and Related References

Brian Larson is the author of Microsoft SQL Server 2008 Reporting Services (McGraw Hill Osborne Media, 2008). He is the Chief of Technology for Superior Consulting in Minneapolis, MN (http://teamscs.com), and a featured speaker at industry conferences.

Used with permission.