Report Builder is an awesome tool in lieu of using Microsoft Office Excel or Business Intelligence Studio to create custom reports with SQL Server 2008 Reporting Services.  The version of Report Builder that shipped with SQL Server 2008 is Report Builder 1.0.  Report Builder 2.0 was later released with plenty of new features and a better report writing experience.

The only issue though is that the most discoverable way to install Report Builder using Click-Once is through Report Manager but it points to Report Builder 1.0 even after you install Service Pack 1 for SQL Server 2008.

If you installed Service Pack 1 for your Reporting Services instance, there is actually a way to change the behavior though to point it to the Report Builder 2.0 Click-Once install.  Click “Site Settings” in the upper-right hand corner and then fill out the Custom Report Builder Launch URL to “/ReportBuilder/ReportBuilder_2_0_0_0.application” if your server is setup in Native mode (which it should be if it is the RS instance for TFS) or “/_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application” if it is in SharePoint Mode.

Alternately, if you want to download the full MSI installer you can over at Microsoft Downloads:  http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en.  Thanks to the Reporting Services Team Blog for the handy information.

Ed Blankenship

Grant Holliday, former Team System MVP, is now working on the VSTS product group on various things but most importantly helping keep the internal Microsoft TFS servers in tip-top shape.  They have the largest known TFS instances in the world so you could imagine it’s got to be tough keeping it up and running well.  Brian Harry posts their usage metrics regularly if you’d like to get an idea of how HUGE it is.

Keeping the performance manageable requires lots of monitoring and looking over the data that gets stored in the activity logging.  Grant has packaged up some of the reports they use internally and provided them to the world.  The download package is available on his blog post.

Personally, I’m really glad that the product group is investing in dogfooding the early builds of the version of TFS that they are working on.  By putting the product under real use and under the load that only Microsoft can generate, we end up with a better product by the time it RTMs.

Thanks again for making this available to the world!

Ed B.

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...