Calendar In Sql Server Reporting Services.

In SSRS there is no any option by which we can create the calender in the reports but this thing can be done using SQL-Server here I am giving a example code of creating a calender for a month

For this we have to write the some sql queries in SSRS Data Source

The query is as follows :

--First day of current month

DECLARE @StartDate datetime = '01/01/2011'

DECLARE @EndDate datetime= '01/31/2011'

SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))

DECLARE @CurrentMonth VARCHAR(50)

SET @CurrentMonth = CAST(DATENAME(MONTH, @StartDate) AS VARCHAR)+' '+ CAST(DATENAME(YEAR, @StartDate) AS VARCHAR)

--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,@EndDate)+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 caluclation 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])

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)

After writing this query we have to follow these steps :

1. Right Click on the report page and select matrix table.
2. Right Click on the row group property of this table and give group on value as “=Ceiling(Fields!RowOrder.Value / 7)”
3. Give the width for first column as “0.0inch” and set the visible property of that column as false.
4. For second column right click on Column group property and givr group on value as = “=Fields!WeekDay.Value”
5. Now For the First row in text box write the following code
   =IIF(Fields!WeekDay.Value = 1,"Sunday",
   IIF(Fields!WeekDay.Value = 2,"Monday",
   IIF(Fields!WeekDay.Value = 3,"Tuesday",
   IIF(Fields!WeekDay.Value = 4,"Wednesday",
   IIF(Fields!WeekDay.Value = 5,"Thrusday",
   IIF(Fields!WeekDay.Value = 6,"Friday","Saturday"))))))
6. In second row write the following Code :
   =Fields!Day.Value
   
   
   Now Run the report.