Sprint Burndown from FogBugz

We've been using FogBugz to track work items for the Lysine project at Amino Software until we can make the move towards Team Foundation Server.

In the meantime, we are leveraging our existing FogBugz licenses to give us a simulated Scrum workflow. We've renamed the "Default" release bucket to Product Backlog and have created other "releases" named "Iteration 1", "Iteration 2", and so on, that are scheduled to complete at the 1st of each month.

So far so good, except as we find bugs and add and close items on the current iteration, there is no good reporting for our burndown or velocity. I spent some time digging in the database today to see what type of history and metrics were recorded by FogBugz on the individual work items.

I found enough to allow me to build the following stored procedure which I plan on consuming either in Excel or some other reporting tool to provide us with simple burndown charts for each Sprint, or iteration.

create procedure GetBurndownData(@FixForId int) as select Date, WorkItems.Opened, WorkItems.Closed, (select sum(wi.Opened - wi.Closed) from (select coalesce(Opened, Closed) Date, coalesce(WorkItemCountOpened, 0) Opened, coalesce(WorkItemCountClosed, 0) Closed from (select convert(varchar(max), dtOpened, 101) Opened, count() WorkItemCountOpened from Bug where ixFixFor = @FixForId group by convert(varchar(max), dtOpened, 101)) OpenItems full join (select convert(varchar(max), dtClosed, 101) Closed, count() WorkItemCountClosed from Bug where ixFixFor = @FixForId and dtClosed is not null group by convert(varchar(max), dtClosed, 101)) ClosedItems on OpenItems.Opened = ClosedItems.Closed) wi where wi.Date <= WorkItems.Date) 'Open Running Total' from (select coalesce(Opened, Closed) Date, coalesce(WorkItemCountOpened, 0) Opened, coalesce(WorkItemCountClosed, 0) Closed from (select convert(varchar(max), dtOpened, 101) Opened, count() WorkItemCountOpened from Bug where ixFixFor = @FixForId group by convert(varchar(max), dtOpened, 101)) OpenItems full join (select convert(varchar(max), dtClosed, 101) Closed, count() WorkItemCountClosed from Bug where ixFixFor = @FixForId and dtClosed is not null group by convert(varchar(max), dtClosed, 101)) ClosedItems on OpenItems.Opened = ClosedItems.Closed) WorkItems GO

Simply pass the id of the "FixFor" release bucket and viola, you'll get a nice dataset ready for plotting/reporting.

Tags: scrum, burndown chart, fogbugz, amino software, burndown, stored procedure