Creating Custom Team Foundation Server Reports

If you’re currently using Team Foundation Server (TFS) then you already know about the standard reports that come with a specific template (e.g. Scrum, MSF for Agile, etc.). If these canned reports (or even a customized work item query) retrieve all the data you need, then your journey ends here. If however you do need additional reports then the following should be beneficial.

The beauty of TFS is that it leverages SQL Server and with it comes SQL Server Reporting Services (SSRS). Therefore the foundation for extensibility has already been laid; you just need to know how to build upon it. Don’t fret as it’s not as hard as you might imagine. Realistically once the first one is complete, the rest are sure to be even easier.

First off, verify you have BIDS (SQL Server Business Intelligence Development Studio) installed as this is what’s going to do the heavy lifting. This is where you’re going to create the report and ultimately publish it to the reporting server.

After selecting a “Report Server Project [Wizard]”, follow the steps to connect to a data source, build your data set, add the fields from your query to the report, choose a layout and finally name the report. Believe it or not, you’re practically done. While I’m abbreviating some items others need to be explained in a bit more detail. Such as:

  1. If you’re interested in TFS specific data then you should choose the “Tfs_Warehouse” database as that’s predominately where you’re going to get this type of data from.
  2. You’ll need to determine the SSRS server name and location of where you want to deploy this report. This is important as you’ll want to publish this report in the same collection as your project so it will show up within Team Explorer (more on that part later).

In my fictitious example, I need to see what work items are assigned to which team members and the area and iteration they’re contained within. Yes, this can be done without the need for a report but it’s the process I'm interested in and not so much the data.

So, now that I have the report all designed and ready to go I need to publish the report to the reporting server. Simply right clicking on the report (or the project for that matter) and clicking “Deploy” is all that’s needed. If all goes well you should see something similar in your Output window:

Now let’s go to the reporting site and confirm that the report I just published is indeed there. The report I’m concerned with is “WorkItemAssignments” and sure enough it’s there in all its glory:

Clicking on the report then obviously yields the results I'm looking for:

Great, I now have the report on the site and accessible through a browser but wouldn’t it be great to have it visible and launch directly from Team Explorer? This is where having the correct deployment folder structure is key as since we deployed it to our project collection; it will automatically show up just like a standard report without us doing anything.

Everything looks good so it’s time to check-in the solution/project so it’ll be available to the rest of the team. Now that’s it’s within TFS, I can leverage the traceability and code maintenance aspects that TFS so easily provides.

As you can see, it’s a relatively simple and painless process to incorporate custom reporting into a TFS project. Last but certainly not least, MSDN has a wonderful tutorial on creating a basic table report and other related concepts that is worth reviewing.