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.
select
allDates.DateSK as 'DateActive',
coalesce(count(distinct insidewits.System_Id),0)
from
DimDate allDates
left join
(select
wi.System_Id,d.DateSK
from
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
where
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)
where
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.
