If you read my post on test statistics, then you know that I advocate having a worked-out system for producing metrics if your defect tracking database doesn’t do the job you need it to. Over the years I’ve never had the luxury of using a really good defect tracking system with metrics, so I’ve utilized a spreadsheet. I’ve developed a template that you can import defect data from your database and crank out the statistics you need.

Before I present the template, just a quick cautionary note. I am not advocating using a spreadsheet to actually manage defects – you can’t do this. You need a multi-user system that allows for attachments – a spreadsheet won’t cut it.

Template Links

Ok, so here’s the template in OpenOffice format and in Excel. Grab a version and open it up – there are 5 worksheets.

Read Me

Some additional background information, plus version history.

Data

This is the worksheet where you import data from the defect tracking system. There are many columns in this worksheet – you probably don’t need them, so delete or hide what you won’t use (just don’t delete columns A, B, or C). The Data columns (E, F, O, and P) are necessary for the date-based metrics to generate properly. You’ll notice columns U through AC are generated automatically by the spreadsheet – you can hide columns you don’t find necessary. Also, rows past the data section (row 40 and beyond) contain values for lookups (data validation) within the defect data – change these entries based on the values you’re using, though I’d recommend against deleting entries.

The columns are commented (the OpenOffice version is – I’m working on commenting the Excel version), so refer to the template for more information.

Stats

This section is generated automatically by the spreadsheet based on entries in the Data worksheet. This is summary worksheet on all of the defects – so if you want to produce metrics on a subset of defects (say, just the outstanding defects), you’ll need to use the data pilot/pivot tables worksheet.

Data Pilot Tables/Pivot Tables

These are “raw” pivot tables which allow you to produce metrics based on a subset of data. I’ve added eight different tables – you can create more as you need them.

Dashboard Input

Since the raw pivot tables aren’t the prettiest to look at, I’ve added this worksheet which summarizes metrics from the pivot tables and the Stats worksheets. This isn’t everything you would want to include in a dashboard – you’ll need some data on test case status, schedule, cost, etc… – but this is a start. You’ll probably want to tweak this a lot.

As always, drop me a line with any questions or comments you have.