SSRS tidbit: quick and easy squares to serve as KPIs

Key Performance Indicators are extremely useful for helping to direct a business from an executive perspective. Alternatively, they can act as a quick ‘at a glance’ warning for operational reports too.

KPIs can often take the form of traffic lights, smiley faces and so on. However, what about if you want to go for the ‘minimalist’ approach whereby you just want to show a square, or a colour, or something very simple? This is easy to do in Reporting Services.

To show a square in a cell:

Using the Expression Editor, type in a 0 (ie a zero), highlight it, and then choose ‘wingdings’ as a font in the property settings. It will then come up as a square.

Once it is in the square format, you can do things to make the square convey more information.

For example, you could change the colour of the square, dependent on the value. For example, if you were using a sequential palette, you might want to increase the intensity of the colour dependent on the value. To do this, use the Expression Editor for the ‘font color’ setting and type in an expression similar to the following:

=SWITCH(Fields!RatingCount.Value < 2, “CornflowerBlue”, Fields!RatingCount.Value  “RoyalBlue”,

 Fields!RatingCount.Value >=3, “MediumBlue”

 
Remove the quotation marks


Similarly, if you want to change the size of the square, go to the ‘font size’ property setting and type in the following:

=SWITCH(Fields!RatingCount.Value < 2,

“6pt”, Fields!RatingCount.Value “8pt”, Fields!RatingCount.Value >=3,“10pt”)


In the 17th Century, Thomas Hobbes asserted he had succeeded in squaring the circle. Whilst this facility is interesting, it is important not to go overboard. Have fun with the settings, but not too much fun – you want to be sure that information is conveyed, not drowned out in the noise!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s