SQL Calendar in Business Intelligence Development Studio

So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...

The DataSet

The script I used to generate the dates comes from www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/
copyraw
DECLARE @StartDate DATETIME, @EndDate DATETIME
--First day of current month
SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
--First day to display on calendar
SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate)
--Last day of month
SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
--Last day to display on calendar
SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate)
 
; WITH Dates([Date]) AS (
   --Select First day in range
   SELECT CONVERT(DATETIME,@StartDate) AS [Date]
   UNION ALL
   --Add a record for every day in the range
   SELECT DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < CONVERT(DATETIME,@EndDate)
), Events AS (
   --Create some events to place on the calendar
   SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2009 02:00:00 PM', Note = 'Event 1'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2009 12:00:00 PM', Note = 'Event 2'
  UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 02:00:00 PM', Note = 'Event 3'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 06:30:00 PM', Note = 'Event 4'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2009 07:00:00 PM', Note = 'Event 5'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6'
) SELECT
   -- Number the records based on the date, if multiple records have 
   -- the same date then they will be numbered the same. Used in 
   -- calculation to determine row record is to display on.
   [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]),
   -- date used in all calculations for date
   d.[Date],
   --generates matrix columns
   [WeekDay] = DATEPART(WEEKDAY, d.[Date]),
   --used to display day of month on calendar
   [Day] = DATEPART(DAY,d.[Date]),
   --used in some calculations for display
   [Month] = DATEPART(MONTH,d.[Date]),
   -- used to get the time of the event
   e.EventDate,
   --event details to display
   e.Note
--CTEs defined above are used as the queries for the results
FROM Dates d
   LEFT JOIN Events e ON CAST(CONVERT(VARCHAR(10),e.EventDate,101) AS DATETIME) = d.[Date]
 
--Set the maximum times the Dates cte can recurse
OPTION (MAXRECURSION 100)
  1.  DECLARE @StartDate DATETIME, @EndDate DATETIME 
  2.  --First day of current month 
  3.  SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) 
  4.  --First day to display on calendar 
  5.  SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate) 
  6.  --Last day of month 
  7.  SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) 
  8.  --Last day to display on calendar 
  9.  SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate) 
  10.   
  11.  ; WITH Dates([Date]) AS ( 
  12.     --Select First day in range 
  13.     SELECT CONVERT(DATETIME,@StartDate) AS [Date] 
  14.     UNION ALL 
  15.     --Add a record for every day in the range 
  16.     SELECT DATEADD(DAY, 1, [Date]) FROM Dates WHERE Date < CONVERT(DATETIME,@EndDate) 
  17.  ), Events AS ( 
  18.     --Create some events to place on the calendar 
  19.     SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2009 02:00:00 PM', Note = 'Event 1' 
  20.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2009 12:00:00 PM', Note = 'Event 2' 
  21.    UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 02:00:00 PM', Note = 'Event 3' 
  22.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 06:30:00 PM', Note = 'Event 4' 
  23.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2009 07:00:00 PM', Note = 'Event 5' 
  24.     UNION SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6' 
  25.  ) SELECT 
  26.     -- Number the records based on the date, if multiple records have 
  27.     -- the same date then they will be numbered the same. Used in 
  28.     -- calculation to determine row record is to display on. 
  29.     [Order] = DENSE_RANK() OVER (ORDER BY d.[Date]), 
  30.     -- date used in all calculations for date 
  31.     d.[Date], 
  32.     --generates matrix columns 
  33.     [WeekDay] = DATEPART(WEEKDAY, d.[Date]), 
  34.     --used to display day of month on calendar 
  35.     [Day] = DATEPART(DAY,d.[Date]), 
  36.     --used in some calculations for display 
  37.     [Month] = DATEPART(MONTH,d.[Date]), 
  38.     -- used to get the time of the event 
  39.     e.EventDate, 
  40.     --event details to display 
  41.     e.Note 
  42.  --CTEs defined above are used as the queries for the results 
  43.  FROM Dates d 
  44.     LEFT JOIN Events e ON CAST(CONVERT(VARCHAR(10),e.EventDate,101) AS DATETIME) = d.[Date] 
  45.   
  46.  --Set the maximum times the Dates cte can recurse 
  47.  OPTION (MAXRECURSION 100) 
