Tuesday, 24 November 2009

SSRS gauge custom labels

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

Like so:

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:

=Fields!MyValue.Value

We do so:

=Code.DoubleToConciseValue(Fields!MyValue.Value)

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.

5 comments:

  1. Nice guage, however did you create it using the SSRS Nevron Guage, or the standard SSRS Report Builder 3.0, or another one?

    ReplyDelete
  2. It's just the standard SSRS 2008 gauge - no fancy stuff here!

    ReplyDelete
  3. I know this is an old post, but I wanted to share how I accomplished the same (or similar) a little easier... Scale > Properties > General > Multiply Scale By > Expression... then I used "=iif(Fields!MyValue.Value > 1000000, .000001, .0001)". That handles reducing the number (you could add a nested iif to the expression to accommodate billions). Then, on the Number section of the same Radial Scale Properties, Custom Format > Expression... =iif(Fields!MyValue.Value > 1000000, "$0.0m", "$0.0k") and that handles adding the dollar sign to the front of the number and "m" or "k" after the number.

    ReplyDelete
    Replies
    1. Kudos! I prefer your much more elegant solution.

      Delete
    2. Thank you, and thanks for your help... it was your post that helped me wrap my head around how to tackle it. It was dumb luck that I figured out the conditional multiplier. :) Keep up the great work!

      Delete