Well, moving on from the Sparkline shape in my previous post, this time I thought I’d look at how to go about building a Bullet Graph using Visio. To quote its inventor, Stephen Few, a Bullet Graph “is designed to display a key measure, along with a comparative measure and qualitative ranges to instantly declare if the measure is good, bad or in some other state.”
If you want more details on Bullet Graphs or dashboard design in general then I recommend you buy his book “Information Dashboard Design”. For this post I’m going to concentrate on how to build the shape itself…
If you just want to download the shape, then here it is (Note – this shape uses Visio 2007 ShapeSheet functions so you may need to play around with the colour formulae in particular to get it to work in 2003):
Shape functionality and structure
As with the Sparkline example, this shape is group shape containing a number of child shapes. The main elements can be seen as follows:
The visibility of the label, scale markers and target marker can all be toggled via shape’s context menu and shape data window. The user can also either opt to have an automatic scale (which is the range divided by five, as above) or to set their own custom scale which can take up to five strings.
It’s probably easier to see how the shape is constructed if we break it apart. In the following image you can see that the top shape is the parent group shape and this contains a two geometry sections to display the graph value bar or dot (not shown). Subsequent child shapes sit underneath the parent and hold the target marker and three zone shapes.
It terms of text, the parent shape holds the main label while a further five shapes (not shown above) sit right at the back to hold the five scale points and respective tick geometries.
Shape mechanics
Text – Text scaling works in a similar manner to the Sparkline shape, with the scale markers font size linked to that of the main shape via a user cell factor (User.ScaleToLabelFontRatio). This can be changed as required.
Label position – The label’s position can be set to Left, Top, Bottom or Right via the shape data window or a custom placement using the yellow control handle, to which the shape’s text block position is tied. This works by having a user cell (User.LabelPositionTrigger) monitor the selected position (User.LabelPositionIdx) and when this changes, new coordinates are pushed into the control handle’s X/Y cells. The formula is as follows:
=DEPENDSON(User.LabelPositionIdx)+SETF(GetRef(Controls.Row_1),IF(OR(User.LabelPositionIdx=1,User.LabelPositionIdx=3),Width*0.5,IF(User.LabelPositionIdx=0,-(TxtWidth/2),Width+(TxtWidth/2))))+SETF(GetRef(Controls.Row_1.Y),IF(OR(User.LabelPositionIdx=0,User.LabelPositionIdx=2),Height*0.5,IF(User.LabelPositionIdx=1,Height+(TxtHeight/2),-((TxtHeight/2)+User.ScaleTickLength+User.ScaleTextHeight))))
If that gives you eye strain, then I apologise. I’m not really expecting you to read through this formula, but I’m showing it as it highlights some rules that I try and follow when dealing with complex ShapeSheet formulae and debugging:
- Try to break up long formulae by referencing intermediate user cells. In the above example, I could potentially have combined the last two cell references into a new one called ScaleHeight.
- Use meaningful cell names. As with normal coding, there is a balance to be struck between overlong verbose names and abbreviated terse naming. On the one hand you extend your formulae so that you can’t view it without scrolling and on the other, when you return to your code in the future, you’ll have trouble remembering what all those abbreviations actually meant.
- Use Notepad to construct and breakdown formulae. I find it much easier, when trying to understand what a very long formula is doing by adding line breaks in Notepad at appropriate points and then removing them again before pasting the formula back into Visio. Here’s an image of what I’m talking about:
A last point about this formula is that you’ll notice that I’ve highlighted the ‘DEPENDSON(User.LabelPositionIdx)+’ part in blue. The reason is just to note that it’s not really required. The DEPENDSON() ShapeSheet function creates a dependency on another (target) cell that wouldn’t ordinarily exist. One use of this function might be to push an actual formula (rather than a value) into another cell, and to do this you’d have to put the formula that you want to push, into a string. This removes the dependency and so you would need to use the function to create it. In the above case I’m using a calculated value, based on User.LabelPositionIdx, and so whenever this cell changes the formula would be evaluated anyway. You could take away the DEPENDSON() function and the remaining formula would work perfectly well without it.
Colour – The Bullet Graph shape is designed to use a monochromatic colour scheme. As Few points out, this makes it easy to see if you are colour blind and of course for black and white printing.
I’ve added a number of user cells to deal with the shape’s colour, with the main colour being applied to the value bar and target marker, and Zones 1 – 3 referencing cells that generate a tint of that main colour.
The TINT() ShapeSheet function increases a colour’s luminosity or lightness in a range of 0 to 240. Here is an image of five shapes that were originally set to blue 'RGB-0,0,255' and then had their luminance value adjusted to the respective point:
The TINT() function, along with its SHADE() counterpart (which adjusts the luminosity value down, ie towards black) are very handy but require a little extra thought. Out of the box the TINT() function works perfectly with colours that have a low luminance value (ie dark colours), but if you start off with a light colour, for example, with a luminance value of 180:
…you only have a range of about 40 or 50 points before you end up with a white colour, and this means that you can't just increase the luminace by a set value. My answer to this problem, is to test the incoming main colour and if its luminance is greater than 160 (you can pick your own arbitrary figure), then reset it to 70 (ie a darker value). The result should be that for darker colours, you just work with that colour and take three tints of it, and for lighter ones, you create a darker set of tints. This ensures that there is reasonable contrast between the value markers and three zones in both dark and light cases. Here’s an example:
The formula that achieves this luminosity coercion is the one in User.BaseZoneColor:
=IF(LUM(FillForegnd)>160,
FillForegnd)
Conclusion
Well I’ve tried to stay as close to Stephen Few’s design as possible, but you can easily extend the shape, for example, by adding additional zone shapes and scale markers etc. In any case it’s a great shape to pick through with Drawing Explorer and Formula Tracing window to understand Visio a little better.
Further reading
If you interested in more Visio colour related information, then take a look at these links:
