Room Availability Calendar in Business Intelligence Development Studio

: Joel Lipman; SQL Server Reporting Services; 23 March 2011; Hits: 11799

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:

copy raw

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)

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

copy raw

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

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

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

copy raw

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

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

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:
copyrawstyler
```
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
copyrawstyler
```
=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.
copyrawstyler
```
=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")