Category: Data Visualization

This post is about a visualization I created to show test execution status with related defects using data obtained from HP Quality Center. I’m using Excel to create this chart and deliberately stayed away from “fancy tricks.” If you want to recreate this some steps will be different if you don’t use Quality Center or Excel. In that case, you get to figure it out.

My weekly status meeting drove me to create this chart. Every week, I sit in this meeting with some pretty important people. Whenever I’m in a testing phase, I have to show what it is I’ve been working on. Previously, I’ve used the report templates from Quality Center, but they really are crap. Wait, that’s not big enough…They really are C-R-A-P. Not only is it difficult to jostle the correct data into place, but they are ugly and none of my superiors actually trusts the Quality Center visual. Since I’ve been doing all this reading about data viz, I realized that I could easily create something much better using Excel in less time.

There are several steps to creating any visualization, and I’ll break down the creation of this chart into the following steps. They are from Ben Fry’s excellent book, Visualizing Data: Exploring and Explaining Data with the Processing Environment.
Acquire – getting your data
Parse – structuring, and sorting your data
Filter – remove stuff you don’t need from your data
Mine – apply mathemagic in the form of statistics, data mining, whatever to show patterns in your data
Represent – choose the type of graph or chart you will be using
Refine – polish your chart so that it has clarity and makes people want to look at it
Interact – add ways for the user to explore your chart

Acquire
Looking at your test set in quality center’s test lab, right click, and QC will show you an option at the bottom for “save as.” Click and this and choose excel sheet. Html would work too since excel can parse through QC’s html.

Parse
For this chart, I like to create 3 groupings: passed, failed and no run. You can have other groupings, but you probably want to make sure that the “other” groupings are together. In Excel, I sort the data by status into the 3 groups. If you are using a test set from the past, you might only have 2 groupings.

Filter
This will change depending on how large your team is and how the work is divided. Since I’m the only tester, we all know who executed the tests in my chart. I remove the columns for Attachment, Planned Host, Responsible Tester, Execution Date, Time and Subject. This leaves only 2 columns, Test Name and Status.

Mine
No mining in this one, it’s just straight up data.

Represent
This chart will be using a stacked bar format which looks similar to a stemplot, but isn’t really a stemplot.

Refine
Here’s where the magic is really happening. See that column for status? Add a column between “Test Name” and “Status.” Each cell in the new column gets a color according to that test’s status: Passed=green, Failed=red, No Run and anything else is gray. If you use the basic colors in Excel, your colors will be too bright. In Excel 2003 you can change this by navigating to Tools>Options>Colors. Here you can choose some less saturated versions of red and green. You’ll still need the very bright red, so don’t save another color over it.

Use fill color to change the colors in the empty column next to the status column. Now you can see each test and you can see how much is passed and how much is failed. Since the status is now indicated by color, you can get rid of the column with the status as text. If you’d rather keep it, you could have one column with the fill and the font color set the same. This would mask the text of the status inside the cell.

Gridlines can be very distracting, so clear all of them. To do this navigate to Tools>Options>View>Window options and clear the Gridlines check box. Doing this should immediately relax your eyes when you look at the chart.

To make it obvious which test has which status, right justify the column for test name.

If you want to show defects on your chart, you can do this using color. Remember where I said not to save over the bright red? If you have defects that you need to show on the chart, change the color of the related test case to bright red and add the I.D. and title of the defect to column on the right of the status. This works when you typically have 1 defect per test case. If you have more, I would just color that test bright red, and have a list of the defects elsewhere. Showing defects this way highlights the need for accurate and concise defect descriptions. I was reading in How We Test Software at Microsoft that their testers work very hard at creating good defect descriptions. In fact, a friend of mine had a great post that received excellent comments about this. Here is where the defect description can make a big difference. Ditto the test names. Everyone in the status meeting sees these and asks questions.

Edward Tufte would probably say that I should print this chart out on a really big piece of paper, but he doesn’t work in my group. My status meeting is paperless, so this will be displayed on a big fancy conference room flat screen, ‘cause that’s how we roll. Actually, I’m not joking about the rolling part. The person who leads the meeting has a tendency to compulsively scroll up and down, so I designed my chart to be displayed within the width of his laptop screen. That’s why the summary is out to the side. Also, I made the text of the title and the summary much larger than the font of the individual tests. I’m using Trebuchet MS for the font.

Interact
At the most basic level, this chart allows the user to interact with the chart by focusing on the smaller, individual tests if they choose to do that. They can also look at the defect information if they would rather. As an improvement, I might figure out how to add some information for each test when it is moused over.

This chart probably won’t scale if you have multiple testers, multiple projects or copius amounts of tests and defects. I know that’s most testers, but I really made this chart specifically for my needs. This shows the very busy and important people what they need to see in a way that they can trust.

As always, comments are welcome. Love it, hate it? Let me know. I’m still learning about this stuff and welcome the feedback.