How to Create Custom Test Execution Reports in Excel from Team Foundation Server - Part 2

Miguel Nepomuceno, 26 September 2017

In my previous blog, I’ve gone over setting up Team Foundation Server (TFS) such that we can use queries. TFS queries allow us to group and filter out work activities in TFS (primarily bugs). By setting up our queries to modularise our data and connecting TFS into Microsoft Excel, we can now extract our data such that we can construct our test execution report.

A good way to organize this is having the individual TFS queries in separate individual sheets within the same Excel file (for example, the TFS query shown above called “Active Bugs – Magnetism Sub Group Changes – CRM”). Then have a main report sheet used to store summary tables and charts (for example, Excel sheet shown below called “Test Execution Report Main Page”). This way, we can have a centralized location to summarize bugs in our project via tables and charts. A viewer who wants a brief overview of the test execution report can view this main report page. At the same time, having the separate individual sheets within the same Excel file offers the option of viewing the individual bugs under a query if needed.

The wonderful thing about Excel is that it gives you the capability to pull in data from different sheets in the same Excel file. For example, I used the COUNTA function to pull in the total number of Active Bugs from another sheet called ‘Active Bugs’ in my example spreadsheet.

The COUNTIF function is also used to count the total number of cells given a certain criterion. This can be useful for getting a count of different conditional types, e.g. bug severity types. I used the following COUNTIF cell notation to pull in the total number of Critical Bugs from sheet ‘All Bugs’. It counts all cells in the Severity column in the sheet ‘All Bugs’ given the condition cell contains the word ‘Critical’ (‘contains’ notation denoted by asterisk *).

 
The wonders of the TEAM connector in Excel is the minimal effort of updating the data once everything has been setup correctly. The user only needs to click the Refresh button on each sheet every time we would want to update our test execution report. The way the functions are built in Excel means that our data and total tables are updated automatically.

From here, the fun part begins! It’s all up to you to make use of the varied visualisation features Excel has to offer. Simply use the data tables we have used to draw in data from TFS to construct Excel-lent visuals! Visualisation charts and graphs of the test execution report gives the user a more effective, efficient and appealing way to present and summarize data.

Excel is a powerful tool to use for generating test execution reports and beyond. Once we are able to connect TFS with Excel with the correct intended queries from TFS, we are free to use Excel’s provided tools and functionalities. Excel provides us with tools to filter data via formulas, visual representation, and data analysis. Organized correctly, we can centralize our test execution data to provide viewers simplicity with summary tables and charts. Have fun configuring your reports!