Earlier this year we adopted Scrum to manage our releases. Our internship program used it for their project, and then the rest of the development team started adopting some practices — we found it useful enough that we decided to run the 5.0 release as a Scrum project.

I am acting as the Scrum Master, which means that among other things, that I am responsible for generating the burndown chart. Although we have adopted Visual Studio Team System for source control, we are still using FogBugz for bugtracking, so we don’t get the automatic Scrum tools in VSTS. With a little work, I was able to generate what I need. FogCreek publishes the FogBugz schema, so it’s pretty easy to get the data you need. Since they don’t really store historical estimates and elapsed time, you have to get each date’s datapoint for the chart on the actual date, you can’t get previous days (They do store it in comments, but I decided it was not worth parsing the data, since I have to generate the chart every morning anyway).

This query can get the list of open cases for a person and release:

SELECT
  hrsOrigEst,hrsCurrEst,hrsElapsed,
 ixBug,sTitle,sstatus 
FROM bug,person,fixfor,status
WHERE
  bug.ixPersonAssignedTo = person.ixPerson 
  and person.sFullName=? 
  and bug.ixFixFor=fixfor.ixFixFor 
  and bug.ixStatus=status.ixstatus
  and FixFor.sFixFor=?

For burndown charts, you are interested in the total difference between hrsElapsed and hrsCurrEst for active cases. I also track the total hrsCurrEst to see if we’re reestimating cases or if new cases are being added to the sprint.

Even though I don’t parse the automatic comments (BugEvents) for the estimated and elapsed time, I do want to eyeball them (to track down anomalies).  Here is the SQL for that.

SELECT dt, cast(sChanges as varchar(8000)) as changes
FROM bug,bugevent
WHERE
     bug.ixBug = bugevent.ixBug and bug.ixBug=?
ORDER BY dt desc

I probably wouldn’t need to do this if FogBugz would just do sub-totals of Estimates and Elapsed time on their filters (when I group by Owner), but it’s not so bad.

I take this data and copy to Excel and use linear regression on the data to get an idea of how we’re doing on getting the sprint done in time. I generate the chart, and put a copy in our internal wiki, so everyone in the company can see our progress.

In this article, Joel says: “In FogBugz 6 there’s one place where we need to do literally millions of calculations to display a single chart on a single web page” — I’m hoping that that means some kind of release trend chart.