Agile: Creating an SSRS Burndown Chart Part 1

The burndown chart. A must have for any ScrumMaster and Agile team. What it should show you is the rate at which you are “burning” down story points.

As you can see from the chart above, 3 lines. Red is your “points scheduled”, Green is the “goal” and blue is “points left”. While it is easy enough to create this chart and track the burndown manually in Excel, many teams after using Excel turn towards other systems to track their points and sprints. Right now I have one team using Unfuddle, one team using TFS, there are others that use this chart that use Footprints and really you can use whatever, and this chart can be built off of any database as long as it has the right data.

First, you need a table with your stories in it. You need to have some key columns – Sprint, Points and PointsLeft.

CREATE TABLE [dbo].[Stories](
	[Sprint] [varchar](50) NULL,
	[Points] [int] NULL,
	[PointsLeft] [int] NULL,
	[StoryId] [int] NOT NULL,
	[StoryText] [varchar](max) NULL
) ON [PRIMARY]

Now you may have others, like StoryId, StoryText, Assignee, etc but we aren’t concerned about those for this chart.

You then need at least 2 or tables, and a SQL job. 1 table to hold your Sprint and Dates and one to hold your “Story History”

CREATE TABLE [dbo].[Sprint_Dates](
	[Sprint] [varchar](50) NOT NULL,
	[WorkDate] [date] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Story_History](
	[LogDate] [date] NOT NULL,
	[Sprint] [varchar](50) NOT NULL,
	[Points] [int] NULL,
	[PointsLeft] [int] NULL
) ON [PRIMARY]

You will need a SQL Agent Job to run at 11:55 PM to capture the history, which should run this query:

INSERT INTO dbo.Story_History (LogDate,Sprint,Points,PointsLeft)
SELECT CAST(GETDATE() AS DATE),Sprint,SUM(Points),SUM(PointsLeft)
FROM dbo.Stories
GROUP BY Sprint

Remember you might not need all 3 tables, just the history and dates. You can get your actual stories off of wherever your stories are stored in the database. Now that you have your data in place, you can get ready to write the actual report! Look for the next part in this series.