Test Case Management (TCM) Reporting - Frequently Asked Questions - Part 1

Article
10/14/2011

In the next few blog posts, we will review some of the frequently asked questions related to Reporting with Test Case Management (TCM) in Visual Studio 2010.

FAQ List - Part 1

I am using Microsoft Test Manager (MTM) for my testing activities - what type of reports can I see within MTM?
I would like to create reports in Excel using Work Item Queries, but I cannot do so for all the Test artifacts such as Test Suites, Test Runs etc? Why?
The reporting information shown within MTM is very basic in nature - are there any other Test reports that ship out-of-the-box that I can use?
I am connected to my Team Project within Team Explorer, but I don’t see any of the Reporting Services based Reports listed above. Why?
I am connected to my Team Project within Team Explorer, but I don’t see any of the Excel Reports listed above. Why?
We don’t plan on installing SharePoint - is there no way of getting the "out-of-the-box" Excel reports, primarily for Test related reports?
What are the steps involved in changing the data source of an Excel Report?

Details

1. I am using Microsoft Test Manager (MTM) for my testing activities - what type of reports can I see within MTM?

You can view basic reporting information within Microsoft Test Manager:

- Test Run status at a Test Plan level:

This provides a status of all the tests that have been run, along with their current outcomes, within that specific test plan. This data is aggregated across all suites within the Test Plan. You can view this from the Test Plan Properties page.

- Test Run status at a Test Suite Level:

This provides a status of all the tests that have been run, along with their current outcomes, within that specific test suite. This information can be seen on both the Test Activity Center (as shown below) or Plan Activity Center.

Note: The data shown at a suite level includes the tests that are explicitly within that suite node only - it does not include all the child suites within that suite.

2. I would like to create reports in Excel using Work Item Queries, but I cannot do so for all the Test artifacts such as Test Suites, Test Runs etc? Why?

Creating Reports in Microsoft Excel by Using Work Item Queries

You can use the above method to query for any TFS "Work Items". So, you should be able to create a report for "Test Case", "Bug", "Task" work items.

However, all the other Test artifacts, such as Test Plan, Test Settings, Test Configurations, Test Suites, Test Points etc are not "work items". Hence, you cannot use the work item queries for them.

All the test artifacts which are enclosed within the black box (in above diagram) are not work items but they are stored directly within the Test Case Management store.

3. The reporting information shown within MTM is very basic in nature - are there any other Test reports that ship out-of-the-box that I can use?

Most of the out-of-the-box TFS reports, including Test related reports, can be accessed from within Team Explorer.

There are 2 types of reports that can be viewed from Team Explorer - Excel Reports and SQL Reporting Services Reports.

1. Excel Reports: These reports use Excel to connect to the TFS warehouse/Analysis cube to display the reporting data. The figure below shows the list of all Excel reports that ship out of the box. Depending upon the process template (Agile, CMMI) you used at the time of creation of the project, the respective reports are created at project creation time.

All the test specific reports are highlighted in the above diagram.

Details of each of the Test related reports can be found below:

Test Management Reports

Build Quality Excel Report

Test Team Productivity Excel Report

Test Team Progress Excel Report

Testing Gaps Excel Report (Agile)

Testing Gaps Excel Report (CMMI)

Failure Analysis Excel Report

Test Activity Excel Report

Test Case Readiness Report

Test Plan Progress Report

User Story Test Status Excel Report (Agile)

Requirement Test Status Excel Report (CMMI)

2. Reporting Services based Reports: The figure below shows the list of all Reporting Services based reports that ship out of the box. Depending upon the process template (Agile, CMMI) you used at the time of creation of the project, the respective reports are created at project creation time.

All the test specific reports are highlighted in the above diagram.

Details of each of the Test related reports can be found below:

Build Quality Indicators Report

Stories Overview Report (Agile)

Requirements Overview Report (CMMI)

Test Case Readiness Report

Test Plan Progress Report

4. I am connected to my Team Project within Team Explorer, but I don’t see any of the Reporting Services based Reports listed above. Why?

If you want to use the reporting feature (Excel Reports or Reporting Services Reports) in Team Foundation Server, you must have an installation of SQL Server Reporting Services and SQL Server Analysis Services. These features can be installed on the same server or different servers.

Team Foundation Server requires a report server that is dedicated only to its use. You cannot share the report server with any other application.

Links:

Installing Team Foundation Components

Configuring Resources to Support Team Projects

Add a Report Server to Your Deployment

Add a Report Server to a Team Project Collection

Add Reports to a Team Project

5. I am connected to my Team Project within Team Explorer, but I don’t see any of the Excel Reports listed above. Why?

If you want to use the reporting feature (Excel Reports or Reporting Services Reports) in Team Foundation Server, you must have an installation of SQL Server Reporting Services and SQL Server Analysis Services. These features can be installed on the same server or different servers.

Team Foundation Server requires a report server that is dedicated only to its use. You cannot share the report server with any other application.

In addition, for Excel Reports, you also need to enable SharePoint on the Team Foundation Server because all the Excel reports, by default, get copied and stored in the SharePoint Document Library at the project creation time.

Links:

Installing Team Foundation Components

Configuring Resources to Support Team Projects

Modify or Remove Access Between a SharePoint Web Application and Team Foundation Server

Add a SharePoint Web Application to Your Deployment

Configure a Default Location for Team Project Portals

6. We don’t plan on installing SharePoint - is there no way of getting the "out-of-the-box" Excel reports, primarily for Test related reports?

You can copy the Excel Reports from a different Server/Project where SharePoint has been configured and use them for a server without SharePoint.

If you don’t have any installation of Team Foundation Server with SharePoint, then you can download the 2 attachments (Dev10-Agile-ExcelReports.zip and Dev10-CMMI-ExcelReports.zip) at the end of this post, change the data source within Excel to point to your TFS server and start using them.

7. What are the steps involved in changing the data source of an Excel Report?

For changing the Data source on the Excel reports (Dev10-Agile-ExcelReports.zip and Dev10-CMMI-ExcelReports.zip), follow the steps mentioned below:

1. Open one of the Excel Reports file, go to the “Data” tab in Excel, then click on “Connections”

2. It should open up the following window, Click on “Properties”

3. Connection Properties window opens up, click on “Definition” tab

4. In the Definition tab, update the connection string and then keep clicking on OK all the way back.

Change the Connection String

FROM:

Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=ravidev10tfs;Location=ravidev10tfs;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

TO:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=<yourtfsserver>;Location=<yourtfsserver>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

This should start getting the reporting data from your TFS Server.