Bug tracking analysis using SharePoint

For many companies, Microsoft Sharepoint has become an important way to communicate and collaborate on projects, including hardware and software development projects. It’s very simple with SharePoint’s custom lists to develop quick bug reporting solutions to meet your internal needs and test methodologies in just a few minutes. However, what most people don’t realize is that SharePoint can also be used in conjunction with Microsoft Excel to perform some rather informative analysis and graphing of your defect tracking data.The key to everything is the ability of SharePoint to export data to Excel using web queries. You can export the data to Excel and, using the connector, update the data from SharePoint whenever you want (e.g., on opening the spreadsheet, after a set period of time, via a macro button, etc.). It’s incredibly handy and VERY powerful, especially if you’ve any familiarity with some of Excel’s conditional formulas – in particular, COUNTIF.COUNTIF will, as the function name suggests, count how many times a certain criteria is true. For example, =COUNTIF(bugs,"critical") would examine the named range "bugs" and count how many critical bugs are currently in your bug tracking list. Even if you just had a custom list with only ONE field (bug severity/status), you’ve already collected a TON of valuable data. Don’t believe me? SharePoint automatically tracks the date a list item was created, the date modified, as well as the individual who created and modified any list item, so with just that little bit of information, you can perform some informative calculations.Just think of what you can now calculate:

• How many bugs has your team located today? (Search range "date" for any items added today)=COUNTIF(date,TODAY())
• How many bugs has your team located per day? (Search range for the total number of bugs and divide by the total working days)

=COUNTIF(date,"*")/(NETWORKDAYS(SMALL(date),LARGE(date)))

• How many critical bugs have you located per day? (Search range for all bugs marked "critical" and divide by the total works days)=COUNTIF(bugs,"critical")/(NETWORKDAYS(SMALL(date),LARGE(date)))
• How many bugs has your team fixed per day? (Search range for all bugs marked "closed" and divide by total working days)=COUNTIF(bugs,"closed")/(NETWORKDAYS(SMALL(date),LARGE(date)))
• How many bugs has a particular team member found per day? (Search the "created_by" range for all bugs found by "Larry Johnson")=COUNTIF(created_by,"*larry*")/(NETWORKDAYS(SMALL(date),LARGE(date)))

That's just a sample of what you can evaluate, and those were based on just ONE editable field! Imagine what you could do with a few more. With a little time, you can whip up a handy Excel dashboard that manager can use to get a quick overview of the QA status of your testing. Good luck with your bug tracking!* Note that if the NETWORKDAYS formula (which calculates the number of working days between two dates) gives you errors, make sure to enable to the Analysis ToolPak Add-in for Excel.