Creating a new TFS Report–Bugs added after done (or Regression Bugs)


I have been thinking about different ways to promote and track quality practices and one aspect of that is bugs. 

Lots of Scrum teams treat bugs differently.  Some log every bug, some log only those found once a story is done.  This can make it difficult to apply traditional quality measures to a Scrum team.

One metric that I think is worth tracking is bugs after done.  What does this mean?  Well once a story is finished it is marked as done (You are using a consistent definition of done aren’t you?)

If, as part of a later testing effort we discover a bug in the implementation of that story (either through a regression issue, or maybe we missed it first time during testing) then that might indicate an issue with our practices.  If we find lots of these, then it is probably worth the team spending some time to understand why these are occurring.  I decided that it would be nice to have a TFS report that could track the trend of these over time.

We are looking for a count of bugs that were active and linked to a user story (that was closed or resolved) trended against time.

Note – this will also identify any bugs that were raised against an open user story and then forgotten to be closed (even though fixed)  I could omit these by also checking if the bug creation date was after the date the user story was closed, but I am happy to identify these also.

Writing this report will give us some good insights into the TFS_Warehouse database.  We need to understand how the User Stories have changed over time, as well as how their child (bugs) work items have changed over time.

The Report

This is an idea of what the report should look like:-


You can see that we are trending the number of regression bugs over time.

The Query

This is the query that we will use in the report.

        allDates.DateSK as 'DateActive',
        coalesce(count(distinct insidewits.System_Id),0)
        DimDate allDates
left join

        DimDate d
cross apply
        DimWorkItem wi
cross apply
        GetWorkItemsTree(@TeamProjectCollectionGuid, wi.System_Id,N'Child', d.DateSK) wit
left  join        
        WorkItemHistoryView wih_child  
        on wih_child.WorkItemSK = wit.ChildWorkItemSK
inner join
        DimTeamProject tp on wi.TeamProjectSK = tp.ProjectNodeSK

        d.DateSK > DATEADD(month,-1,getdate())
        and d.DateSK < GetDate()
        and wi.System_ChangedDate <= d.DateSK
        and wi.System_RevisedDate > d.DateSK        
        and wi.System_WorkItemType = 'User Story'
        and (wih_child.RecordCount != -1 or wih_child.RecordCount is null)
        and wih_child.System_State = 'Active'
        and wih_child.System_WorkItemType = 'Bug'
        and wi.System_State = 'Closed'
        and wi.System_Reason = 'Acceptance tests pass'
        and tp.ProjectNodeGuid=@ProjectGuid) insideWits       
    on (allDates.DateSK = insideWits.DateSK)
        allDates.DateSK > DATEADD(month,-1,getdate())
        and allDates.DateSK < GetDate()
group by allDates.DateSK

I will not go through it line by line.  A couple of points to note – we are using the GetWorkItemsTree to get the particular state of workitems over time.  We are also using the ChangedDate and Revised date fields to optimize our query as explained here.  This is using Work Item Tracking compensating records to find work item dimension data quickly.  This query shows us the data over the last month.  Obviously you could adjust that or add it to the report as a parameter.

I have attached the finished report rdl here, for anyone who might find it useful.  Stay tuned for more quality focused reports.


1 comment :

  1. Heya¡­my very first comment on your site. ,I have been reading your blog for a while and thought I would completely pop in and drop a friendly note. . It is great stuff indeed. I also wanted to there a way to subscribe to your site via email?

    Agile Coach Training