SQL Server Reporting Services 2008 introduced several new features. One key feature was the inclusion of the Gauge Controls. I was recently asked to provide a real-world example of the Linear Gauge Control. Since I am always up for a challenge, I delve head deep into the gauge controls. After doing a little reading and testing of the linear control I chose my path. I started with the following report:
The data in the report can be reproduced using the stored procedure provided at the end of the posting. As you can see the report contains Sales data from each country. Looking at the report I noticed, after a little time, that the United States has the largest amount of sales. In the past I would have added a parameter that allowed dynamic sorts based on the Total Sales column, which is not a bad solution. This helps the end-user to quickly identify the BIG winners and losers in regards to sales. However, what about all the countries that fall in between? As a result, I decided to add a linear gauge control that would graphically depict sales as a percentage.
When you initially add the Linear Gauge control to the report, there are several items (ranges, pointers and labels) that are unnecessary.
Therefore before attempting to associate data with the control, I remove three things:
1. LinearPointer2
2. Two right-most Ranges, which are shaded in the lighter two shades of gray
I also hide the labels and any tick marks, which can be done by selecting the entire scale and un-checking the items labeled Show Labels and Show Major Tick Marks.
In then end you are left with a control that looks like this:
The next thing I did was to set the End range at scale value for the remaining Range to 100. Since I am attempting to graphically depict sales as a percentage, comparing the values to 100 should provide a meaningful representation of the data. To calculate the percentages I used the following expression:
=(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100
In the expression I divide the Sales for each country by the total sales for all countries. Note that the sum for all countries is calculated by using the sum function, including the scope value. Finally the value is multiplied by 100 to ensure that the result is between 0 and 100, which is the start and end values of the controls only range (the remaining dark grey bar in the control).
Now the only thing left is to assign the calculated value to LiinearPointer1. To do this, select the orange part of the control only. Then right click, the following dialogue box will appear:
Click Pointer Properties, then the Linear Pointer Properties dialogue box will appear. Choose the expression button next to the textbox labeled value and insert the above calculation. Once complete run the report and you will be presented with a result similar to the following:
As you can see from the above image, an end user can easily identify which country is selling and which is not. If you have any questions or comments concerning this topic please feel free to email me at pleblanc@pragmaticworks.com.
Talk to you soon
Patrick LeBlanc, founder www.sqlscripts.com and www.sqllunch.com
