Room Availability Calendar in Business Intelligence Development Studio

Well I tried and I spent a few hours searching the net for a solution which displays the hours in the top columns, and undefined number of rooms in the rows. No all I was looking for was a calendar showing 1 day with the hours on the top. Working hours only as well...

Ok I admit I only googled for about 1 hour before I gave up and just wrote my own. Saying that it's taken me 4 hours just to do the below... hopefully it will be quicker next time now that I noted it all down.


The SQL Script:
copyraw
DECLARE 
	@StartTime datetime, 
	@EndTime datetime,
	@GivenDate datetime;

SET DATEFORMAT dmy;

SET @GivenDate = '23/03/2011';
--First slot of the day
SET @StartTime = @GivenDate + ' 08:00:00';
--Last slot of the day (30 minutes before the end)
SET @EndTime = @GivenDate + ' 17:30:00';

; WITH Times([Time]) AS (
   --Select First hour in range
   SELECT CONVERT(DATETIME,@StartTime) AS [Time]
   UNION ALL
   --Add a record for every half-hour in the range (change based on slot times)
   SELECT DATEADD(MI, 30, [Time]) FROM Times WHERE Time < CONVERT(DATETIME,@EndTime)
), Events AS (
   --Create some events to place on the calendar
   SELECT Room='TEST1', EventStart = '15:00:00', EventFinish='16:00:00', Status = '2', Tooltip='Some more details'
   UNION SELECT Room='TEST2', EventDate = '11:00:00', EventFinish='12:30:00', Status = '2', Tooltip='Some more details'
)
SELECT * FROM Times t
LEFT OUTER JOIN Events e ON CONVERT(VARCHAR(8) , t.Time, 108) BETWEEN e.EventStart AND DATEADD(minute, -1, e.EventFinish) 
--Set the maximum times the Dates cte can recurse
OPTION (MAXRECURSION 100)
  1.  DECLARE 
  2.      @StartTime datetime, 
  3.      @EndTime datetime, 
  4.      @GivenDate datetime; 
  5.   
  6.  SET DATEFORMAT dmy; 
  7.   
  8.  SET @GivenDate = '23/03/2011'
  9.  --First slot of the day 
  10.  SET @StartTime = @GivenDate + 08:00:00'
  11.  --Last slot of the day (30 minutes before the end) 
  12.  SET @EndTime = @GivenDate + 17:30:00'
  13.   
  14.  ; WITH Times([Time]) AS ( 
  15.     --Select First hour in range 
  16.     SELECT CONVERT(DATETIME,@StartTime) AS [Time] 
  17.     UNION ALL 
  18.     --Add a record for every half-hour in the range (change based on slot times) 
  19.     SELECT DATEADD(MI, 30, [Time]) FROM Times WHERE Time < CONVERT(DATETIME,@EndTime) 
  20.  ), Events AS ( 
  21.     --Create some events to place on the calendar 
  22.     SELECT Room='TEST1', EventStart = '15:00:00', EventFinish='16:00:00', Status = '2', Tooltip='Some more details' 
  23.     UNION SELECT Room='TEST2', EventDate = '11:00:00', EventFinish='12:30:00', Status = '2', Tooltip='Some more details' 
  24.  ) 
  25.  SELECT * FROM Times t 
  26.  LEFT OUTER JOIN Events e ON CONVERT(VARCHAR(8) , t.Time, 108) BETWEEN e.EventStart AND DATEADD(minute, -1, e.EventFinish) 
  27.  --Set the maximum times the Dates cte can recurse 
  28.  OPTION (MAXRECURSION 100) 

1. Create a new report:
We're going to run the wizard here but without it just add a matrix with [time] in the "column" and [room] in the "row" and [note/status] in the "data" cells.


2. Use the SQL code above:
So you can paste this straight into the textarea or use "Query Builder".


3. Select Matrix Tablix:
We want all rooms on the left and the time along the top. We select a matrix because this will automatically group the rows by room (you need to do this anyway otherwise it will look a bit like a staircase of bricks).


4. Design the Matrix:
In other words, put where all the values are going to go. [Time] into "Columns", [Room] into "Rows", and [Status] into "Details".


5. Save and close the report wizard
Pretty much there you should have the following


6. Preview the report
Ta daa! Ok it needs a lot more work in terms of design but the functionality is there.

Adding Events

Again I've put two rooms as examples in the above script. The "Events" table needs as a minimum the 4 fields "Room", "EventStart", "EventFinish", and "Status" ("Tooltip" was for me so that when the user hovers the mouse over the block it will display the details).

I've used a UNION clause which will hopefully make all rooms (even empty ones) display. Note the second select statement (to list all the rooms) has to have the same number of fields as the first statement.

