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. 

Advertisement