[video] How To Create Burndown Charts For User Stories in SharePoint

I love tracking user stories in SharePoint. It’s free (assuming you have access to a Windows Server 2003 or 2008 machine with WSS turned on) and is extremely flexible. Adding new custom columns is easy. Adding custom views is easy. Bulk editing is amazingly simple and powerful (assuming you’re willing to use IE). And building a custom home page “portal” with all the states of your team’s workflow (e.g. unassigned, stories I’m working on, my resolved stories, to peer test, etc) is easy and is intuitive to use.

This 13 minute screencast shows you how to create a user stories list in SharePoint and build burn up and burn down charts off of the data using Microsoft Excel. It includes the custom fields you'll need in SharePoint, the formula's you'll have to create in Excel, and shows tips along the way like how to simplify refreshing your reports when data changes.

Once you’ve watched the screencast if you want to implement this yourself here is the information you’ll need:

Custom SharePoint Columns

  • Iteration (Choice column e.g. Iteration00, Iteration01, zBacklog)
  • Released (Date – the date the iteration was closed out)
  • Points (Number)
  • Created Date (for demo only, you should be able to use “Created” on your project)

Calculated Columns in Excel

  • Total Points to Date
    • The total points on a specific date including backlog, current iteration and all completed stories
    • =SUMIF([Created Date],CONCATENATE("<=",Table_owssvr_1[[#This Row],[Released]]),[Points])
  • Completed To Date
    • Total points for stories released prior to current story's release date
    • =SUMIF([Released],CONCATENATE("<=",Table_owssvr_1[[#This Row],[Released]]),[Points])
  • Backlog
    • What remains to be completed at a point in time (e.g. don’t include user stories added to the backlog in iteration 4 if this is for a task in iteration 03)
    • =Table_owssvr_1[[#This Row],[Total Points to Date]]-Table_owssvr_1[[#This Row],[Completed to Date]]

Burndown Pivot Table Values

  • Row: Released
  • Filter: Released After [Some Date]
  • Values: Max(Backlog)

Burnup Pivot Table Values

  • Row: Released
  • Filter: Released After
  • Values
    • Max(Total Points to Date)
    • Max(Completed To Date)


I hope you found the presentation useful and if you are currently using SharePoint for tracking user stories please comment on your experiences.


Anonymous said…
Can't view the video. Neither IE8 nor Firefox recognizes the plugin needed. Please advise.
Lee Richardson said…
Anonymous: It requires Flash 10. Try updating your Flash and see if that helps.
Anonymous said…
Great video! Im just having a little trouble with the formular in excel 2010. It dosent accept the formular. It seems like there is a difference in how the formulars are written in 2010 version?? Could you maybe make a little update where you show how to use formular in excel 2010? Would be mutch appreciated!
Anonymous said…
Excellent post, exactly what I was looking for and very clearly presented. Thanks a million
Anonymous said…
Excellent post, exactly what I was looking for and very clearly presented. Thanks a million