This is the final script for the events table (DO NOT USE THE BELOW: this works for me and the timetabling system I have, you need to adjust how you get the values from your events database). First get the events from your database and then get the rooms:
copyraw
), Events AS (
   --Create some events to place on the calendar
	SELECT
		tt.[RoomId] Room, 
		tt.[StartTime] EventStart', 
		tt.[FinishTime] EventFinish,
		CONVERT(VARCHAR(2), tt.[Status]) AS Status,
		'Details and other information' AS Tooltip
		
	FROM [SLOTDETAILS] sd
	INNER JOIN [TIMETABLE] tt 
		ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
	INNER JOIN [CONTACT] c 
		ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
	INNER JOIN [WEEKMAPNUMERIC] wm 
		ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId])
		
	WHERE sd.[SetId] = @setId
	AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @GivenDate
	AND wm.[WeekNumber] = @weekNumber
	AND tt.[SiteId] = @siteId
	AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'

   --Cycle through all possible rooms and include these in the calendar
	UNION
	SELECT
		rms.RoomId AS Room,
		CONVERT(VARCHAR(8), @StartTime, 108) AS EventStart,
		CONVERT(VARCHAR(8), @StartTime, 108) AS EventFinish,
		'' AS Status,
		'' AS Tooltip
	FROM [ROOMS] rms
	WHERE 
		rms.SiteId=@siteId
	AND
		rms.SetId=@setId
)
  1.  ), Events AS ( 
  2.     --Create some events to place on the calendar 
  3.      SELECT 
  4.          tt.[RoomId] Room, 
  5.          tt.[StartTime] EventStart', 
  6.          tt.[FinishTime] EventFinish, 
  7.          CONVERT(VARCHAR(2), tt.[Status]) AS Status, 
  8.          'Details and other information' AS Tooltip 
  9.   
  10.      FROM [SLOTDETAILS] sd 
  11.      INNER JOIN [TIMETABLE] tt 
  12.          ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] 
  13.      INNER JOIN [CONTACT] c 
  14.          ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] 
  15.      INNER JOIN [WEEKMAPNUMERIC] wm 
  16.          ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId]) 
  17.   
  18.      WHERE sd.[SetId] = @setId 
  19.      AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @GivenDate 
  20.      AND wm.[WeekNumber] = @weekNumber 
  21.      AND tt.[SiteId] = @siteId 
  22.      AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' 
  23.   
  24.     --Cycle through all possible rooms and include these in the calendar 
  25.      UNION 
  26.      SELECT 
  27.          rms.RoomId AS Room, 
  28.          CONVERT(VARCHAR(8), @StartTime, 108) AS EventStart, 
  29.          CONVERT(VARCHAR(8), @StartTime, 108) AS EventFinish, 
  30.          '' AS Status, 
  31.          '' AS Tooltip 
  32.      FROM [ROOMS] rms 
  33.      WHERE 
  34.          rms.SiteId=@siteId 
  35.      AND 
  36.          rms.SetId=@setId 
  37.  ) 
You should be able to provide this statement with a result set similar to the following (if you include a tooltip with loads of info then just picture it at the end of these fields):
copyraw
Time                    Room          EventStart EventFinish Status
----------------------- ------------- ---------- ----------- -----------
2011-03-23 08:00:00.000 Test1 Room    08:00:00   08:00:00    
2011-03-23 08:00:00.000 Test2 Room    08:00:00   09:30:00    2
  1.  Time                    Room          EventStart EventFinish Status 
  2.  ----------------------- ------------- ---------- ----------- ----------- 
  3.  2011-03-23 08:00:00.000 Test1 Room    08:00:00   08:00:00 
  4.  2011-03-23 08:00:00.000 Test2 Room    08:00:00   09:30:00    2 
Note that [Test1 Room] will appear as a row but have no slots booked against it. [Test2 Room] will both appear as a row and have 3 slots taken up (if you left the above to 30 minute slots).

Tweaks

  • Separate the field @GivenDate off into an SSRS parameter (note do not declare it in the SQL code if using as a parameter).

  • You could in the SQL above change the start time (note the end time if you want the end of the day should be "23:30:00" if you have 30 minute slots).

  • Set the columns to stay at the top of the screen (scrolls with you) and left-most column to always be visible.

  • Set StartTime to the minimum possible time on that day. Do the same for EndTime but set it to the maximum time (adds a lot of load)

  • If you are using working hours boolean you could do:
    copyraw
    IF @DisplayHours = 1
    BEGIN
    	--First hour of the day
    	SET @StartTime = @GivenDate + ' 08:00:00';
    	--Last hour of the day
    	SET @EndTime = @GivenDate + ' 17:30:00';
    	--First time of the day (required to display empty rooms)
    END
    ELSE
    BEGIN
    	--First hour of the day
    	SET @StartTime = @GivenDate + ' 00:00:00';
    	--Last hour of the day
    	SET @EndTime = @GivenDate + ' 23:30:00';
    	--First time of the day (required to display empty rooms)
    END
    1.  IF @DisplayHours = 1 
    2.  BEGIN 
    3.      --First hour of the day 
    4.      SET @StartTime = @GivenDate + 08:00:00'
    5.      --Last hour of the day 
    6.      SET @EndTime = @GivenDate + 17:30:00'
    7.      --First time of the day (required to display empty rooms) 
    8.  END 
    9.  ELSE 
    10.  BEGIN 
    11.      --First hour of the day 
    12.      SET @StartTime = @GivenDate + 00:00:00'
    13.      --Last hour of the day 
    14.      SET @EndTime = @GivenDate + 23:30:00'
    15.      --First time of the day (required to display empty rooms) 
    16.  END 


  • Add check to SQL to remove rooms with no name.

  • Show hour in top column only on the hour
    copyraw
    =IIF(MINUTE(Fields!Time.Value)=30, "", Fields!Time.Value)
    1.  =IIF(MINUTE(Fields!Time.Value)=30, "", Fields!Time.Value) 


  • Make the object in the cell a block (which I made big and then didn't allow cell to increase/decrease height). This way when it's printed out (backgrounds won't print by default) the occupied blocks will show up.
    copyraw
    =Switch(Fields!Status.Value="", "#ffffff", Fields!Status.Value="2", "firebrick", Fields!Status.Value="-1", "#999999")
    1.  =Switch(Fields!Status.Value="", "#ffffff", Fields!Status.Value="2", "firebrick", Fields!Status.Value="-1", "#999999") 



One I did earlier: Design


One I did earlier: Preview

I have since made the vertical lines white and the top borders a lighter shade to make a sort of bevel effect.
Category: SQL Server Reporting Services :: Article: 330

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

Joes Word Cloud

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.