Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Linear Gauge Control: A Practical Use

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:

clip_image002

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.

clip_image004

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.

clip_image006

In then end you are left with a control that looks like this:

clip_image008

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:

clip_image010

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

Comments

Posted by Steve Jones on 1 December 2009

Nice example. I'm not a huge fan of these gauges for visualizing data, but many people seem to like them, or at least they're in demos.

If you didn't have the numbers, could you get a tool tip like popup from a hover of the value?

Posted by Patrick LeBlanc on 1 December 2009

I searched for a tooltip option, but I couldn't find anything.  

Posted by Mark Underhill on 2 December 2009

Surely a bar or column graph would do the same much more simply, or am I missing something?

Posted by Patrick LeBlanc on 2 December 2009

This is just a different approach.  Even further, this provides the developer with a method of delivering a graphical depiction of the data with additional in row data that does not relate to the graph.  This is all accomplished without a graph consuming all of the report real estate.  

Posted by Jerry OLoughlin on 2 December 2009

One question, if the bar is a percent of total sales, why does the United States look like it's at 100% ?

Posted by Patrick LeBlanc on 2 December 2009

Great catch Jerry!  The End range at scale value on Range in the Gauge was not set correctly.  I updated the image.  Thanks

Posted by Simon Hall on 2 December 2009

What if total sales are zero? Does it cope with divide by zero gracefully?

Cheers

Posted by Anthony Kirkland on 2 December 2009

Based on the example above, the bullet guage has been used to create an in-cell bar chart. These guages would normally be used to display performance against targets. To me they come into their own only when you do use the ranges and linear pointers.

As an example, to extend the design above, the US has a population roughly 10-15 times the size of Australia and 4-5 times the size of the UK, set targets like 50% of total sales for the US, 5% for Australia, 15% for UK, etc. A linear pointer of type "marker" could be used to display this and then we can compare the actual percentage of total Sales against the targets to see which countries are performing well and which aren't. The ranges just add more context of what is good, average and poor performance.

Also tool tipping is available on each component within the guage, ie: the pointers and the ranges and the tick marks if you want.

Posted by Patrick LeBlanc on 2 December 2009

Shall, I did not include this in the initial solution, but you could wrap the calculation in an IIF, like this:

IIF(SUM(Fields!TotalSales.Value,"Tablix1"))*,(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100,0), returning zero in the event that the sum is 0.  I would also add an expression on the color of the LinearPointer, changing the color to something other than orange when the value is 0.

Posted by Patrick LeBlanc on 2 December 2009

Thanks for the tips Anthony.

Posted by Tom Garth on 4 December 2009

Finally! Something to make me like BIS 2008. Thanks for the cool tip Patrick.

Tom Garth

Posted by David on 8 December 2009

It's a decent example..but kind of like using a racecar to get the groceries. It's overkill for what a linear guage is used for. The depiction here is an inline databar, which has been available since RS2005. Simply drop a bar chart into the tablix cell and you have the same ouput as above--per row.

A linear gauge is typically used as a KPI. So you'll have the value to present, as well as a visual indicator of where that falls on/off target...think of a thermometer. You might have a value for temperature...and a value in red to depict when you should call the doctor. The graph not only tells you data point, but relation to a standard.

For inline databars, the callenge has always been like this one, the chart is to the right or left of the data. What I've always wanted to do is something like Excel which depicts the value ON TOP of the datbar so the value and visualization are together.

If you want an example of that, I have that as part of the "Top Reports" report in SQLScrubs on codeplex: http://scrubs.codeplex.com

Or check out the screenshots here, it is the fourth report down:

www.summitcloud.com/.../ssrs-report-catalog.aspx

Posted by Cedric on 15 July 2010

Hi Partick thank you for the example .I just have a comment in general which is a bit of a pet hate ,across the intenet you will find articles that mention ssrs 2008 ,but in fact it is the ssrs 2008 r2 release

,reading thru your article I only realized that your refering to SSRS 2008 R2 and not SSRS 2008 when I saw your expression "=(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100" and you make use of the tablix which is only prevelant in SSRS 2008 and not R2

It creates quite a bit of confusion on some articles across the web

Posted by Cedric on 15 July 2010

Sorry typo from my side, the tablix is prevelant in SSRS 2008 R2 and not SSRS 2008

Leave a Comment

Please register or log in to leave a comment.