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.