I'd appreciate if you bear with me as I add captions to the below screenshots:

Design the Query: So copy the above text, click on query builder and "edit as text", then paste it into there. When you ok this you should be on the following screen:


Select the Report Type: For this calendar we want matrix because we're going to group based on the rows:


Design the Matrix: Ok so we want weekday in the columns (to have mon-sun along the top), "Order" in rows (order is which week in the month), and the Date and Note in the details (will appear in each cell per day).


Grouping by weeks: Now we finish with the report wizard and returned to the designer. Right-click on the "[Order]" cell and select "Add Group" then click on "Parent Group..." as shown.


Week grouping expression: you're being asked to Group by, so we click on the "fx" button to enter the below expression:


Delete Order Column: Ok the expression and BIDS will add the parent group we added just before. You can now right-click on the top of the [Order] column (grey square -like excel- to select the column). Select "delete columns".


Still deleting: Make sure you select "delete columns and associated groups":


Overview: So the following screenshot is how your report should look now.


Result: You can even run/preview it and you should get the below:



Formatting

In other words, making it look pretty. I'm doing this now because you may not need parameters or events. The above should create your calendar, the steps that follow are to display it in a recognizable calendar format.














A Parameter

Now I need to add a parameter to the report and I'm doing it now because I'll need to use it for formatting and enhancing the dataset. For the moment, I want the user to be able to select a date and then the report to return the month containing that date:




I'm going to change the header from "Reinventing the wheel" to the Month and Year based on this parameter:
Note that I've asked it to return the full date of the parameter, this is because I will format it via the text-box properties instead of getting the expression to return it exactly (there seem to be issues with this - error textbox1[0]...).

You will need this expression to set the font color of days that are in the given month (where "Gainsboro" is a type of light grey):
copyraw
=IIf(Month(First(Fields!Date.Value))=Month(Parameters!GivenDate.Value), "Black", "Gainsboro")
  1.  =IIf(Month(First(Fields!Date.Value))=Month(Parameters!GivenDate.Value), "Black", "Gainsboro") 
Right-click on the Date textbox and select Font then for color, select Expression (fx):





Adding Events

To do this we'll be modifying the dataset. Now would be a good time to save if you haven't yet. Let's focus on this part of the used dataset SQL script:
copyraw
), Events AS (

   --Create some events to place on the calendar
   SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2011 02:00:00 PM', Note = 'Event 1'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2011 12:00:00 PM', Note = 'Event 2'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2011 02:00:00 PM', Note = 'Event 3'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2011 06:30:00 PM', Note = 'Event 4'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2011 07:00:00 PM', Note = 'Event 5'
   UNION SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6'
  1.  ), Events AS ( 
  2.   
  3.     --Create some events to place on the calendar 
  4.     SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2011 02:00:00 PM', Note = 'Event 1' 
  5.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2011 12:00:00 PM', Note = 'Event 2' 
  6.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2011 02:00:00 PM', Note = 'Event 3' 
  7.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2011 06:30:00 PM', Note = 'Event 4' 
  8.     UNION SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2011 07:00:00 PM', Note = 'Event 5' 
  9.     UNION SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6' 
As you can see, these events were hard-coded into the script. I was asked to replace these with events from an actual database. So I just need a table returned with the "EventDate" and the "Note" variables from the other database. If I use the hard-coded examples above, I would want a results table similar to the following:
copyraw
EventDate                            Note
-------------------------------------------------------------------
01/30/2011 02:00:00 PM               Event 1
01/23/2011 12:00:00 PM               Event 2
01/28/2011 02:00:00 PM               Event 3
01/28/2011 06:30:00 PM               Event 4
01/30/2011 07:00:00 PM               Event 5
  1.  EventDate                            Note 
  2.  ------------------------------------------------------------------- 
  3.  01/30/2011 02:00:00 PM               Event 1 
  4.  01/23/2011 12:00:00 PM               Event 2 
  5.  01/28/2011 02:00:00 PM               Event 3 
  6.  01/28/2011 06:30:00 PM               Event 4 
  7.  01/30/2011 07:00:00 PM               Event 5 
Category: Transact-SQL :: Article: 318

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.