Designing Bullet Graphs in SSRS – Part 1

Two weeks back, I bought myself the second edition of  Information Dashboard Design, authored by the data visualization guru – Stephen Few. I had read the first edition but was intrigued by the 6 new chapters in the second edition. Hence, I decided to buy it and I must say that I am really happy with my decision. One of the new chapters that particularly struck me is on designing Bullet Graphs, a simple invention that Stephen Few had invented specifically for dashboards. There are a whole lot of posts on how to make the bullet graphs in SSRS, but most of them (if not all) just talk about how to develop a basic bullet graph. That is when I decided to write a blog series on how to design and develop advanced bullet graphs in SSRS. The concepts in this series is heavily borrowed from the book and is going to concentrate on the following topics:- I) How to make Bullet Graphs in SSRS
II) Deciding the Color
III) Multiple points of comparison
IV) Alternatives to bar
V) Differentiating measures that need to be read differently
VI) Positive and negative values
VII) Values within a distribution
VIII) Future Projections Designing Bullet Graphs in SSRS Part 1 I) How to make Bullet Charts in SSRS 1) For this demo, we are using a very simple dataset having Sales (primary measure), Target (comparative measure) and SalesPrevYear (another comparative measure)  as the resultant columns. Also, there are 3 other columns – Low, Medium and High, for denoting the upper limits of the ranges. Drag and drop a Gauge report item from the Toolbox and select the Bullet Graph. SSRS Select Gauge Type 2) Drag and drop Sales and Target into LinearPointer1 and LinearPointer2 respectively. Add pointers It is advisable to set the ToolTip property also for each of the Values. 3) Change the FillColor and FillGradientColor property for LinearPointer1 and LinearPointer2 as Black. Change colors 4) Change the FrameStyle property to None for the gauge. Set frame style to none 5) Right-click on the gauge, select Gauge Panel –> LinearRange1 Properties and then set the Start Range as 0 and End Range as [Sum(Low)] (selected from the dropdown). Set range properties Repeat the process for LinearRange2 (where the Start Range = [Sum(Low)] and End Range = [Sum(Medium)]) and LinearRange3 (where the Start Range = [Sum(Medium)] and End Range = [Sum(High)]). It is also advisable to change the default colors of the ranges (as per the guidelines given in the next section) using the Fill tab as well as set the Border to None. 6) Click on the Scale to set the format string to #,0 and MaximumValue property to =Sum(Fields!High.Value) Set Scale properties It is advisable to set the Font also to something smaller, usually between 8 and10 pt instead of the default 12 pt. 7) Most people developing Bullet Graphs in SSRS stop with the previous step. However, it is very important to resize the graph, as there is a lot of white space around the graph in SSRS (remember, one of the main reasons we use bullet graphs is because it is so space efficient). You can modify that by going to the Gauge properties and setting the X Position, Y Position, Width and Height as shown below. Resize bullet graph to utilize whitespace 8) You are ready to preview the Bullet Graph. Final Bullet Graph II) Designing the color 1) For the primary measure, the color must stand out and hence it is usually kept as Black. However, other colors can also be used (although I tend to exclude red and green) as is shown below. Primary Measure color 2) For the secondary measure, I usually keep it as black or dark grey (if dark grey is being used, care must be taken that it stands out from the background colors of the qualitative ranges). 3) For the background colors of the qualitative ranges, the overarching principle is to vary the colors by intensity rather than hue so that color blind viewers can also make distinctions. The common practice is to use varying intensities of black depending on the number of qualitative ranges (Two: #A6A6A6 and #E6E6E6, Three: #9A9A9A, #C0C0C0 and #E6E6E6, Four: #808080, #A6A6A6, #CDCDCD and #E6E6E6, Five: #808080, #A6A6A6, #CDCDCD, #E6E6E6 and #F6F6F6, although keeping it to around 3 would be optimal as more colors would make the chart difficult to understand). However other colors can also be used like shown below Background color for qualitative ranges III) Multiple Points of Comparison Sometimes there are more than one measures to compare against the primary measures. The bullet graph can accommodate one more measure gracefully by using another symbol. Any more than two comparison measures will complicate and slow perception. Follow the steps below to achieve the same in SSRS:- 1) Click on the scale, right click to bring up the Scale Properties and select the Add Pointer option. Add new Pointer 2) Add SalesPrevYear as LinearPointer3. Add SalesPrevYear 3) The default symbol is a rectangle for the pointer. However, it is better to use a X mark as the second pointer symbol as it is big emough to be noticeable and also we can find the centre of intersection. For that, I took an image of the x mark, uploaded it to the report and named it X. Change the Pointer Image properties – MIMEType as png, Source as Embedded, Value as =”X” and Width as 25. Add image for pointer 4) Preview the bullet graph. Preview bullet graph with 3 Pointers While I was showing this to my colleague Javier Guillen, he gave a feedback that the intersection point of the X symbol will not be visible if it is lesser than the value of the primary measure. For the same reason, I would recommend to change the color of the bar to something other than black and keep the X symbol to the black color. Change color fo primary measure This is it for now in this post, but do come back soon to read Designing Bullet Graphs in SSRS – Part 2  and Designing Bullet Graphs in SSRS – Part 3 for the rest of the topics.

Posted by SQLJason

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.