Refocusing “Remaining Work” on Your TFS Project Portal (Part 1)

One of the neat things about the “default” project portal is the “Remaining Work” graph. At a glance it can give you an idea on how things are going with your project. However, it can also be misleading. On my current project, we’ve identified quite a few features/tasks that we want to complete in a future iteration. Unfortunately the “graph” shows that we’re drowning. To the guys paying the bills, that’s a bit of a worry.



The real issue is that the graph includes tasks for all iterations. Obviously the solution is to have it only display data for the current iteration. But how can this be done?


The first thing you need to do is figure out what parameters need to be passed to Reporting Services. As it turns out, that’s easier said than done. The “Remaining Work” report is driven from data in Analysis Services. The Iteration parameter is called “IterationParam” and expects values read from the corresponding dimension in the OLAP database. Cutting to the chase, you can’t just provide text such as “Iteration 1” as a value for this parameter. To illustrate, here a URL to display the remaining work for Iterations 0 and 1 of my project:




Ugly isn’t it? Focus on the first IterationParam value. After URL decoding, this equates to IterationParam=[Iteration].[Parent_ID].&[19]. This may make sense to those that have worked with Analysis Services before. In short, it is identifying the member of the Iteration dimension that has a key of 19. In my case, this corresponds to “Iteration 0”. In your case, it could be anything.


The easiest way I’ve found of determining the key to use is to look up the relevant __ID from the TfsWarehouse.dbo.Iteration table in the TFS data tier. All you need to do is find the Iteration Path you’re interested in and read the corresponding key from the __ID field. Armed with this knowledge, you can construct a report query like the one above. In case you haven’t noticed, you can nominate multiple iterations to report on by providing several IterationParam parameters to Reporting Services.



In my next post, I’ll link this into the Project Portal.