Partager via

Using Excel reports with Microsoft Test Manager to identify test state at a test plan level

  • Article

From Microsoft Test Manager, it is possible to identify the state of test cases (pass, fail, blocked etc) for a particular test suite. You can just select the test suite on the left side and the right pane would show all test cases with their test status.

Figure 1 – Selecting a test suite shows the state of all test cases in that test suite

However, getting the same information at a test plan level is not easy. In the Test Plan properties view, you can see a pie chart indicating the state of all test cases in a given test plan.

Figure 2 – Pie chart in test plan properties indicating overall state of all test cases in the test plan

But, it is not possible to drill down deeper to identify which if the test cases are failing in the test plan.

The workaround – Creating your own Excel Reports

The workaround is to create a report in Excel. In Excel, you can create a new report as shown below:

Figure 3 – Excel report showing state of all test cases (grouped by configurations) in a test plan

Figure 4 – You can drill down to identify failing test cases in a particular test plan

Steps to create this Excel Report

Figure 5 – The PivotTable used to create the Excel Report

Once you’ve created a PivotTable report (steps 1 to 6 given here), you can create a report with the following fields:

Report Filter:

1. Team Project. [Team Project Hierarchy] – Select the team project for the report

2. Test Suite.[Test Suite Hierarchy] – Drill down to select the test plan for the report.

Row Labels:

1. Test Configuration.[Configuration Name] – Test Configurations selected under the report filters.

2. Test Case.[System_Title] – The names of all test cases, grouped by test configuration

Column Labels:

1. Test Result.[Outcome] – the state of the test case (failed, passed, never run, error, blocked or aborted)

Values:

1. Test. [Point Count Tread] – indicates the count of all test points (test cases x configurations) satisfying the row/ column labels

Comments

  • Anonymous
    March 29, 2011
    What is the exact query used to export the data to Excel? The TSF administrator on our team is unable to install SQL Server Analysis Services, so the only way to extract data at this point is through building a query in Visual Studio.

  • Anonymous
    March 31, 2011
    Excel  reports is great way to percent worksheet database for the audience.  When we use Microsoft report it will  easier for use with sql database and reports. And great tutorials about excel report you can now get at this niche, Follow it. <a href="www.excelreports.info/">Excel Reports</a>

  • Anonymous
    March 31, 2011
    Excel  reports is great way to percent worksheet database for the audience.  When we use Microsoft report it will  easier for use with sql database and reports. And great tutorials about excel report you can now get at this niche, Follow it. <a href="www.excelreports.info/"> Excel Reports </a>

  • Anonymous
    April 02, 2011
    Excel website, has a great Excel tutorial. Highly recommended for people creating reports in Excel http://www.excelreports.info/ www.excelreports.info/.../excel-reports-tutorial.html

  • Anonymous
    April 06, 2011
    This has some great tips for crating reports. I also use this to create the actual output of the statement reports in Excel that I create. www.oneclickcommissions.com

  • Anonymous
    April 19, 2011
    I like using Gantt charts for project management.  What you have here is pretty similar. -Grahm I.E. 7.0+ www.oneclickcommissions.com/excel-report.html -or in Safari- www.oneclickcommissions.com/excel-reports.html

  • Anonymous
    June 15, 2011
    The comment has been removed

  • Anonymous
    July 29, 2011
    The article is very helpful! Thank you, I'll be sure to use your advice. Personally, I had some problems converting excel files over to PDF. I got so annoyed from manually moving the information (such as commission reports, invoices, etc.) over to another document, but then I found this program at my work that quickly and painlessly converts my excel documents into PDF and ready to go templates and reports, saving me aggravating hours upon hours. It provides my business a simple way to make individualized statements for our business associates, taking our invoices and seamlessly placing the information in the awesome templates. Check it out <a href=“www.nirvaha.com/excel-to-pdf.html”>here</a>.

  • Anonymous
    January 17, 2012
    Interesting post, thanks! Here is another dashboard for testmanagement made in Excel: qastuff.wordpress.com/.../simple-excel-dashboard-to-monitor-your-test-progress

  • Anonymous
    August 29, 2012
    How to load report from a previous build that was assigned to the project?

  • Anonymous
    October 18, 2012
    Great write up! Very useful. Now, if only had an easier way to get at the steps results. Cheers!

  • Anonymous
    October 30, 2012
    We are using excel reports to report tests run for a suite in a plan. But the numbers in Test manager do not match the numbers in Excel report ? Should there be a reason for this ? Regards, Himal.

  • Anonymous
    April 05, 2013
    Hello, I am facing the same problem as the above comment. Could someone respond plz. "We are using excel reports to report tests run for a suite in a plan. But the numbers in Test manager do not match the numbers in Excel report "

  • Anonymous
    September 01, 2014
    Hi, I tried to generate the report as per the above method , but under the outcome its showing total number of test cases for each category (Blocked, failed, passed etc). Please help me to resolve this. Thanks