A picture is worth a thousand words. I realized this the other day when I gazed upon an image of the new Alfa Romeo 8C Competizione, ( although I won’t mention which words entered my head at that point ), but anyway, with this in mind, it brings us to Reporting Services 2008, and the new data visualization elements available to us.
Knowing full well that dashboards and scorecards are becoming more and more popular, the team have added Dundas gauges to the mix, and I suspect that this object will be used extensively. ( If you consider the cost of competing reporting tools with features like these, a few more choice words enter the mind ). But how easy is it to use ?
The short answer is VERY. We have 2 types of gauges, radial and linear. Each gauge has a number of sub-areas, as you’ll no doubt notice from the diagram above, but the ones to be interested in initially are the scale and the pointer.
The scale defines what the gauge measures, and by default it is set to a minimum of 0 and a maximum of 100. If you’re using the gauge to display KPI’s , you might want to reset the scale to a range of 0-3 or 0-5.
The pointer is the next thing to focus on. Not because of the possibility changing the physical pointer to a wide range of different types ( and you can ) , but because this is where you link your data to the gauge itself. So lets assume we had the following query in the dataset, running against the Adventure Works database.
|SELECT ST.Name, Sum(SOH.TotalDue) AS Sales|
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesTerritory ST
ON SOH.TerritoryID = ST.TerritoryID
GROUP BY ST.Name
Create a simple matrix of sales per territory ( with Territory as the Row Group, SalesAmount as the data field and no Column group ). It should look like the one below. Remember that although you’re starting off with a matrix, it’s actually a tablix underneath.
1) Right-Click anywhere on the report and select Insert – Gauge.
2) Select the very first Radial gauge from the list. Leave it on the canvas.
3) Right -Click inside the header of the second column and Select Insert Column – Inside Group Right.
4) Drag the gauge object into the new cell. You may have to increase the row height in order to see the gauge clearly.
What needs to be done now, is to calculate what percentage of the overall sales constitutes the sales for a particular territory. To do this we divide the sales for a territory ( in other words the sales for every row ) by the total sales amount. This is achieved by using the following expression :
A simple expression. Select the pointer of the gauge, Right -Click and select Pointer Properties. On the Value field, you can select Fx and input the above expression. Now when you run the report, you will find that the gauge indicates this percentage quite nicely, for example the Southwest Territory was responsible for about 22% of all sales.
This example illustrates how to use the gauge within a tablix, but remember, you don’t have to use it like this. You can use it as a standalone object , but then it has to bind to a single data value. Also, remember that if the data value is greater than the maximum value of the scale, the gauge will read as the maximum possible value of the scale.