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

Previous posts:

TCM Reporting - FAQ - Part 1

Continuing on with series, here are the next set of frequently asked questions related to Reporting with Test Case Management (TCM) in Visual Studio 2010.  

FAQ List - Part 2

1. I would like to create custom test reports in Excel based off the Analysis Services cube. How should I go about it?
2. What are the different Test related measures in the Analysis Services cube that I can use to create my custom test reports?
3. What are the different Test related dimensions in the Analysis Services cube that I can use to create my custom test reports?
4. Can I control what information makes it into the warehouse/cube? If so, how?
5. I don’t see any way to report on what steps were passed/failed during manual testing using the warehouse/cube. How can I achieve this?
6. I want to write a custom 3rd party warehouse adapter, how should I go about it?

Details

1. I would like to create custom test reports in Excel based off the Analysis Services cube. How should I go about it?

There are 2 ways of doing this, start from scratch or use one of the existing out-of-the-box Excel reports as a starting point.

Start from Scratch: You can always start creating a new Excel report by adding a new data source, as mentioned below:

a. From “Data” tab, click on “From Other Sources->From Analysis Services” option

b. Provide the name of your TFS server.

c. This should connect you to the TFS_Analysis database. If your data warehouse for Visual Studio ALM is using SQL Server Enterprise Edition, the list of cubes will include Team System and a set of perspectives. The perspectives provide a focused view of the data so that you do not have to scroll through all of the dimensions and measure groups that are defined for the whole Team System cube. Choose the CUBE or the TEST perspective to create test related reports.

d. Save the connection information locally or it can also be saved on a SharePoint.

e. Select where you want to create the PivotTable Report or PivotChart

f. Drag and drop various dimensions and measures from Pivot table field list to construct your custom report.

Use existing out-of-the-box Excel reports as a starting point: You could also start with any of the existing out-of-the-box Excel reports and then customize it to suit your needs by dragging/dropping the different measures/dimensions.

The simplicity of Excel reports (of just using drag/drop of dimensions/measures) is one of the main reasons why you will see a lot more out-of-the-box reports for Excel rather than Reporting Services based reports.

2. What are the different Test related measures in the Analysis Services cube that I can use to create my custom test reports?

All the test related measures present in the Analysis Services cube are shown below:

Description for each of above measures can be found here:

https://msdn.microsoft.com/en-us/library/ms244708.aspx#measures

A list of all the perspectives and measure groups present in Team Foundation Server can be found here:

https://msdn.microsoft.com/en-us/library/ms244710.aspx

3. What are the different Test related dimensions in the Analysis Services cube that I can use to create my custom test reports?

All the dimensions present in the Analysis Services cube are shown below:

Description for all the Test specific dimensions can be found here:

https://msdn.microsoft.com/en-us/library/ms244708.aspx#dimensions

4. Can I control what information makes it into the warehouse/cube? If so, how?

Here is a quick primer on the different components of data warehouse and how data moves from the Operation store to Warehouse to Cube using the warehouse adapters.

Components of the Data Warehouse for Team Foundation

Yes, you can control what goes into the warehouse - refer to below topic for details around work item specific fields:

Adding and Modifying Work Item Fields to Support Reporting

Note that you can use the above to control Test Case Work item field customizations. However, since the other test artifacts (Test Suites, Test Runs, Test Results etc.) are not work items, it is not possible to use the above approach.

5. I don’t see any way to report on what steps were passed/failed during manual testing using the warehouse/cube. How can I achieve this?

That is correct - Test Steps along with expected results and the passed/failed information is not present in the warehouse/cube. The entire Test Steps (including Step and Expected Result) is a HTML based custom control, hence this information is not pushed into the warehouse. As a result you cannot create reports for the same using the cube.

However, there are couple of ways of getting this Step related data:

TFS APIs: You could get this information using the TFS APIs  

Refer to a sample Test Steps Report, which was created using the TFS APIs.

https://geekswithblogs.net/TarunArora/archive/2011/10/02/mtm-testing-scorecard-using-tfs-api.aspx

Since this sample also has source code, you could customize it to suit your organization needs.

Test Scribe: You could also use Test Scribe tool to generate a document containing Test Steps.

There are 2 modes of generating the document using this tool -

"Test Plan Summary" view which you can use to review/sign-off the Test Plan & its associated test cases along with individual test steps/expected results/iteration values.

"Test Run Summary" view which you can use to get detailed Run level information - however it does not provide a breakdown of individual test results within a particular test run.

6. I want to write a custom 3rd party warehouse adapter, how should I go about it?

You can find a Sample custom warehouse adapter below, that you can use as a starting point:

https://code.msdn.microsoft.com/Tfs2010SampleAdapter

-Ravi Shanker