Create Calendar style report with Gauge Indicator using SQL Server Reporting Services

In the previous post, I described the steps to create a calendar view report using a matrix type data region. This works well for many operational scenarios described in the article. The report provides detailed numerical information such as billable hours, number of phone calls made, and Internet sales on the e-Commerce web site that can be useful for department level and mid-level managers and supervisors.

However, this report may include too much detailed information for higher level managers and executives. They may want to see the exception based report that highlights the numbers that do not meet a certain target or standard.

Once you have created a matrix report described in the previous post, it is quite easy to convert it to a graphical report using the gauge component available in SSRS.

Simply drag the Gauge control on the top of the field in the matrix that you want to report on.

If you right click on the control, click Indicator Properties and select the Value and States tab, you will see the following window.

Let us review the properties in this window.

Value: This defaults to the underlying field used to evaluate the indicator.

States Measurement Unit: This can be a percentage or numeric i.e. the absolute value of the field above. You may also use a function to calculate this value. It defaults to Percentage. I will explain how the Percentage is calculated below.

Synchronization scope: In this case, it defaults to the entire matrix of which the cell is part of.

Minimum and Maximum: Both of them default to “Auto”. In other words, it uses the minimum and maximum values from the entire matrix for the purpose of calculation.

Indicator states: By default, you get 3 states representing 3 different color icons. You can click “Add” to add more states or “Delete” to delete a state.

Now, let us look at how the indicator is evaluated. SSRS first finds the minimum and maximum values in the matrix. It then divides the range i.e. the difference between the minimum and maximum, using the Start and End values in the Indicator states table above. So, using the default numbers in the table above, it divides the range into bottom 33%, 33% to 66% and 66% to 100% and then calculates the target sales numbers to be used for evaluating the indicators. In the example for June 2007 below, the minimum number for the month is $9,130 and maximum number is $31,241. The range is $22,111 i.e. the difference between the minimum and maximum. Based on this range, the states are assigned as follows:

$9,130 to $16,426.63: Red

$16,426.64 to $23,723.26: Yellow

$23,723.27 to $31,241: Green

I used the default states and start and end values. However, you can change the values based on your business needs. For example, you may want to include bottom 20% of the values in the “red” state and top 20% of the values in the “green” state.

If you have the budgets or sales quota stored in the database, you can use this data for states and start and end values. You will need to use the expression to populate these values.

You may create a drill-down report if the manager is interested in viewing underlying data that makes up this indicator. You can configure this on the Action tab of the Indicator Properties window.

The report definition (RDL) file for this report is available here.

Create Calendar style reports in SQL Reporting Services

Sometimes it is easier to visualize the activity related information in a calendar view. Here are a few scenarios where Calendar style report provides a better view of information than a standard table or list.

- number of outbound telephone calls by salesperson or department in a telemarketing company

- billable and non-billable hours by consultant/department in a consulting company

- Internet sales on the e-Commerce web site

You can create a calendar style 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.

In this article, I will explain the steps required to create such a report from AdventureWorksDW database (available for download on Codeplex). I have also provided a link to download the report definition (RDL) file below.

Here are the key points to note for creating a calendar style report using SSRS.

1. Dates or Calendar table: We will use this table to include date, week number and day of the week information in the data set. This table contains all dates in each calendar year you will be reporting for. The AdventureWorksDW has the DimDate table that contains this information. If you do not have such as table in your database, you can create a temp table and use it in the query.

2. Outer Join: We will need to use the Outer Join between the dates table and the fact table so that all dates are included in the data set whether there is data or activity for that date or not.

3. Week Number: We will need this field to generate the matrix. This field has erroneous data in the AdventureWorksDW database. Therefore, I had to create a calculated field to calculate this value. Please see below for details.

4. Use Matrix to create the Calendar view. The Matrix has the WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. These fields are used only for grouping and should be hidden in the final report design. The WeekNumber can be any number from 1 through 53. The DayNumberOfWeek can be any number from 1 through 7 – 1 being Sunday and 7 Saturday.

5. The matrix can have from 4 to 6 rows depending on the number of weeks in the month and the day on which the month starts. It always has 7 columns – one for each day of the week.

The screen shot below shows the query used for the data set used in the matrix. Note the LEFT OUTER JOIN between DimDate and FactInternetSales tables.

As mentioned earlier, the week number field in the DimDate table in AdventureWorksDW has erroneous data. Therefore, I created a calculated field WeekNumber as follows:

=DatePart(DateInterval.WeekOfYear, CDate(Fields!FullDateAlternateKey.Value))

The following screen shot shows the report in the design mode. As you will notice, the matrix uses WeekNumber as the Row Group and DayNumberOfWeek as the Column Group. EnglishDayNameOfWeek is used to display the days – Sunday through Saturday.

The following screen shot shows the sample output for the month of June 2007. This month has 5 rows representing 5 weeks and 7 columns representing Sunday through Saturday.

The following screen shot shows the sample output for the month of September 2007. Note that this month has 6 rows representing 6 weeks.

The report definition (RDL) file is available for download here.

Please also see the related post to display gauge indicator on the calendar style report.

Create Calendar View in SQL Reporting Services

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.