I’m not an artistic person – hardly the best attribute for a sometime report designer, so most of my dashboard efforts are primarily functional and secondarily aesthetic. For this reason I have thus far avoided the gauge control on the grounds that if something business critical (e.g. expenditure exceeding income) needs to be spelt out using pretty colours and dials then the consumer of that information probably shouldn’t be in a decision making capacity. Back in reality, where the world does not revolve around my dogmatic prejudices and the fact is that gauges are a very useful way of indicating critical success factors in a business without frying the mind of the busy executive.
So after years of avoidance I found myself having to use these dreaded controls in a SSRS 2008 project against an SSAS 2008 data source. All I had to do was display cost v income – 2 measures. This should be simple thought I foolishly only to be still working on the same task 2 days later. Much of this time was spent getting the range of the gauges to be dynamic according to the upper and lower limits of income and expenditure – but one of my biggest bugbears was displaying anything other than numbers on the scale.
Basically, I wanted numbers to be displayed in the most concise format possible so that very large values don’t crowd the scale - for example:
2500 should be displayed as 2.5k
1,000,000 should be displayed as 1m
1,000,000,000 should be displayed as 1bn
First thing I did was create an SSRS (VB) function that converted a double value to this concise representation:
Public Shared Function DoubleToConcise(valueToConvert As Double) As String Dim negator As Double = 1 Dim value As Double = Math.Abs(valueToConvert) If valueToConvert < 0 Then negator = -1 If value >= 1000 Then If value < 10000 Then Return ((value/1000)*negator).ToString() & “k” Else If value < 100000000 Then Return ((value/1000000)*negator).ToString() & “m” Else Return ((value/1000000000)*negator).ToString() & “bn” End If End If End If Return (value*negator).ToString() End Function(Note the use of the negator value and Abs function to save having to include negative integer ranges in the If statements) The only problem with this function is that the gauge still expects to be given (and display) numerical ranges, which means I’d have to return a double (rather than a string) value – which is fine except for the fact that 100,000 is now being shown as 0.10 with no qualifying suffix. After much gnashing of teeth and wailing I realised the solution was actually quite simple – basically split the function into two:
- one to return the numeric value
- another to return the suffix
Public Shared Function DoubleToConciseValue(valueToConvert As Double) As Double Dim negator As Double = 1 Dim value As Double = Math.Abs(valueToConvert) If valueToConvert < 0 Then negator = -1 If value >= 1000 Then If value < 10000 Then Return (value/1000)*negator Else If value < 100000000 Then Return (value/1000000)*negator Else Return (value/1000000000)*negator End If End If End If Return value*negator End Function Public Shared Function DoubleToConciseFormat(valueToConvert As Double) As String Dim value As Double = Math.Abs(valueToConvert) If value < 1000 Then return String.Empty Else If value < 10000 Then Return "k" Else If value < 100000000 Then Return "m" Else Return "bn" End If End If End If End Function
We then apply the DoubleToConciseValue function to all values passed into the gauge (pointer, scale limits, etc) so instead of:
We do so:
But what about the suffix on the scale? This is where that old chestnut the format string comes in. Basically we can pass literal values into format strings and they are displayed verbatim – e.g. applying the format string “0.00hello” to a value of 156 would give us “156.00hello”. So we put the following formula in the FormatString property of the scale’s LabelStyle property:
=”£0.00” & Code.DoubleToConciseFormat(Fields!MyValue.Value)
Which gives us the finished gauge:
There are limitations to this approach in that it pretty much requires that at least some of the output is numerical, although even this limitation could be overcome with innovative use of IFormatProvider in a custom assembly.