SSRS 2008 Generate Calendar(s) based on a date range
I’m finally able to start blogging again. In addition to my Simple-Talk article I figured my first post would be an updated calendar report based on employee birthdays in the AdeventureWorks Database. Using recursive common-table-expressions I was able to generate the records needed to build a calendar for a given date range. Here is a link to the article (http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/#hr) to give you a basic idea of what the query is doing to generate the data as well as an example of how to build the report .
Here is the updated query:
--DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) --FirstDayOfMonth
SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) --LastDayOfMonth
; WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
[Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
[Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] < FilterDate
), EmployeeBirthDay AS (
SELECT
e.BirthDate,
[Day] = DATEPART(DAY, e.BirthDate),
[Month] = DATEPART(MONTH, e.BirthDate),
[Year] = DATEPART(YEAR, e.BirthDate),
FullName = c.FirstName + ' ' + c.LastName
FROM
HumanResources.Employee e
INNER JOIN Person.Contact c ON c.ContactID = e.ContactID
) SELECT
DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
d.Month,
[Day] = DATEPART(DAY,d.[Date]),
d.Year,
[WeekDay] = DATEPART(WEEKDAY, d.[Date]),
[Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
d.Date,
ebd.FullName
FROM
Dates d
LEFT JOIN EmployeeBirthDay ebd ON ebd.Year = DATEPART(YEAR,d.[Date]) AND ebd.Month = DATEPART(MONTH,d.[Date]) AND ebd.Day = DATEPART(DAY,d.[Date])
OPTION (MAXRECURSION 1000)
Thanks to David Williams I have updated the query.
The earlier version of the report has been updated to SSRS 2008. There is only a few changes that need to be made to get multiple calendars to display. Add a new tablix which is grouped by “DisplayOnCalendar” with page break after each group. Move the Month information from the report header into the new tablix as the group header. Drag the previous tablix into the details section on the new tablix. You can now remove the “Note” column from the previous tablix and update the value on the “Time” column to look at “FullName.” I made some additional format changes but won’t go in to details on those. Below is an example of what my report looks like before I preview it.
Once I preview the report I will show you the report which includes all the employees from AdventureWorks with their birthday in February 2010. The Date range I used is 03/26/2009 through 03/26/2010 so you will see there is 13 pages of the report to reflect the 13 months in the date range.
Here is a link to download the RDL file for the report above (http://www.box.net/shared/o7i6pc2znt) the zip file also contains the original RDL for the current month calendar report. The current month report is “Calendar.rdl” the updated version is “RangeCalendar.rdl” these reports will work with SSRS 2008 and the Adventureworks database.
This is great! I’m having some problems using it to show planned vacation days for each resource from MS Project Server Reporting Database. Unlike birthdays, multiple records exist for the same employee, so I’m getting erroneous results. Can you tell me where I need to make the adjustment in order to make your example work for me?
Thanks
David can you send me a copy of your query and give me an idea of what your data looks like? rduclos(at)gmail.com
Thank you,
Ryan Duclos
I just came across this post while searching for how to do this. Excellent information. Thanks so much for sharing!
Michael
Great article, I was able to do the calendar as instructed above but when i try to plug in my own data i get errors. This is my query.
SELECT RMResources.ResourceName AS Instructor, RMEvents.EventName, RMEvents.StartDateTime, Orders.OrderID
FROM RMReservations INNER JOIN
RMEvents ON RMReservations.EventID = RMEvents.EventID INNER JOIN
RMResources ON RMReservations.ResourceID = RMResources.ResourceID INNER JOIN
OrderLines ON RMEvents.EventID = OrderLines.EventID INNER JOIN
Orders ON OrderLines.OrderID = Orders.OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
CustContacts ON Orders.ContactID = CustContacts.CustContactID INNER JOIN
Addresses ON Customers.AddressID = Addresses.AddressID LEFT OUTER JOIN
Notes ON Orders.NoteID = Notes.NoteID
WHERE (RMReservations.ResourceTypeID = 17) AND (RMEvents.StartDateTime >= @startd) AND (RMEvents.StartDateTime < @endd + 1)
ORDER BY RMEvents.StartDateTime
I would try something like this:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) –FirstDayOfMonth
SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) –LastDayOfMonth
; WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
[Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
[Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] = @StartDate)
AND (RMEvents.StartDateTime < @EndDate + 1)
) SELECT
DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
d.Month,
[Day] = DATEPART(DAY,d.[Date]),
d.Year,
[WeekDay] = DATEPART(WEEKDAY, d.[Date]),
[Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
d.Date,
e.Instructor,
e.EventName,
e.OrderID
FROM
Dates d
LEFT JOIN Events e ON e.Year = DATEPART(YEAR,d.[Date]) AND e.Month = DATEPART(MONTH,d.[Date]) AND e.Day = DATEPART(DAY,d.[Date])
ORDER BY
DisplayOnCalendar,
[Order],
e.StartDateTime
OPTION (MAXRECURSION 1000)
i recieve errors when i try to run it.
@Luis
What errors are you receiving?
This article has been extremely useful. Could you tell me how I can change the calendar so that it does not display Sunday and Saturday?
Without actual implementing it myself, i see a couple of options off the top of my head.
1. You can filter out the records for DayOfWeek 1 and 7
2. You can set the column visibility based on the DayOfWeek.
@Ryan Duclos
if i copy the query you sugested and paste it in sql management studio and run it , this is the error i get:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘–’.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘–’.
Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword ‘AND’.
Thanks
you can correct me if im doing something wrong but these are the steps i took:
1) Downloaded your report , pointed then to the adventureworks db and the daterange calendar runs fine.
2) Copy and pasted your query above and pasted it o the datase query and pointed the dataset to my db ( the one with the RMevents table)
3) Run the calendar again and get this error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘–’.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘–’.
Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword ‘AND’.
Thanks
@luis
you need to replace the “–” with a hyphen. Some times text editors replace characters with those not supported by sql.
@ryan
That worked great for the “-” error but im still getting 1 last error
” Incorrect syntaxt net the keyword ‘AND’
it seems it doesnt like this part
WHERE
[Date] = @StartDate)
AND (RMEvents.StartDateTime < @EndDate + 1)
) SELECT
DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
d.Month,
[Day] = DATEPART(DAY,d.[Date]),
d.Year,
[WeekDay] = DATEPART(WEEKDAY, d.[Date]),
[Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
d.Date,
e.Instructor,
e.EventName,
e.OrderID
FROM
Dates d
LEFT JOIN Events e ON e.Year = DATEPART(YEAR,d.[Date]) AND e.Month = DATEPART(MONTH,d.[Date]) AND e.Day = DATEPART(DAY,d.[Date])
ORDER BY
DisplayOnCalendar,
[Order],
e.StartDateTime
OPTION (MAXRECURSION 1000)
I really appreciate your help!
@luis
Looks like I need to remove the first “)” from the where clause.
[Date] = @StartDate)
to
[Date] = @StartDate
@rduclos
I fell like im a lot closer but below that imget an error
e.Instructor,
e.EventName,
e.OrderID
FROM
Dates d
LEFT JOIN Events e ON e.Year = DATEPART(YEAR,d.[Date]) AND e.Month = DATEPART(MONTH,d.[Date]) AND e.Day = DATEPART(DAY,d.[Date])
ORDER BY
DisplayOnCalendar,
[Order],
e.StartDateTime
OPTION (MAXRECURSION 1000)
i have squigly lines below almost all of these. not sure if this helps but the data i need is an a different database in a table named rmevents, from that table i need 3 fields to display on the calendar
rmevents.orderid
rmevents.eventaname
rmevents.startdatetime
Thanks
@luis
The only portion of my query that requires the AdventureWorks DB is the “EmployeeBirthDay” Common table expression (CTE). You should be able to replace that section for your needs and update all the references to it with your schema information and run it on your database.
@rduclos
I apreciate your help with this , ive been going at it all night and have 1 error left. I cant understand why im not able to get this right , Im only going after 1 table (RMevents) and I only need to fields (rmevents.eventname & rmevents.startdatetime) the eventname is what i want to put on the calandar
This is the error i get:
“incorrect syntax neer keyword select”
these are the only two fields ftom the table i need:
e.EventName (This is replacing the employee name), e.startdatetime
This is the code:
–DECLARE @StartDate DATETIME, @EndDate DATETIME
SELECT @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)) –FirstDayOfMonth
SELECT @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)) –LastDayOfMonth
; WITH Months AS (
SELECT
[Month] = DATEPART(MONTH,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
[Year] = DATEPART(YEAR,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
FirstDayOfMonth = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0)))+1,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)))
UNION ALL SELECT
[Month] = DATEPART(MONTH,DATEADD(MONTH,1,FirstDayOfMonth)),
[Year] = DATEPART(YEAR,DATEADD(MONTH,1,FirstDayOfMonth)),
FirstDayOfMonth = DATEADD(MONTH,1,FirstDayOfMonth),
LastDayOfMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)),
FirstDayOfCalendar = DATEADD(DAY,-DATEPART(WEEKDAY,DATEADD(MONTH,1,FirstDayOfMonth))+1,DATEADD(MONTH,1,FirstDayOfMonth)),
LastDayOfCalendar = DATEADD(DAY,6-DATEPART(WEEKDAY,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0))),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,1,FirstDayOfMonth))+1,0)))
FROM
Months
WHERE
LastDayOfMonth < @EndDate
), Dates AS (
SELECT
[Month],
[Year],
[Date] = FirstDayOfCalendar,
FilterDate = LastDayOfCalendar
FROM
Months
UNION ALL SELECT
[Month],
[Year],
[Date] = DATEADD(DAY,1,[Date]),
FilterDate
FROM
Dates
WHERE
[Date] = @StartDate
AND (RMEvents.StartDateTime < @EndDate + 1)
SELECT
DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
d.Month,
[Day] = DATEPART(DAY,d.[Date]),
d.Year,
[WeekDay] = DATEPART(WEEKDAY, d.[Date]),
[Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
d.Date,
e.EventName, e.startdatetime
FROM
Dates d
LEFT JOIN Events e ON e.Year = DATEPART(YEAR,d.[Date]) AND e.Month = DATEPART(MONTH,d.[Date]) AND e.Day = DATEPART(DAY,d.[Date])
ORDER BY
DisplayOnCalendar,
[Order],
e.StartDateTime
OPTION (MAXRECURSION 1000)
Great calendar report template! I am working on solving a formatting issue which is that the title (group header) is not repeating on new pages if the calendar is too large for one page.
Awesome calendar, thanks for sharing. I have to build 2 reports, one showing a month view, one showing a week view. For the month view, everything looks great, but for the week view, I’d like to have it fit one page(i.e. maybe 2 or 3 rows, with 2 or 3 days in each row), do you know if if there’s a way to do that? Either way, thanks again!
Great article Ryan. I’d like to give you credit and provide a link back to this article in a blog article I’m writing. Can you contact me so I can give you a draft version to review? mguthart (at) meta-comm.com
You, have my permission to link-back to this article. I’m glad my post was able to assist you.
Greg,
I would suggest playing around with the ranking functions used with the ceiling function that determine when to break into new rows. If you can send me a screen shot or something to show me how you want it displayed I may take a look at trying to figure it out. Also let me know which version of SSRS you are targeting.
You can contact me from http://about.me/rduclos or rduclos (at) gmail.com
Great article! – Just shared this with my CIO and got a go ahead to replace a .net web app used to display day specific data in calendar cells. I’ll send another post once I roll up my sleeves and get knee deep in it. Thanks for sharing!
I used your process above to create my own calendar (I didn’t use the stored procedure or rdl) and am running into a minor issue and wonder if you can help me solve it.
I have my data grouped by Row on Week Number and by Column on Day of Week for a whole year. Everything works correctly, but I get misc dates in the non-date Column Headings.
For example, for Jan 2011 ends on Monday, I have it set to move to a new block for Feb, so Tues-Fri (not showing weekends) should be blank, instead, the Col heading shows Jan 4, Jan 5, Jan 6, Jan 7 (no data is displayed below, which is correct). Then when it shows Feb, it has Feb 7 in the Monday block (should be blank), then Feb 1st data (correct).
Mon Tues Wed Thurs Fri
Jan31 Jan4 Jan5 Jan6 Jan7
678
Mon Tues Wed Thurs Fri
Feb7 Feb1 Feb2 Feb3 Feb4
569 454 453 3434
I want to have nothing show for the non-dates in the months and can’t seem to get it to work using an expression.
KDooley, Glad you figure out the expression in the Title. Kudos to you!
Simply awesome!