Customizing the TFS Agile Workbooks

I have been working with customizing a process template for our company. This is a real challenge since there are so many aspects to this, and many are not known until later when the template is in use. It can then be hard to change it if you have been using it in a lot of TFS projects. I have been making different customizations since 2005 and I strongly advise you to put a lot of thinking effort into this work to make sure you do not end up with something that brings you more work…

In my work with a new corporate template for TFS2010 I have been using the Agile template as a base, but want to have more states since we think Active and Closed are just not enough. Also we want to make a template that could work both for Agile (Scrum) teams, for more traditional waterfall based approach and for maintenance projects. You could discuss a lot whether this is a good approach or not – but one additional thing that comes into place is how our TFS projects are structured. We have been creating TFS Projects for almost all of our software projects that mostly lead to a new system. This system always interacts with several other systems, both on the Windows platform and other platforms. In new projects several systems are involved which means you get a TFS project that “sends out” Change Requests to other TFS projects. This means that in the end it is really useful to be able to put together reports and queries that spans several projects. This also means that we use a terminology that is a bit Agile but does not use the “heaviest” Scrum terms. So for example we want to use “Effort” instead of “Story Points” to be more flexible.

We have also been influenced by the MS Scrum template and got rid of query structure of Iteration 1, Iteration 2, and Iteration 3 and replaced it with Current Iteration instead. A renaming and minor change in the queries was also made. One of the most useful new things I have come across so far in the 2010 release is the Agile Workbooks, with Iteration Backlog as the most valuable and Product Planning as the next. So we really want to have people use these since it brings more productivity into their work. So I ended up with a need to customize the Product Planning workbook in several ways.

* Replace “Story Points” with “Effort”
* Change the formulas to use more than two states, meaning that there is not such a distinct difference between Active and Closed
* Different names and folder structures in the Queries

I have also checked with Microsoft, and these customizations are supported.

The first two changes I have written about in a blog post called Howto Customize Product Planning Workbook

The third change is described in the blog post Necessary Customizations when changing Query Names in TFS Process Template

Advertisement

Howto Customize Product Planning Workbook

The reasons for me customizing the Product Planning Workbook can be found in the blog post called Customizing the Agile Workbooks

To get the customized workbook to be created and tied to each new TFS Project you need to customize the empty workbook in the process template. To do this you first have to download the template. This can be done in Team Explorer by right-clicking on the root node (collection) and choose Team Project Collection Settings, Process Template Manager. In the dialog shown, choose the template you want to customize and click Download. The template is written to a directory, and under this you can find the Product Planning Workbook in the folder

First of all I needed to replace “Story Points” with “Effort” in the query and the formulas. This showed up to be made in two places. First a change in the actual query file Product Planning.wiql where I simply replaced the field. Then to change this in the Product Planning Workbook. Not so suprisingly the workbook consists of some Macro code. It turns out that this code is based on values that can be found on a hidden tab in Excel. So this is how you easily change this:
1 – Open up the Product Planning.xlsx file. Find and press on the Macro button to the right end of the Ribbon called Show (at least that is a direct translate from the Swedish Visa).
2 – Choose to Edit the Macro. A Visual Basic for Applications window is shown. Make sure to mark the Microsoft Excel-object LocalizableStrings. At the bottom of the properties windows you can find the Visible property. Change this from “xlSheetVeryHidden” to “xlSheetVisible”

3 – Switch back to Excel. You can now see and edit an Excel sheet called LocalizableStrings. Here I have change two values. First I change “Story Points” in cell B5 to “LFEffort” which is the field name for the Effort field I have added in my workitems. Then I change Active to Closed in the cell C2. I will explain why later on.

4 – Get back to the Macro sheet and change back the visible property on the LocalizableStrings worksheet to “xlSheetVeryHidden”. Take caution here because when you change the property to hidden the tool will automatically change to another visible sheet. First I thought I did not change the property and ended up hiding all sheets…
5 – Secondly I needed to adjust how the sheet actually calculates the values in “Planned” and “Delivered” cells in the Iterations sheet. I found out that the formulas for this is created during project creation. And the state value is taken from the cell C2 in the LocalizableStrings sheet mentioned above. Since I have changed my state machine from Active+Closed to Proposed+Active+Resolved+Closed I need to find a way to have these values to be calculated in a correct way. The default behavior means that Planned is the workitems with State=Active and Delivered is State<>Active. The easiest way to customize this is to have Planned be State<>Closed and Delivered to be State=Closed. With the change in the LocalizableStrings sheet I have been able to change Active to Closed, but to switch the operators I have to do a change in the code that puts the formula together. This can be found in the module called ProductBacklog. In the code you can find a Sub called UpdateFormula. At the end of this Sub there are two calls to UpdatePointsColumn where the operator is the last parameter. So I simply switched these to get the desired behavior. Se image below.

6 – Switch back to Excel and bring up the first sheet and position the marker in the call A1. I did not do this at first so when the workbook was initialized during project creation the table for Product Backlog was created in the wrong sheet…

The only thing that now needs to be done is to upload the new process template using the reverse of what I explained at the beginning of this post. If you do process template customizations I really encourage you to use Source Control for all changes and not download and upload the workitem definition, but only use upload. In this way you get a controlled way doing this – and the possibility to roll back changes you do not want since you have the previous version in hand.

I have not yet needed to customize the Iteration Backlog workbook, but I might need to – and then I will write a blog post about that as well.