It seems that many people will get the first iteration backlog workbook working and then they will copy & paste it for future iterations. This is what we started doing until we found out just how messy this really is. The Agile iteration backlog workbooks are very useful for Agile (and SCRUM) teams to understand their capacity with interruptions.
These workbooks are very useful, though, there is some confusion often by those using the workbooks what to do when a new “Sprint” (or “Iteration” – used interchangeably in this post) is started. The copy & paste exercise *might* work but often it locks the workbooks up due to the macros getting unhappy. This is the point of today’s post.
I will walk you software dev managers (project, pm’s, or whatever title you have) through starting from scratch at each sprint and utilizing the workbooks to track progress.
Step 1: Move any un-completed work from Previous Sprint to Backlog
The first step is to select the iteration backlog tab of your previous sprint’s workbook by click on it, then locating the Iteration Path column and moving all work items back to the Backlog iteration. To do this, here are the step-by-step pieces –
- Open Sprint N Workbook
- Click Iteration Backlog tab in the Workbook
- Locate the Iteration Path column to the far right of the workbook, and click the dropdown
- Do this for all remaining work
- Click Team tab in the Excel Workbook
- Click Publish
Step 2: Create New TFS Workbook Query for new Sprint
The next step, often overlooked, is to create a new List-based query that pulls all the work items and columns for your upcoming sprint. In this step, I will walk you through step-by-step creating the new query that will be utilized by your new Iteration backlog workbook.
- In Visual Studio 2010, open your Agile Project
- Click Work Items
- Click Team Queries
- Click Workbook Queries
- Assuming you’ve still got the original queries shipped with TFS 2010, right-click on Iteration 1 Backlog and select Copy
- Right-click on Workbook Queries and select Paste and provide the new Query item name
- Right-click on the newly created query, select Edit
- In the query editor, locate the Iteration Path and change it to the iteration you’ve defined for this new sprint
- Click Save Query
Step 3: Open Iteration Backlog Template & Bind to new Query
The next step actually lights up your iteration backlog by binding the workbook to the query you’ve recently edited and saved. This tells the workbook what data to pull in to manage for this sprint. Let’s walk through this binding process…
NOTE: This step assumes you’ve got a folder under documents called Process Templates. If you do not, then you will need to locate where you’ve got a file named Document Template – Iteration Backlog.xlsm and open it. This file is bound to the project automatically and should be made available as part of your creation of a new project.
- Open Visual Studio 2010 and connect to your Project
- Drill-down into the Documents, Process Templates, Project Management folder by clicking on the arrows
- Right-click on Document Template – Iteration Backlog.xlsm and select Copy
- Locate where you would like to save this new workbook, right-click and select Paste (it must live somewhere within the Documents scope as it should be saved to SharePoint)
- Right-click to Rename the workbook to Sprint N Iteration Backlog.xlsm
- Double-click to open the Workbook
- In the workbook, click the Team tab and then the Configure –> List
- In the Configure List Properties, click the … and then Select the query created in Step 2
- Click OK
- Click Apply
- Click on the Save icon to save this workbook back to SharePoint
That’s it. You are now ready to start pulling items from your backlog and assigning them to the sprint and they will now show up in your workbook. Please keep in mind that you utilize the Product Backlog workbook to set the iteration path and then, once you’ve selected your sprints scope, you then open the Iteration backlog and start setting your Remaining Work & Completed work, Capacity, etc.
In today’s post, I wanted to share with project managers how they can best utilize the great workbooks that are part of TFS 2010. The unfortunate time comes when you’ve got to understand how to take a template all the way to a workbook and this wasn’t something that necessarily just “made sense.” Today, I walked you through the steps to go from a template (shipped with TFS) to an actual workbook with actual data.