Before I started blogging for Perficient, I had a few posts on a personal blog. One of the post, though outdated, is still referenced quite often. So I thought it might be a good idea to update the post for use with SQL Reporting. The original post was created for SQL Server Reporting Services (SSRS). The technique used to build a calendar style report can be used in other areas as well. The technique is typically called horizontal tables. I use a calendar because I believe it is the easiest way to understand the need for this technique. Another common use for this type of report would be for creating mailing labels.
The sample reports can be found here. The report will generate a single page for the current month and list employee birthdays from a version of the AdventureWorks database. The query and report can easily be updated for use with other dates and information that needs to be listed on a given day. The query I use for my data-set may not offer ideal performance, but I believe it shows how you can use some T-SQL functionality to your benefit.
1: ; WITH DateSequence([Date]) AS ( 2: SELECT CONVERT(DATETIME,@StartDate) AS [Date]
3: UNION ALL 4: SELECT DATEADD(DAY, 1, [Date]) FROM DateSequence WHERE Date < CONVERT(DATETIME,@EndDate)
5: ), EmployeeBirthday AS ( 6: SELECT
7: e.BirthDate, 8: [Day] = DATEPART(DAY, e.BirthDate),
9: [Month] = DATEPART(MONTH, e.BirthDate), 10: [Year] = DATEPART(YEAR, e.BirthDate),
11: FullName = e.FirstName + ' ' + e.LastName 12: FROM
13: DimEmployee e 14: ) SELECT
15: [Order] = DENSE_RANK() OVER (ORDER BY ds.[Date]), 16: [WeekDay] = DATEPART(WEEKDAY, ds.[Date]),
17: [Day] = DATEPART(DAY,[Date]), 18: [Month] = DATEPART(MONTH,ds.[Date]),
19: [Year] = DATEPART(YEAR,ds.[Date]), 20: ds.[Date],
21: eb.FullName 22: FROM DateSequence ds
23: LEFT JOIN EmployeeBirthday eb ON eb.Month = DATEPART(MONTH,ds.[Date]) AND eb.Day = DATEPART(DAY,ds.[Date]) 24:
25: OPTION (MAXRECURSION 1000)
The @StartDate and @EndDate parameters are calculated in the report. @StartDate is equal to the first Sunday to display on the calendar, even if the day falls in the previous month. @EndDate is equal to the last Saturday to display on the calendar, even if the day falls in the next month. I use several common table expressions (CTEs) to try to make the query a little easier to follow. I also use the DENSE_RANK function to number the records based on how they need to display within the calendar.
I’m going to assume you have experience working with SSRS if not SQL Reporting. The design tools for building a report for SQL Reporting, are for the most part consistent with SSRS. Starting with a blank report you will need to add a data-source connecting to a SQL Database. SQL Database is presently the only data-source that SQL Reporting can use.
Once you have the data-source added you can then add the data-set.
Now that you have your data-source and data-set you should now be able to start laying out your report. First you’ll need to add a Matrix control to the body of your report. Then add a Table to the Matrix control in the area watermarked “Data”. Once the Table is added remove the two of the three columns generated. Your report should look like the below screen shot.
You will now need to associate your Matrix with your existing data-set by updating its properties. Once the Matrix is associated with your data-set you will now be able to set up the groupings. for the row grouping you will use a Ceiling calculation based on the number of days in a week. The Ceiling function will decide which records display on which row based on the numbering determined in the query. It basically breaks the records into weeks.
Now that you have your row grouping set up, you’ll want to set up your column grouping. The column grouping will be based on the day of the week from the query.
The Table inside the “Data” region of the Matrix doesn’t need any further work. Based on the established matrix row and column groupings, the matrix data will be organized appropriately. Now that the control of the data is set up, it’s time to define the expressions that will decide what results to display in the matrix and table, when the report is rendered.
I am going to resize the control. We don’t need to display anything in the matrix “Rows” region, so we minimize the left column of the matrix control.
The next step is to apply the following Expressions to the various report items for display on the report:
- Matrix “Columns” area, =WeekdayName(Fields!WeekDay.Value), displays the days of the week across the top of the report.
- Table “Header” area, =Fields!Day.Value, displays the day number for each day in the timeframe.
- Table “Data” area, =Fields!FullName.Value, displays the employee’s name.
Your report layout should now look like the below layout.
Now that the layout is complete you will now need to specify the report parameters to be used by the query. To slightly simplify my calculations for readability I added a couple extra parameters not directly used by the report query. The report parameters with expressions are defined below.
- @FirstDayOfMonth: =Today().AddDays(-(Today().Day-1))
- @LastDayOfMonth: =Today().AddMonths(1).AddDays(-(Today().Day))
- @StartDate: =CDate(Parameters!FirstDayOfMonth.Value).AddDays(-(WeekDay(CDate(Parameters!FirstDayOfMonth.Value))-1))
- @EndDate: =CDate(Parameters!LastDayOfMonth.Value).AddDays(7 -(WeekDay(CDate(Parameters!LastDayOfMonth.Value))))
Now if you were to preview the report you should see something like below, if you have everything set up correctly.
Although the report is now functional, it still looks a little unpolished, so the last step is to tweak the layout and formatting until you are happy with it. For me the below shows a screenshot of the finalized report, both in layout mode and rendered.
You can find a copy of the finalized report(s) here. I additionally added a second version of the report which takes a date range and will generate a page for each month in the range. The query and the reports can be easily updated based on your needs. You can use the techniques describe above with SQL Server 2005 and up as well as SSRS 2005 and up. Though there will be minor differences in the designers.
By the way, if you need a Windows Azure subscription, here is the link for a free trial.
