How to produce a Gregorian Calendar in SSRS like is shown in the image

Question

How can I produce in SSRS this Gregorian calendar (to choose a start date or an end date for a filter like is shown in the picture)? Should I use a gauge or something else?

There's not a chart or other object in SSRS to do this easily. It's probably a bit of work. Your days of the month would need a row number with the 1st starting at the Day of the week (if the 1st is a Tuesday, the row_number should start at 3). I would use a **Matrix** for the calendar with `=CINT(Fields!Row_Number.Value) / 7` for the column grouping and `=Fields!Row_Number.Value MOD 7` for the row group. Hope that gets you started if you want to tackle it. — Hannover Fist, Dec 22 '18 at 00:20

score 0 · Answer 1 · edited Dec 23 '18 at 20:02

first get your calendar data into a workable format:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '12/01/2018'
SET @EndDate = '01/31/2019'

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
 )
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,
case when month(d.Date) = Month then '1' else '2' end as ID
FROM
 Dates d

Next arrange you report in design view, you need to create three matrix each placed within each other:

The larger matrix is grouped by row grouped on "=DisplayOnCalender" the medium sized matrix is grouped by row grouped on "=Ceiling(Fields!Order.Value/7)" and column grouped on "=Weekday".

The smaller matrix contains the expression =IIF(Fields!ID.Value = 1, Fields!Day.Value, "")

When you have created the two matrix, the dark green background rows can be deleted they give information for the adjacent field expressions. drag the smallest matrix into the red box. then drag these two combined matrix into the yellow box.

Now your report design should look like the figure on the right:

How to produce a Gregorian Calendar in SSRS like is shown in the image

1 Answers1