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 = '03/26/2009', @EndDate DATETIME = '03/26/2010'
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 = e.FirstName + ' ' + e.LastName
	FROM
	 DimEmployee e
) 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.Month = DATEPART(MONTH,d.[Date]) AND ebd.Day = DATEPART(DAY,d.[Date])

OPTION (MAXRECURSION 1000)

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.

About these ads
    • David Williams
    • August 5th, 2010

    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

  1. 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

    • mmartin
    • November 21st, 2010

    I just came across this post while searching for how to do this. Excellent information. Thanks so much for sharing!

    Michael

    • Luis
    • January 3rd, 2011

    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

  2. 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)

    • Luis
    • February 5th, 2011

    i recieve errors when i try to run it.

  3. @Luis

    What errors are you receiving?

    • Susie
    • February 14th, 2011

    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?

  4. 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.

    • luis
    • February 17th, 2011

    @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

    • luis
    • February 22nd, 2011

    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

  5. @luis

    you need to replace the “–” with a hyphen. Some times text editors replace characters with those not supported by sql.

    • luis
    • February 22nd, 2011

    @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!

  6. @luis
    Looks like I need to remove the first “)” from the where clause.

    [Date] = @StartDate)
    to
    [Date] = @StartDate

    • luis
    • February 23rd, 2011

    @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

  7. @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.

    • luis
    • February 26th, 2011

    @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)

    • Raul Herrero
    • March 9th, 2011

    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.

    • Greg
    • July 6th, 2011

    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!

  8. 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

  9. You, have my permission to link-back to this article. I’m glad my post was able to assist you.

  10. 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

    • bsivel
    • August 25th, 2011

    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!

    • KDooley
    • September 12th, 2011

    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.

  11. KDooley, Glad you figure out the expression in the Title. Kudos to you!

    • A.M.
    • November 29th, 2011

    Simply awesome!

  12. Great sample….I was able to make this work with our database with no issues. I did have one question, is there a way to hide the previous months days and data from the current calendar? For example, if I run this report for 3 months Jan- March and I look at page 2 with February 2012 data, I do not want to show Sun – Tuesday data in week 1 because that is already in the January 2012 calendar. Basically it is showing data in two places.

    Thanks again!
    Bob

  13. I see the color expression for the note in the older sample from the simple-talk example, if I could get the color to go to white for these days, that should solve it.

    • Matt
    • March 26th, 2012

    @Bob Naylor
    I am getting the same issue. If one of my events falls on 3/26/2012 for example, it will also show up on 2/26/2012 (they show on the same page). Looking at the query it appears that [DATE] is somehow forcing the event to have both dates (3/26 & 2/26) for some reason so it is appearing twice.

    Any thoughts?

    • Matt
    • March 26th, 2012

    @Bob Naylor
    Found out the issue. It looks like the FROM code of the DisplayOnCalendar functionality was not qualifying the month of my data. I updated the code to look like this (end of the query). Keep in mind that I modified the query some (ScheduledDate is my custom query):

    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
    ,sd.new_initials –My result fields
    ,sd.new_name –My result fields
    FROM
    Dates d
    LEFT JOIN
    ScheduledDate sd ON sd.Month = DATEPART(MONTH,d.[Date]) –Updated right side of equation to take MONTH into account
    AND
    sd.Day = DATEPART(DAY,d.[Date])
    AND
    sd.Year = d.Year

    OPTION (MAXRECURSION 1000)

  14. @Matt, @Bob Naylor

    Using my query above update the JOIN to include an additional filter criteria “ebd.Month = d.Month AND ebd.Month = DATEPART(MONTH,d.[Date])“. The first part compares the month being displayed. The second part compares the actual month of the date giving. All 3 months must be the same for the record to display. Depending on your needs you may need to-do the same for YEAR filtering.

    If you don’t want the “Day” to display either, you can use the following expression “=IIf(Not Fields!Month.Value = Month(Fields!Date.Value), “”, Fields!Day.Value)” in the cell of the matrix.

    Thank you,
    Ryan Duclos

    • Ryan
    • December 4th, 2013

    Ryan – thank you for posting this. This was very unselfish of you and also saved me a lot of time and heart ache :)

    We modified this report to show 12 months of an employee’s leave/absence on the same page. The supervisor can enter start and end dates and the employee’s ID to pull the 12 months of data that corresponds to the employees review period.

    The supervisors like the ability to visualize an entire calendar year at once, instead of reading down a list.

    The comments and fixes on this page has helped as well.

    Thanks again. Roll Tide!

    Ryan in Cincinnati

  15. Hi Ryan,

    Thanks for sharing your awesome article. It has saved so much of time. I am trying to list events on the calendar. But my problem is if an event spans across multiple days, then how can I show it on the report. Is there any way to display that information in a bar as in Outlook Calendar.

    Thanks,

    Diva

  16. Diva,

    Believe it or not I recently made a report to span multiple days. Download the report and take a look. You just mainly need to update the Activity CTE in the query (https://dl.dropboxusercontent.com/u/20069958/ActivityCalendar.rdl).

  17. Hi Ryan,

    Wow… you are a champion. I am not able to download the rdl. In my scenario, there could be multiple events on the same day and they could span over several days.

    Thanks,

    Diva

  18. @Priyanka Agarwal Kochhar

    The calendar i sent will allow for up to 5 events on a giving day that can span multiple days.

  19. Hi Ryan,

    Thanks for sending the rdl. I am having issues with display of multiple courses which spans multiple days. Issue is that for day1, the ranking of the course is 1 so it appears on first row for the day but day 2, the ranking of the course is 2 so it appears on second row for day 2 as there is one more course on that day. How can I fix this issue?

    Thanks,

    Diva

  20. @Priyanka Agarwal Kochhar

    Send me a copy of your query and I will see if I can figure out what is wrong with it.

  21. Sent it to you.

    Thanks,

    Diva

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: