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.