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.

Comments (1)
  1. Aaron says:

    Why is TFS database so seemingly poorly structured? It's hard to find anything in that database and the one I am working on doesn't seem to have the same structure that you have here. I'm using TFS 2012 and it only has TFS_Warehouse and TFS_ClientWork in it. I can't find any keys I would need in TFS_ClientWork and TFS_Warehouse doesn't have the data I want in it.

    I'm trying to build a report that would generate work orders based on iteration path, it would create a list of requirements with their titles and descriptions (not in TFS_Warehouse) then it would loop through all the requirements and find all the child tasks and add them under the description of the requirement and these would be labeled "scope of work included".

    I can't seem to find estimates of hours or descriptions in TFS_Warehouse and in TFS_Clientwork, I can't use the report builders to join up iterations and projects (workspaces).

    The thing I don't understand is why the hell the naming conventions of the tables seem to be all over the place. DimWorkItem, tbl_WorkItems, FactWorkItems, WorkItemsAre…. etc… there doesn't appear to be rhyme or reason to me as to why this database looks to be structured like a novice SQL user set it up.

Comments are closed.

Skip to main content