The MSF for Agile process template that ships with TFS 2010 includes a nice Iteration Backlog workbook. Because this is an Excel workbook, it’s completely customizable. However, as far as I know, there isn’t any documentation on how to go about customizing this workbook. The Iteration Backlog workbook contains a fair amount of VBA code, so it may be a bit daunting when you try to make changes. I did much of the development on this workbook when I was in TFS, so I can provide some insight into how best to make changes to this workbook.
When you double-click the Iteration Backlog workbook inside one of the team projects, you’re getting a copy of the workbook that has been bound to a specific team project. Once a workbook is bound, it’s very hard to unbind it. How is this important? If you’re only going to be changing the workbook for one team project, it isn’t important. However, if you want to make changes that will work with more than one team project, it’s far easier to make changes to a single unbound workbook, and then bind copies of that workbook to the different team projects.
Here is how you can get unbound copy of the workbook:
These steps will download the entire process template to a new folder created inside the folder you selected. You’ll find the Iteration Backlog workbook in the following path under the process template’s folder:
Windows SharePoint Services\Shared Documents\Project Management
Windows SharePoint Services\Shared Documents\Project Management
Now that you have an unbound workbook, there are some steps you’ll need to take in order to temporarily connect the workbook to TFS without actually binding the workbook to TFS. I’m using Office 2010, so the instructions below are for that version of Excel. If you have people who will be using Excel 2007, you should probably use Excel 2007 to modify the workbook just to be safe.
The first step is to retrieve a set of work items you can use while you’re making changes. You’ll save these as a plain text file to ensure that the workbook you’re changing is not bound to TFS, or that it has links to another workbook.
Now open the unbound copy of the Iteration Backlog workbook and follow these steps:
The subroutine inside this module is called Prepare, and it should look like this:
.Add tableName & "_ASServerName", False, msoPropertyTypeString, "".Add tableName & "_ASDatabaseName", False, msoPropertyTypeString, "".Add tableName & "_TeamProjectName", False, msoPropertyTypeString, "".Add tableName & "_TeamProjectId", False, msoPropertyTypeString, ""
You will to insert some text inside the “” at the end of each line. Here is a summary of what will need to go into each of those lines:
Once you’ve made these changes, click anywhere inside the Prepare subroutine and press F5 to run this method. Your workbook should now function as if it is bound to TFS without actually being bound (the content on the Iteration Backlog tab is “static” because it’s not actually bound to a TFS query).
Once you’ve finished making changes to the workbook, you’ll need to undo the temporary “binding” you setup in the previous section. This is actually very simple. Place the cursor inside the Clean subroutine in DevelopmentModule and press F5. This will set the workbook back to a clean state that you can then put into a process template or share with anyone on your team.
If you want to bind to an actual team project, make sure you make a copy of the workbook (so you don’t bind your “development” copy).
Then click the Team ribbon and click New List
You may be wondering why there is so much VBA in this workbook. And there is quite a bit. In this section I’ll provide an overview of the VBA used by each tab of the workbook.
The VBA code on this tab runs whenever you switch to this tab. It sets up custom formatting of the State and Remaining Work columns, as shown here:
The state has a green background whenever it’s Active. The Remaining Work column shows bars that give you an idea of the size of the different tasks, in terms of the number of hours remaining. You can easily modify these macros to change the formatting to work the way you want.
If you’ve changed the name of states, you don’t need to modify the macros. Instead, there is a hidden worksheet that contains the list of names. I’ll cover this a little later.
The main job of the macros for this tab are to update the validation list used for the Area and Iteration cells. These lists display all the areas/iterations that are present in the query result. The actual code to update these lists is in UtilityModule, and is rather long. Chance are you won’t need to modify these macros.
The macros on this tab handle updating the team member list (from the Assigned To field on the Iteration Backlog tab) and doing some validation.
This tab is probably where you’ll want to make customizations. There are a number of moderately complex formulas on this page, and many of them are actually set by the VBA code. Why, you ask? The formulas in this tab need to reference the query results table in the Iteration Backlog tab. However, this table doesn’t exist until TFS creates it. Since you can’t have formulas reference something that doesn’t exist, we had to use macro code to setup the formulas. That means any changes you make to the formulas, without updating the VBA code, will be overwritten by the VBA code whenever you switch back to this worksheet.
The easiest way to modify the formulas is to try making changes directly in the worksheet until you have it working the way you want. Then copy the formula into Notepad, for example. And finally update the VBA code that sets the formulas to use your modified formula.
I would venture to say that the majority of the VBA code in the workbook is devoted to this tab. The burndown chart is driven from a PivotTable on the hidden BurndownData worksheet that is connected to the TFS cube (which is a special type of database designed for historical reporting).
The names of states and fields that are used by macros are stored in a “very hidden” worksheet called LocalizeableStrings. In order to display this worksheet, you’ll need to switch to the VBA editor (press Alt+F11), select LocalizeableStrings, and then change the Visible property to –1 – xlSheetVisible, as shown here:
You’ll now be able to see that worksheet in the workbook.
This blog post just scratches the surface, but I hope it helps people customize the Iteration Backlog (and Product Backlog) workbooks for their own needs.
first of all, I would like to thank you for the great post.
I have succeeded in getting the IterationBacklog List for our teamproject. The other Tabs in the Excel show errors.
Settings Tab, Interruptions Tab, Capacity Tab: "One or more required fields filters are not present in the BurndownData pivot table: Area Path, Iteration Path, Completed Work"
Burndown Tab: "One or more document properties are not present: VSTS_ccc3170e_4023_49aa_94b0_d63ea9eadf6a_ASServerName, VSTS_ccc3170e_4023_49aa_94b0_d63ea9eadf6a_ASDatabaseName, VSTS_ccc3170e_4023_49aa_94b0_d63ea9eadf6a_TeamProjectName, VSTS_ccc3170e_4023_49aa_94b0_d63ea9eadf6a_TeamProjectId. This could be because Reporting Sevices is not installed for this TFS instance."
I guess this is an incompatibility between MSF Agile and Scrum template. Where do I have to correct these settings?
Kindy follow the below steps
1) Click Burndown sheet
2) Click View Menu and select Macro or press ALT-F8. It will open Macro Listbox.
3) Click UpdateBurndown and click run or double click UpdateBurnDown macro.
I hope it will solve your problem
What a fantastic article. Couldn't be clearer!
However, I need to do the Product Planning workbook and the section where you are editing the DevelopmentModule won't work for the Product Planning workbook because there are no .Add tableName's there. Does that mean there is no work needed to be done in the ProducPlanning's DevelopmentModule?
Yes, that is correct. In the Product Planning workbook, there shouldn't be any changes you need to mkae in DevelopmentModule.
A followup question. I walked through this article step by step and then tried to add a column to the workbook. When I saved it, though, the new column wasn't there. It just disappeared. Is there something special that has to be done to add a column to the workbook?
I must say that article is great, but I have a problem a few days with "TeamRemainingWork" formula. This formula use WorkItemType "Task" by default, but I need to use "Sprint Backlog Item" by default. I have modified VB Code, but still no working. May I get correct vb code how it should look like.
Hi John, great article.
How can i do to see the burndown chart by team member? Is there an easy way to do this?
Thanks in advance.