Month: August 2013

Designing Bullet Graphs in SSRS – Part 2

Designing Bullet Graphs in SSRS – Part 2

August 14, 2013

The concepts in this 3 part blog series is 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 Designing Bullet Graphs in SSRS Part 2 In my previous post (Designing Bullet Graphs in SSRS – Part 1), I started off with how to make a bullet graph in SSRS and lightly touched upon some design related topics. This post will carry on from where I left. IV) Alternatives to Bar The primary measure of the Bullet Graph is typically depicted as a bar. Whenever we use a bar to encode a quantitative value, the scale should start at zero as per visualization best practices. For accurate comparisons, it is sometimes useful to narrow the quantitative scale of the bullet graph, which means not starting the scale at zero (for eg, in cases where we know that the values is always going to be between a particular range). In such cases, we can use a mark to encode the primary measure instead of the bar. Follow the steps below to reproduce the same:- First, let us see how a completion rate of 97.5% would look in a bullet graph against a target of 98 where the completion rate (primary measure) is visualized as a bar. Normal Bullet Graph You can see that it is very hard to notice the difference. However, see the difference below when we visualized the bar as a marker. Bullet Graph with Marker instead of Bar 1) To achieve the results, change the bar to a X symbol using the same technique as discussed in the previous post (see Multiple Points of Comparison) Pointer properties 2) Also change the scale properties as appropriate by using expressions (for this example, I hardcoded it as between 90 and 100, however the best practice is to use expressions to make it dynamic). Change Scale Properties V) Differentiating Measures that need to be read differently Consider the chart below where the Revenue and Expenses are placed next to each other. These measures need to be read differently as we know that having more of Revenue and less of Expense is good. The background colors help in a way to distinguish this as we can see that the light colors (which denotes good) are there at the end for Revenue and at the beginning for Expenses. Expenses and Revenue visualized as normal Bullet Graphs However, a better way to differentiate them would be to display them as below:- Reversing the scale direction for Expense Now straightaway we can see that Expenses is to be read differently than Revenue by the direction of the scale. To do this, follow the steps below:- 1) Right Click on the scale and select Scale Properties. Select Scale Properties 2) Check the tick box to Reverse Scale Direction. Reverse Scale Direction Now we should be able to get the desired result. VI) Positive and Negative Measures Bullet graphs which display both positive and negative values are especially useful when we try to compare variances between 2 measures (E.g., Sales vs. Target for customers). image To achieve the same, follow the steps below:- 1) For this demo, I am using a dataset that returns Customers, Sales, Target and the Low, Medium, High values of the qualitative ranges image 2) Drag and drop a matrix to the report layout. Now, add Customer to the Rows and a bullet graph to the column as shown below. Also add Sales as LinearPointer1, 0 as LinearPointer2 and Target as LinearPointer3. image I also made some modifications like changing the bar color, reducing the width & height of LinearPointer2 and changing the color to Gray (as it is supposed to be the 0 line and hence need to be differentiated). 3) I also changed the max and min value of the scale to 30 & -30 respectively. Scale properties 4) The 3 ranges are also changed as 0 – Low, Low to Medium and Medium to high. LinearRange1. Do for the other 2 ranges also Now we should get the desired results. The rest of the topics will be covered in my next post – Designing Bullet Graphs in SSRS – Part 3.

Posted by SQLJason, 0 comments
Designing Bullet Graphs in SSRS – Part 1

Designing Bullet Graphs in SSRS – Part 1

August 13, 2013

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, 0 comments