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


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 (14)

  1. amyr@scandiasoft.com says:

    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.

  2. Chandana says:

    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="http://www.excelreports.info/">Excel Reports</a>

  3. Chandana says:

    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="http://www.excelreports.info/"&gt; Excel Reports </a>

  4. vimukthi says:

    Excel website, has a great Excel tutorial. Highly recommended for people creating reports in Excel

    http://www.excelreports.info/

    http://www.excelreports.info/…/excel-reports-tutorial.html

  5. RichG says:

    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.

    http://www.oneclickcommissions.com

  6. Looks familiar says:

    I like using Gantt charts for project management.  What you have here is pretty similar.

    -Grahm

    I.E. 7.0+

    http://www.oneclickcommissions.com/excel-report.html

    -or in Safari-

    http://www.oneclickcommissions.com/excel-reports.html

  7. VSTS Quality Tools says:

    You need to have SQL server Analysis services turned on to make Excel reports work. The Excel gets data from the Analysis Services cube, which in turn gets data from the TFS Warehouse on a periodic basis.

    Since test results, test plan and test suites are not work items, queries in Visual Studio cannot be used to build equivalent reports.

    If you already have SQM Server Analysis services (installed along with TFS), to rebuild the data warehouse and the Analysis Services database

    1. Open the administration console for Team Foundation.

    2. In the navigation bar, click Reporting.

    3. In Reporting, click Start Rebuild.

    4. In the Rebuild the Warehouse and Analysis Services Databases dialog box, click OK.

    Note: The warehouses will continue to be rebuilt and the data will continue to be repopulated after the Start Rebuild action finish. Depending on the size of your deployment and the amount of data, the whole process might take several hours to complete.

    You can also use the RebuildWarehouse command (msdn.microsoft.com/…/ee349264.aspx)

    To configure Warehouse settings

    Browse to http://servername:8080/tfs/TeamFoundation/Administration/v3.0/WarehouseControlService.asmx and use the GetSetting and ChangeSetting webservices to get/ set warehouse rebuild settings.

    Related Links:

    1. bartwullems.blogspot.com/…/refresh-tfs-2010-warehouse.html

    2. blogs.msdn.com/…/tfs2010-warehouse-and-job-status-reports.aspx

  8. Joseph Becket says:

    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=“http://www.nirvaha.com/excel-to-pdf.html”>here</a>.

  9. Kumar says:

    Interesting post, thanks!

    Here is another dashboard for testmanagement made in Excel:

    qastuff.wordpress.com/…/simple-excel-dashboard-to-monitor-your-test-progress

  10. Vikas says:

    How to load report from a previous build that was assigned to the project?

  11. Clint says:

    Great write up! Very useful. Now, if only had an easier way to get at the steps results.

    Cheers!

  12. Himal Shelat says:

    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.

  13. 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 "

  14. Star says:

    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