So, if you didn't know I'm a huge fan of the Quality Indicators report in the MSF for Agile Software Development process template in Team Foundation Server.  Here at Infragistics, we're evaluating the Conchango Scrum Process Template for Team System and I don't get that wonderful report I love :-(  I've been stalling (because I haven't ever written a SQL Reporting Services custom report for TFS) but I finally did it today.  It really wasn't that bad.  Thank God I already had most of the report set up for me!  There are a TON of data and metrics you can get from the OLAP cube in the TFS Data Warehouse.

If you haven't read the Wrox Professional Team Foundation Server book, then it's a great avenue for learning TFS.  It also has a good section on how to creating custom reports with the Visual Studio Report Designer.

Really the only issue with just importing the original report to the Scrum team project is the fact that Bugs are actually Sprint Backlog Items with an Item Type of Bug (as opposed to the MSF Agile template that just has a separate bug work item type) and the status of an active bug is actually "In Progress."

Steps to Modify Report

1. You need to get a copy of the Quality Indictors RDL file.  Take a look at Customizing Reports to get more information on how to do this.  I happened to create a whole new Reporting Services Project in Visual Studio called TFS Scrum Reports because I may be creating some new ones later.
2. Be sure to add the two shared Data Sources to your new Visual Studio project.  You can use the settings of the existing shared data sources on your TFS SQL Reporting Services server.  (Usually found at http://[TFS Server Name]/Reports/ or you can right-click the Reports folder under your team project in the Team Explorer window and choose "Show Report Site...")
    
3. With the report open, navigate down to the section that gets the Active Bugs metric:
    
4. Notice what it grabs for the work item type and state:
   
   MEMBER [Measures].[Active Bugs] AS ([Measures].[Cumulative Count], [Work Item].[System_State].[Active], [Work Item].[System_WorkItemType].[Bug], STRTOMEMBER([Measures].[Build Date])) + 0
   
5. Now, change these values to the following:
   
   MEMBER [Measures].[Active Bugs] AS ([Measures].[Cumulative Count], [Work Item].[System_State].[In Progress], [Work Item].[Conchango_VSTS_Scrum_SprintBacklogItemType].[Bug], STRTOMEMBER([Measures].[Build Date])) + 0
   
6. All you have to do now is save the report and upload it to the SQL Reporting site in your team project's reports folder.
   
7. Last thing, you may run into a problem with the report finding the shared data sources.  If this happens, go into the properties of the report on the SRS site and choose the shared data sources that are being used by SRS.

Really, you can use this method to port this great report into just about any process template you might be using.  Pretty much any of the OLAP metrics can be changed.

What other kinds of reports should we have?  Do you have any interesting custom reports that you find useful for the major process templates that are available on the Internets and what not?

UPDATE:  I've attached a modified report if you don't want to go through all the steps of customizing.  Just download it and start at Step 6.  It's much more fun doing it yourself though!

Ed B.

P.S. We're changing up some of our tags to better organize all of our blog posts.  May take some time...