Enabling the Iteration Backlog Workbook on an Upgraded Team Project

  • Article

NOTE: THE STEPS OUTLINED IN THIS POST ONLY APPLIED TO TFS 2010 BETA 2. PLEASE SEE THIS POST FOR THE SIMPLIFIED RTM STEPS. 

Visual Studio Team System (VSTS) 2010 Beta2 introduces new features and capabilities to help agile teams with planning.  One of those features is “out of the box” Excel workbooks that ship as part of the MSF for Agile Software Development v5.0 process template

  • Product Backlog – Use this workbook to create and manage the product backlog, estimate the team's velocity, and to break the project down into iterations.
  • Iteration Backlog – Use this workbook to plan an iteration and track its progress. This workbook helps you load balance your team effectively and ensure that your team is committing to goals it can achieve.

This article describes the steps you can take to enable the Iteration Backlog workbook for existing Team Projects not based on the MSF for Agile Software Development v5.0 process template.  This post is one of a series about upgrading Team Projects to take advantage of new features that ship in VSTS 2010. To simplify the steps this article assumes you’re existing Team Project is built on the MSF for Agile Software Development v4.2 process template.

Iteration Backlog Workbook

  • Step 1 – Download the MSF for Agile Software Development v5.0 process template
  • Step 2 – Modify the Task work item definition
  • Step 3 – Create an iteration backlog query
  • Step 4 – Bind your query to the product backlog workbook
  • Step 5 – Modify the workbook document properties

Step 1 – Download the MSF for Agile Software Development v5.0 process template

To get started you’ll first need to download the MSF for Agile Software Development v5.0 process template from your Team Foundation Server.  We need to download this template to get a copy of the Iteration Backlog workbook.

  • In the Visual Studio Team Explorer, right click your Team Project Collection, then click Team Project Collection Settings, then click Process Template Manager.
  • In the Process Template Manager dialog, select the MSF for Agile Software Development v5.0 process template and click Download.
  • Save the process template to a local directory. We’ll use the workbook in this template in later steps.

Step 2 – Modify the Task work item definition

Next, let’s modify the Task work item definition in your current project to include the fields required by the Iteration Backlog workbook.

  • From the command shell, go to “Program Files\Microsoft Visual Studio 10.0\Common7\IDE”
  • Run the following command to download the task work item type:
 witadmin.exe exportwitd /collection:https://myserver:8080/path/mycollection
/p:myproject
/n:“Task” 
/f:MyTask.xml
  • Open the MyTask.xml file that you downloaded above and add the following snippet to your Scenario.xml work item definition under the WITD.WORKITEMTYPE.FIELDS node.
 <FIELD name="Original Estimate" 
    refname="Microsoft.VSTS.Scheduling.OriginalEstimate" 
    type="Double" reportable="measure" formula="sum">
    <HELPTEXT>The number of hours originally estimated to 
    complete this task</HELPTEXT>
</FIELD>
  • Add the new Original Estimate field to the layout of the Task work item form.  To do this, modify the Schedule node as follows:
 <CONTROL type="FieldControl" 
    fieldname="Microsoft.VSTS.Scheduling.OriginalEstimate" 
    label="Original Estimate &amp;(hours):" 
    labelposition="Left" 
    numberformat="DecimalNumbers" 
    maxlength="10" />
  • Execute the following command to upload the new work item definition.
 witadmin.exe importwitd /collection:https://myserver:8080/path/mycollection
/p:myproject
/f:MyTask.xml

 

Step 3 – Create an iteration backlog query

In order to use the Iteration Backlog workbook you must create the appropriate query in Team Explorer.

  • In the Visual Studio Team Explorer, right click the Work Items node and click Add Query.
  • Create a query with the following parameters and columns.

Parameters:

    • Team Project = @Project
    • Iteration Path = <Iteration You Are Planning>
    • Work Item Type = Scenario

Columns:

    • ID
    • Assigned To
    • Title
    • Remaining Work
    • Completed Work
    • Original Estimate
    • Area Path
    • Iteration Path
    • Work Item Type
  • Save the new query and name it “Iteration Backlog”.

Step 4 – Bind your query to the iteration backlog workbook

Next, we’ll bind the query we created in Step 3 to the workbook.

  • Open the \Windows SharePoint Services\Shared Documents\Project Management folder from the process template you downloaded in Step 1. 
  • Copy the Iteration Backlog.xlsm into a new location and open the file.
  • After the file opens, click the Options… button in the security warning and select Enable this content.
  • Place your cursor in cell A1 of the Iteration Backlog worksheet.
  • Switch to the Team ribbon and click New List.
  • Select your Team Project and click Connect.
  • From the New List dialog, select the Iteration Backlog query you created in step 3.

Step 5 – Bind your query to the iteration backlog workbook

