TFS 2010: Using Stories Progress in your Dashboards but scoped to Iteration or Areas

As you’ve seen in my post previously, there are some great ways to enhance reporting in TFS 2010 through the use of Windows SharePoint Services (WSS).  This is very important for obvious reasons such as communicating with organization leaders on the status of your projects, especially when you are using agile/scrum based methodologies.  In a recent review with our leadership team, it was asked that upper management have visibility into the scenarios which our projects are tackling on a per sprint basis.  This makes sense when obviously management isn’t always clear as to what “features” are getting added versus what regression and bug work is getting tackled in a new sprint.  TFS 2010 gives you, when using MSF Agile, some great built-in reports such as Stories Progress & Stories Overview.  The key thing is that, unfortunately, you are going to have some difficult out-of-box getting these reports scoped to a specific sprint/iteration which is the goal when you are showing work progress to management (very seldom do you show stories progress that are in the backlog).

In this post, I will focus on sharing what you should know in order to effectively utilize reports such as Stories Progress & Stories Overview in your self-made Dashboard.

Defining the Goal:  Setup Dashboard Widgets for Stories Progress

You’ve got powerful user stories you are working from that is driving your development effort and solutions.  It is important to give everyone visibility, including management, technical staff not on the immediate team, as well as your team members direct insight into how their effort is progressing (or for those half-empty types, not progressing <grin>).  You’ve set out to utilize the stories progress (or overview) reports and you’ve noticed that, unfortunately, they show all stories entered in the backlog.  If you are like our team, we often have user stories (or DCRs) that come from product owners who simply reach for the stars and the stories albeit legit would take a army size of a million to complete.  Do you really want management to see the progress is ‘0’ for these backlog items that your team hasn’t accepted to work on?

Out of the box, your stories progress reports will look something like the following:

imageThis might work for some organizations but for our development team we wanted to avoid a report that focused more on ‘0’ than on the hard work we were putting in for current sprint.

Easy:  Edit the Iteration Param and off you go…

The first thing that many will do is jump into the report in SSRS and will see what they can do to restrict this report to only the sprint they care about.  Let me caution you a bit on how to do this the most effective without messing up the “out-of-box” reports that come with TFS (besides, they might be useful for something in the future).

The first thing to do is to edit the report – but not the default one.  The steps to edit the report are the following:

image On the TFS projects reporting site (Home > TFS Reports > [Project Collection] > Project, click Show Details in the upper right-hand side of the screen.  Assuming you have the proper rights in SSRS, you will then be able to select ‘Edit’ from the menu and modify the report.

image When you click edit, you should then see the following screen for the report -

image You now have the option of editing the report directly that is in-box.  Another method, though, is to use Visual Studio 2010 and select Reports and “copy” > “paste” the report in the same folder.  Visual Studio will then create the new report with the name “Copy of [Report Name]” that you will then edit in the above Properties.  This, though, is messy and overkill and not the method that is recommended.  Starting out, I didn’t realize this but I learned from the error in my ways.  <grin>

With that said, the most effective method is to do the following -

  1. Create a New Folder under Project Management (or whatever) named for the Iteration/Sprint (like RTM)
  2. On the properties page for the default report (like Stories Progress), click Create Linked Reportimage
  3. Give the new linked report a “Name” and description, like the following: Name = RTM Sprint Story Progress  Desc:  RTM Sprint
  4. For Location, click Change Location and select the folder created in Step 1image
  5. Click OK

I realize that I’ve not really shared anything magical but instead just helped you save some time and help you for organization a bit as you make these reports.  We often create a hidden folder called ‘Archive’ that isn’t shown in the default list view that we move folders to have the sprint is complete.  That way we still have access to the reports in the future if necessary.


Editing Parameters:  Updating Linked Report to be Sprint Specific

The following is where the million dollar answer comes into this and I’ve seen nowhere this is documented.  In fact, I had to meet with the developer on the TFS product group to get the answer on how to effectively do this.  The first thing to note is that any report which has the following type parameters “####MDXParam” is not a report based on the TFS cube (TFS_Analysis) but instead is a SQL-based report. 

How to determine if it is a cube-based report vs. SQL Report:

image If the above exist in the report, then you know that it is a SQL based report and you will need to move forward.

Thus, you will not be able to follow the typical behavior of editing the IterationParam value to scope the report to a sprint (like M9 or RTM.)  In fact, if you do this your report will come back each time with the Iteration and\or Area parameter blank as shown in the following:

imageWith this configuration, your Stories Progress report will return -

image This produces the following output:


Effectively Determining the correct value for the IterationParam/AreaParam for any SQL-based reports used in TFS 2010

Unlike when using cube-based reports where you download MDXStudio, connect to the cube, and then locate your iteration and\or parameter and determine the value through drag and drop, you don’t do this for SQL-based reports.  Instead, you utilize your friend SQL Management Studio. 

Using MDXStudio to determine iterationParam & areaParam for cube-based reports:

imageFor SQL based TFS reports (ones that have IterationMDXParam/AreaMDXParam), you do the following to determine the correct value for your iteration/sprint:

  1. Open SQL Server 2008 Management Studio

  2. Connect to the TFS Warehouse (Database:  TFS_Warehouse)image

  3. Click New Query

  4. In the query pane, use the following:

     select IterationSK, IterationPath
    from DimIteration
    where IterationName like '{insert your iteration name here like RTM}'
  5. Use the value in InterationSK that matches the Iteration Path for your current sprint (or future)

  6. Open the parameters for the report (Stories Progress or Stories Overview), select IterationParam, insert the value from the lookup

  7. Click Apply

  8. Click View and watch the magic occur…Bam – you got your scoped view of progress for a sprint!

NOTE: For Area Path, use where AreaPath instead of IterationPath

After you’ve done this, your view should return a report that matches your sprint and not all information.  Thus, for our example listed above we should now have all the items in the backlog removed.



This has been so much fun learning some in-depth aspects of TFS 2010.  Like I said, TFS 2010 is the real deal and amazingly powerful across the board.  It does a great job of helping folks run agile (and non-agile) projects and with little or no overhead from the team.  If the team does their part to ensure they enter correct information into their tasks/bugs for things like effort (Remaining Work, Completed Work) and then closes users stories then TFS 2010 & its reports are magical!

I want to say special thanks to John Socha, developer extraordinaire for the TFS team.  Without his personal assistant, I would be building my own reports in Report Builder and been angry, angry, angry! 

After you’ve followed these steps in this blog, now just add your webpart to your dashboard with the path to the linked report and your dashboard now includes awesome stuff!




Digg This