} Dynamics CRM: Service Calendar that shows all activities | Dynamica Labs - Blog
Loading
×
Get your free qoute by filling in the form
Submit
×
Get your free qoute by filling in the form
Submit
×
Get your free qoute by filling in the form
Submit
10 Feb 2012

Dynamics CRM: Service Calendar that shows all activities

Dynamics CRM Service Calendar

Have you ever wondered why Dynamics CRM Service calendar doesn’t show all CRM activities but only Appointments and Service Activities?

Here is the report that can help you a lot when you plan your tasks, phone calls and etc.
Link to the file

The source code:

–DECLARE @StartDate DATETIME, @EndDate DATETIME
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
),
ActivityName AS
(
SELECT
scheduledend,
[Day] = DATEPART(DAY, scheduledend),
[Month] = DATEPART(MONTH, scheduledend),
[Year] = DATEPART(YEAR, scheduledend),
Subject,
Activityid,
Activitytypecode
FROM
FilteredActivityPointer AS CRMAF_FilteredActivityPointer
)
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,
aname.Subject,
aname.activityid,
aname.activitytypecode
FROM
Dates d
LEFT JOIN ActivityName aname ON aname.Year = DATEPART(YEAR,d.[Date]) AND aname.Month = DATEPART(MONTH,d.[Date]) AND aname.Day = DATEPART(DAY,d.[Date])
OPTION (MAXRECURSION 1000)

I used the idea by Ryan Duclos – and updated the report to work correctly with Dynamics CRM

SSRS 2008 Generate Calendar(s) based on a date range « Ryan Duclos

Comments (0)