The final step is to add a set of document properties that allow the workbook to communicate with the TFS reporting warehouse. Before we create the properties we need to collect some information about your project. The first piece of information comes from the table created in the previous step.  As you collect these properties, copy them into notepad so they can be used in later steps.

  • [Table name] – Switch to the Design ribbon and select the Table Name value in the Properties portion of the ribbon

  • [Project GUID] – In the Visual Studio Team Explorer, right click your Team Project and select Properties.  Select the URL value and copy the GUID (long value with lots of characters) at the end of the URL.

  • [Team Project name] – In the Properties dialog, select the Name field and copy the value. 

  • [TFS server name] – In the Properties dialog, select the Server Name field and copy the value. 

  • Switch back to the Iteration Backlog workbook.   Click the Office button and select Prepare – Properties.

  • Click the Document Properties – Server drop down and select Advanced Properties.

  • Switch to the Custom tab and add the following properties using the values you collected above.

    • Name: [Table name]_ASServerName, Value: [TFS server name]
    • Name: [Table name]_ASDatabaseName, Value: tfs_analysis
    • Name: [Table name]_TeamProjectName, Value: [Team Project name]
    • Name: [Table name]_TeamProjectId, Value: [Project GUID]

  • Click OK to close the properties dialog.

  • Save and close the workbook. 

Your new Iteration Backlog workbook is now attached to your Team Project and can be used to plan an iteration.  To preserve the workbook you should save it to a permanent location (SharePoint portal, etc).

Comments

  • Anonymous
    May 19, 2009
    After you upgrade your server to Visual Studio Team System 2010 Beta 1, you have a wealth of new features
  • Anonymous
    June 11, 2009
    Hi,In Step 3 – Create an iteration backlog query.Would'nt the work item type be "task" instead of "scenario"?Bertrand
  • Anonymous
    June 11, 2009
    You're right Bertrand.  I've corrected the typo.Thanks.Aaron
  • Anonymous
    February 23, 2010
    The comment has been removed
  • Anonymous
    February 23, 2010
    Russell - I'd be glad to help.  Can you email directly at aaron.bjork@microsoft.com?Thanks.Aaron
  • Anonymous
    January 19, 2011
    Hi Aaron,Have MS resolved  the errors in Iteration backlog workbook due to the iteration name change?I am still waiting for the solution for this.  We can't do any project planning in TFS due to this as we renamed the "iteration 1" in the very beginning.social.msdn.microsoft.com/.../846c372a-ece2-48b2-abe9-dae51a768e39
  • Anonymous
    January 19, 2011
    @StanMar - Sorry you're getting that error.  Have you tried downloading an unbound copy of the workbook as described here:   blogs.msdn.com/.../unbound-agile-workbooks.aspxThat should do the trick as it gives you a fresh, unbound workbook.  This bug is on our backlog.Thanks.Aaron
  • Anonymous
    January 20, 2011
    Yes, I've tried downloading an new copy from the Process Template Manager and use it locally.  But it still didn't work.We renamed "iteration 1iteration 2iteration 3" to MQ1Sprint1Sprint2 in the very beginning of the project setup and have created work items under each iteration.  We can connect the unbounded iteration backlog to TFS and configure a new list in the iteration backlog spreadsheet.  But we get "the value you entered is not valid" error when trying to change the Iteration from "Iteration 1" to "Sprint1" in the Settings spreadsheet.  The burndown sheet also returns "Iteration 1 is not in the cube" error.PS. I am unable to complete the Step5 as you described above. •Switch back to the Iteration Backlog workbook.   Click the Office button and select Prepare – Properties. •Click the Document Properties – Server drop down and select Advanced Properties.I didn't see a Office button on my iteration backlog and not sure if it is related to the problem  I am facing.
  • Anonymous
    January 20, 2011
    @StanMar - the steps in this post are no longer relevant... which is why I pointed you to blogs.msdn.com/.../unbound-agile-workbooks.aspx.  You don't need to do a bunch of the additional steps in this post.Try the steps in the post above.  When you get to the settings tab, you can only select a value that is returned in the query.  In other words, you'll need to ensure that your query returns a work item with a value of "Sprint1" before you'll be able to selected it.  Let me know if that works.  
  • Anonymous
    January 24, 2011
    I tried the downloaded interation backlog workbook.  I am able to configure a new list in the Iteration Backlog tab and select from the query I saved in My Queries.  However, I didn't see my interations (like Sprint1, Sprint2) in the Iteration droptown list in the setting tab.  I only shows "interation 1" in that field.Is there anyway I can troubleshoot and debug?thanks a lot
  • Anonymous
    January 25, 2011
    @StanMar - that cell on the settings tab will only display a subset of what is returned in your query result.  It's hardcoded to "Sprint1" at the start... but once your query results return something else, those values should be avialable in the cell drop down list.  Let me know if that works.
  • Anonymous
    January 27, 2011
    Nope.  I expect the iteration in the settings tab displaying "Sprint1",  but "Iteration 1" is the only item available in the iteration dropdown list.I renamed iteration1iteration2 to Sprint1Sprint2 in the Team ExplorerProjectTeam Project SettingsAreas and Iterations right after I created the project.  I didn't know it will create so many problems.  For instance, the folder name in Team Queries still remains  "Iteration 1", "Iteration 2" and I can't access any of the team queries under "Iteration 1" folder.  It shows "TF51011 error: the node specified is not found in the hierarchy" when you click a query under Iteration1 (such as Active bugs, Active Tasks).  Rename folder name "Iteration 1" to "Sprint1" doesn't help.Looks like it also creates problems in the iteration backlog and product planning workbooks. I am able to configure a new list in the Iteration Backlog tab and select from the query I saved in My Queries.  But the Iteration dropdown list in the settings tab doesn't get result from the query I configured for the list.