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

[アーティクル]
04/01/2010

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:

This 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:

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.

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

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 -

Create a New Folder under Project Management (or whatever) named for the Iteration/Sprint (like RTM)
On the properties page for the default report (like Stories Progress), click Create Linked Report
Give the new linked report a “Name” and description, like the following: Name = RTM Sprint Story Progress Desc: RTM Sprint
For Location, click Change Location and select the folder created in Step 1
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:

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:

With this configuration, your Stories Progress report will return -

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:

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

Open SQL Server 2008 Management Studio
Connect to the TFS Warehouse (Database: TFS_Warehouse)
Click New Query

In the query pane, use the following:

 select IterationSK, IterationPath
from DimIteration
where IterationName like '{insert your iteration name here like RTM}'

Use the value in InterationSK that matches the Iteration Path for your current sprint (or future)
Open the parameters for the report (Stories Progress or Stories Overview), select IterationParam, insert the value from the lookup
Click Apply
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.

Summary

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!

Thanks,

-Chris

Comments

Anonymous
January 01, 2003
Hi Bob, Sorry this didn't work for ya... What reports were you attempting to edit out of curiosity? I wouldn't mind re-testing on this end. Besides that, could you maybe drop me a quick screen shot via email (see email on my blog) of what values you changed in parameters? I will do my best to help ya out - I know it works as we use it here all the time! Thanks, -Chris
Anonymous
January 01, 2003
So do you actually come back and do this routine every single iteration to maintain your dashboard? Is there no other way? This is frustrating when managing multiple dashboards and mutlipe reports like this.
Anonymous
January 01, 2003
Hey Russ- Unfortunately, the answer is yes. This could be automated a bit though we've not go around to doing it due to other pressing matters. The nice thing is if you build the report as a "linked" report then you can copy/paste the report and just change the iteration. However, you are right, it doesn't scale well when you have as you mention multiple dashboards with multiple iterations going at the same time. You will be very busy if this is the case. I know your frustration, for sure. If you are on MOSS, there are some advantages you get using the Excel reports as they have the ability to be modified easier than the reports. On the other hand, you can also switch to the Visual Studio SCRUM 1.0 process template as it makes life much easier with its sprint & release reports. Thanks, -Chris
Anonymous
January 01, 2003
Extremely useful! Thank you for sharing this!
Anonymous
January 01, 2003
Hi tfsusers/Alfonso- Thank you & I'm glad that it helped you. -Chris
Anonymous
January 01, 2003
Excellent post! Thank you Chris! Thank you John Socha!
Anonymous
June 21, 2010
I tried this with our install of TFS. When I entered the IDs in for Area and Iteration, saved clicked View, the Iteration and Area drop downs were blank and clicking View Report resulted in an error message telling me that Iteration could not be blank.
Anonymous
August 09, 2010
My Test Plan Progress is not showing all Iteration and Area values in filter-menus. Iteration menu shows only the Project and not values. Area menu shows 3 values, but there should be 7. As a result, I can't report a test progress by iteration or area. What's the problem?
Anonymous
December 10, 2014
This is very clever, and I have to ask why it is important to have this kind of report if you're doing Agile? One of the foundations of Agile is that people estimate poorly. That's why Scrum uses a burndown chart (work remaining) rather than a burnup chart (work remaining and work completed). From the charts you are showing, I don't see why you couldn't use MS Project and save yourself a lot of trouble.

次の方法で共有