Friday, February 12, 2010

[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.