Today during the Deep dive in Visual Studio and MTM course we were covering the types of reports that can be produced in TFS. Either for tracking your tasks or your tests progress, these reports are produced from the same place and with the same mechanism. Let’s take a look to the next picture:

As we all know, the SQL Server database that is in TFS is in charge of storing and producing the right KPIs for our reports. Most of the information we query is directly extracted directly from the database where our project is hosted (our project collection DB). But most of the reports are produced directly not from the project DB but from the OLAP data cube that is managed by SQL Server Analysis Services.

So said that, we have two extra Data Bases, one is the OLAP Cube, TfsAnalysis data base, that is mainly use to generate reports in Excel or to export reports to SharePoint wherever you have an integration with it.

The other database, TfsWarehouse, is being used to generate reports through SQL Server Reporting Services, which give us the chance to generate also our own reports using the Report Designer tool. This reports are easily accessible through the Web Access of our team portal or from our Team Explorer in Visual Studio.

Either for TfsAnalysis or TfsWarehouse databases, you should have permissions to them both in order to create this reports.

For how to GRANT PERMISSIONS TO VIEW OR CREATE REPORTS IN TFS follow the next link.

SQL Server Reporting Services

When you finally get access to SSRS, you would be able to access to the folder above represented, and, depending on the project template that your team is using for the project, you will see less or more reports. But remember, the report builder is there!

Some of the reports are the next:

• Test and bug reports

  • Test Case Readiness
  • Test Plan Progress
  • Bug Status
  • Bug Trends
  • Reactivations

• Project Management Reports:

  • Backlog Overview
  • Release Burndown
  • Sprint Burndown
  • Velocity

All these reports and more are explained here

Probably the test case readiness is one of the most detailed and spectacular, showing us the next information:

This is an extended version of the simple chart that we usually take from the Microsoft Test Manager (Plan tab, Results menu), where we can analyse the Test Result Summary of our tests executions by tester, by suite, by configuration and answer to questions such as:

How much testing has the team completed?

How many tests are left to be run?

How many tests are passing?

How many tests are failing?

How many tests are blocked?

Why tests are failing?

Are new issues going into production?

Is there any regression on the failing tests?

Last but not least, another way to create reports in TFS is using Microsoft Excel.

These awesome pivot tables can be generated from either the Team tab in Microsoft Excel or from Visual Studio (straight from a query).

If you are creating the report from Visual Studio Team Explorer, you basically have to look for the query you want to use to generate the report, and then right click and create report in Excel.

If you decide to create the Report from the OLAP Cube, in Microsoft Excel there is a tab called “Data”. There you will find an action called “From other sources” where you can select “From Analysis services”

After you chose this option, a wizard will be introduce to you:

1. Connect to the DataBase server
   
2. Select DataBase and Table (Tfs_Analysis will be ours)
   
3. Give it a name and point it to our file
   
4. Report will be generated
   

I hope this blog post has thrown some light on what reports are available in TFS and do not forget… In terms of having these reports, you have to generate the data first! So make sure you create your work items such as tasks, user stories, test cases, and others properly or reports will be useless J

Enjoy!

Eduardo Ortega