Customizing the Iteration Backlog Workbook

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.

Getting an Unbound 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:

1. In Team Explorer, right click the server node.
2. Click Team Project Collection Settings
3. Click Process Template Manager
4. Click MSF for Agile Software Development v5.0 to select this process template in the list
5. Click Download, select the folder where you’d like to save and click Select Folder

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

Preparing the Workbook for Development

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.

1. From Team Explorer, double-click the Iteration Backlog workbook that is in one of your team projects
2. Select the entire table on the Iteration Backlog tab and copy this to the clipboard
3. Paste the results into Notepad and save it as a text file
4. Quit Excel

Now open the unbound copy of the Iteration Backlog workbook and follow these steps:

1. If you see a security warning, click the Enable Content button:
   

2. Open the text file you saved in the previous step in Notepad, select all the text, and copy it. Note: make sure Word Wrap… is not checked under the Format menu in Notepad before you copy the text

3. Select cell A1 in on the Iteration Backlog tab and past the text from the previous step. Note: It’s important that you copy the text from Notepad so you’re not pasting in a reference to another workbook.

4. With the cells containing the copied text still selected in Excel, click the Insert tab on the ribbon, and then click Table

5. Ensure the My table has headers checkbox is checked and click OK.

6. Press Alt+F11 to display the Visual Basic for Applications window

7. In the Project pane, expand the Modules node

8. Double-click the DevelopmentModule to display the VBA code for this module:

   

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

Sharing the Customized Workbook

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

1. Make sure the Iteration Backlog tab is the active tab
2. Click cell A1 to make this the active cell
3. Click the Team ribbon
4. Click New List
5. Select your team project and click Connect
6. Select the Iteration Backlog query and click OK

Then click the Team ribbon and click New List

When and Why VBA

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.

Iteration Backlog Tab

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.

Settings Tab

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.

Interruptions Tab

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.

Capacity Tab

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.

Burndown Tab

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

Customizing State Names and Other Strings

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.

Summary

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.

Comments

• Anonymous
  March 09, 2011
  Hi, 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? Best regards Yannik

• Anonymous
  March 21, 2011
  Hi Yannik, 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 Regards, Thirumoolan

• Anonymous
  April 24, 2011
  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? Thank you.

• Anonymous
  April 25, 2011
  Yes, that is correct. In the Product Planning workbook, there shouldn't be any changes you need to mkae in DevelopmentModule.

• Anonymous
  April 26, 2011
  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?

• Anonymous
  January 10, 2012
  Hi John, 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. Thanks.

• Anonymous
  July 18, 2012
  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.