Note: I have posted an updated article on this topic here.
Sometimes it is easier to visualize the activity related information in a calendar view. For example, you may want to view the number of telephone calls and emails each day by department and/or salesperson during the month in a calendar view (rather than as a tabular report).
You can create a calendar view report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.
You would first create a temporary table with a row for each day of the month. Each row has the following columns:
Day of the month: Use this to display the date in each cell of the matrix.
Week of the month: Use this as a row header of the matrix (you can hide this field in the actual report because it is not needed for the calendar view)
Day of the week: Use this as a column header of the matrix.
Activity count (or whatever data you want to aggregate for each day): Use this as a cell data element of the matrix.
You can calculate the day of the week and week of the month in SQL as follows:
DECLARE @d_date DATETIME,
@d_dow SMALLINT,
@d_weeknum SMALLINT,
@d_dow1 SMALLINT
SET @d_dow = DATEPART(dw, @d_date)
By default Sunday is the first day of the week.
SET @d_date = @mnthbeg_date
SET @d_dow1 = DATEPART(dw, @d_date)
SET @d_weeknum = ((@d_dow1 + DATEPART(d, @d_date) - 2) / 7) + 1
As you will notice from the above SQL statements, @d_dow1 is the day (Sunday through Saturday) on which the first day of the month falls.
Once you have this temporary table populated, you can simply select it and use it as a dataset for the matrix.
You can further enhance the report using the following default filters:
Month: Default to current month using =Month(Now)
Year: Default to current year using =Year(Now)
Salesperson/User: You can populate this list with the dataset from SystemUser filtered view. You can also default the user to the current CRM user using the CRM function dbo.fn_FindUserGuid(). If you are creating this report for a non-CRM application, you can simply use the User!UserID global variable to get the current Windows user.
In addition, you can create a drill-down report that displays the details of activities when the user clicks on a specific date in the report.
Sometimes it is easier to visualize the activity related information in a calendar view. For example, you may want to view the number of telephone calls and emails each day by department and/or salesperson during the month in a calendar view (rather than as a tabular report).
You can create a calendar view report in SQL Reporting Services using a matrix type data region. The matrix will have weeks of the month as rows and days of the week as columns.
You would first create a temporary table with a row for each day of the month. Each row has the following columns:
Day of the month: Use this to display the date in each cell of the matrix.
Week of the month: Use this as a row header of the matrix (you can hide this field in the actual report because it is not needed for the calendar view)
Day of the week: Use this as a column header of the matrix.
Activity count (or whatever data you want to aggregate for each day): Use this as a cell data element of the matrix.
You can calculate the day of the week and week of the month in SQL as follows:
DECLARE @d_date DATETIME,
@d_dow SMALLINT,
@d_weeknum SMALLINT,
@d_dow1 SMALLINT
SET @d_dow = DATEPART(dw, @d_date)
By default Sunday is the first day of the week.
SET @d_date = @mnthbeg_date
SET @d_dow1 = DATEPART(dw, @d_date)
SET @d_weeknum = ((@d_dow1 + DATEPART(d, @d_date) - 2) / 7) + 1
As you will notice from the above SQL statements, @d_dow1 is the day (Sunday through Saturday) on which the first day of the month falls.
Once you have this temporary table populated, you can simply select it and use it as a dataset for the matrix.
You can further enhance the report using the following default filters:
Month: Default to current month using =Month(Now)
Year: Default to current year using =Year(Now)
Salesperson/User: You can populate this list with the dataset from SystemUser filtered view. You can also default the user to the current CRM user using the CRM function dbo.fn_FindUserGuid(). If you are creating this report for a non-CRM application, you can simply use the User!UserID global variable to get the current Windows user.
In addition, you can create a drill-down report that displays the details of activities when the user clicks on a specific date in the report.
