Customizing TFS Work Item States and Reporting

We're using Team Foundation Server to manage our projects. The MSF Agile template, which is what we started with, has just two states for Tasks: Active and Closed. We wanted to add more states to keep track of the flow through iteration planning and test, so we ended up with the following states:

Pending
In Process
Developed
In Test
Closed

The initial state of Pending means it hasn't been planned for an iteration, whereas In Process means it's been assigned to an iteration. You could certainly envision other states, but you probably get the idea.

I added new states and transitions to the template and uploaded it to the server (you can use witexport to get a copy of the template and witimport to save it back to the server). The first think I noticed is that all the items in the "Active" state were flagged as having an invalid state because I had replaced "Active" with "Pending." There wasn't any batch method for updating these work items, so I had to change them one at a time. Fortunately, this is a young project, so there weren't many items that I had to change.

The next thing I found out is that I had broken some of the reports. It turns out that the burn-down chart on the Project Portal page is from the Remaining Work report, and this report assumes that Tasks only have the Active and Closed states. That means all the Tasks in all the new states disappeared from this graph. Woops!

Updating the Warehouse

Since I needed do some more work and testing, I switched to a test TFS server so I wouldn't be working with the production server. However, this server didn't have Tasks in the other states, so I changed the state of a number of tasks. But since the warehouse isn't updated immediately, I needed to run the warehouse task. You can do this by running a web Service on the TFS reporting box:

http://localhost:8080/Warehouse/v1.0/warehousecontroller.asmx?op=Run

You'll need to run this web service method from the TFS machine if you're using a browser to run it.

Setting up Visual Studio to Customize Reports

The reports in TFS are built on top of SQL Server Reporting Services, and Visual Studio has a way to edit the reports. However, getting it all set up wasn't exactly obvious. Here is how to set things up so you can edit and test reports.

Download a Report

The first step to customizing a report is to get a copy of the report from the server. There is an article on MSDN with the steps to get a local copy of the report: How to: Locate and Edit a Report. However, this article is missing one important piece of information.

Setting up the Data Sources

The reports on the server that I downloaded uses two shared data sources with specific names. I needed to create these data sources in the Visual Studio project created in the previous step so they would have the same names, as defined in Data Sources for Team Foundation Server Reporting.

In order to set up data sources, you'll need to have access to the SQL Server machine with the TFSWarehouse database. I didn't have access to the production server, but I did have access to a test box with TFS on it, so I set the connection strings to point to the test box. Just right click on the Shared Data Sources folder and select Add New Data Source from the context menu to create a new datasource. Read Data Soruces for Team Foundation Server Reporting to setup the two data sources correctly (both point to the TFSWarehouse database, but they use different providers).

Here is what the project looked like after I setup the data sources:

Modifying and Testing the Report

At this point I could double-click on the Remaing_Work.rdl report file and open it in the visual report designer. The report was initially setup to show only three values that correspond to the three states Active, Resolved, and Closed. Since I added new states to the Task template, I decided to add a new value for each state.

You add new state values to this report by clicking on the Layout tab and then right-clicking on the graph and selecting Properties.

In the Chart Properties dialog box click on the Data tab and then click on the Add button to the right of the Values list box. This will allow you to add a new value. For the value I used a variation of the expression that was already used for existing values:

=Sum(IIF(Fields!State.Value="In Process",Fields!Cumulative_Count.Value,0))

All I had to do was change the name of the state in the State.Value= test. The resulting dialog looked like this:

Test the Report

Finally, if you have the connections setup properly, and have sufficient rights, you should be able to test your modified report on your computer using the Preview tab of the Report Designer. The report should behave exactly as it would if you double-clicked on the report in the Team Explorer Reports folder.

Upload Modified Report

Once you have your report working the way you want, you'll need to upload it back to the server. The process for this is almost identical to downloading the report, except that you'll click on the Update link instead of the Edit link at the bottom of the page:

Here is what the chart looked like when I was all done:

This graph actually shows the before and after. Most of the graph shows Tasks that were based on the three original states, using data saved in the data warehouse. The last two days of the graph show the "Active" state split into two of the new states: "Pending" and "In Process." As we move forward, we'll see even more states appear on this chart.