Designing Bullet Graphs in SSRS – Part 3

The concepts in this 3 part blog series are heavily borrowed from the book Information Dashboard Design, authored by the data visualization guru – Stephen Few 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 In my previous post (Designing Bullet Graphs in SSRS – Part 2), I showed how to make alternatives to bar, how to differentiate measures and how to show positive and negative values in bullet graphs developed in SSRS. This post will touch upon the final 2 topics. VII) Values within a distribution Sometimes the value displayed in a bullet graph needs to be compared to the distribution of an entire set of values. Lets say, a teacher needs to see how individual students performed with respect to the class average. For that, I have made a sample dataset shown below L1, L2, L3 and L4 are limits of the three ranges, Score is the score for that student, Avg is the average for that Subject in the class. Now let’s see how to make bullet charts for the data shown above. 1) Drag and drop a table in the report layout. Put Subject field in the Rows and then put a bullet chart in the data field. Select Score as the LinearPointer1 and Avg as LinearPointer2. 2) Add 3 ranges as shown in step 5 of How to make Bullet Graphs in SSRS – Designing Bullet Graphs in SSRS – Part 1. The upper and lower limits should be L1 & L2, L2 & L3 and L3 & L4 respectively for the three ranges. 3) Change the Scale properties to a minimum of 40 and maximum of 100 (we are hardcoding it now but ideally you should use expressions). Also change the Number format to #,00 instead of percentage. 4) Change the bar to a cross mark as per the technique shown in Alternatives to Bar – Designing Bullet Charts in SSRS – Part 2. Now the result should be similar to the image shown below 5) Add 2 more ranges so that the empty spaces (for eg, 40 – 60 for English) can be filled. For that, right click on the scale and then select the Add Range option. 6) Select the Start range as 40 and End range as L1. Set the Placement relative to scale option to Cross. Also, change the Fill color to No color. Repeat the same for the second range but set the Start range as L4 and End range as 100. Now, with a bit of the formatting techniques described in the previous posts, you should get the desired result. VIII) Future Projection With the normal bullet chart, it is easy to compare a measure with another measure, say Sales against Target. However, there are scenarios where you would want to track a measure against a future target, say YTD Sales against a yearly target. In such scenarios, it would be better to project the measure and then display it in a bullet chart as shown below In the above bullet chart, you can see that the Sales is 65, Target is 245 and Projected is 270. But more importantly, you get an idea that the current sales is on track to meet the target. For creating this type of bullet chart, I am using the following sample dataset Follow the steps below to achieve the same:- 1) Make a bullet chart with Projected as Pointer1 and Target as Pointer2. Change the color for the LinearPointer1 to #608fc7 (or whatever you prefer). Also, set the scale properties and ranges appropriately. I used 0 to L1, L1 to L2 and L2 to L3 as the Start Range and End Range for the three ranges. 2) Now right click on the scale and add a third pointer. 3) Click on the properties for LinearPointer3 and select the value as Actual. Also change the color appropriately. Ensure that he Type and Width properties are set as shown in the image below (so that the marker is a bar instead of the default rectangle). Now we should be able to see the desired graph on previewing the report. With this, we have come to the end of the three blog series on bullet charts. Hopefully, this will come in handy when you are faced with a design related questions while developing bullet charts in SSRS.