Download a working demo (below): No showing off, this has been developed in VS11 using MTM11 and version controlled in TFS Azure 
In this blog post I’ll be showing you how to use the TFS API to build a testing scorecard. In this testing score card, I’ll be going through each of the test plans programmatically looping through test suits (yes, the test suits can be nested) looking for test cases and querying their status and further going in at the test step level to get the status of each test step individually. The outcome will look something like below,

We all love our testers, they work so hard to make sure that the software is delivered with utmost quality. I thought of creating a report which aggregated test step status, test case status, test suit status and test plan status and presented the result in one single view, this would really simplify their discussions during the scrum daily stand ups. But I hit a problem…
=> Problem
It is not possible to report on the actual progress of test steps in the test case in Microsoft Test Manager. The out of the box reports can tell you the pass/fail status at test case level, not individual test steps. There is nothing wrong with this, Microsoft test manager does not enforce the user to mark test step pass or fail at each level so there is nothing wrong with no out of the box support for reporting against test steps.

In the screen shot from Microsoft Test Manager you can see that the test steps have been marked as passed/failed now if i wanted to report on them, there were two possibilities
- Create a TFS Warehouse adapter that will process all data first from different tables/XML and then move to the TFS warehouse. But that might not be the best option since exporting data at the test step level to the warehouse would significantly increase the size of the warehouse and this is one of the reasons possibly why the build in warehouse does not process the test step result data.
You might turn around to ask why can I not use powerpivot over the tfs operation database tables where test results are stored and build my report off that. There are two reasons why that would not work,
1. Test step is a custom control where the data is stored as XML blob in database. The test result view shown in the screen shot above is possible because MTM (Microsoft Test Manager) actually parses the XML and correlates it with the Test result information before showing it in the MTM.
2. You should never query the TFS operation databases directly. Any interaction to the TFS operational database should be done through the TFS API.
- Use the Test Case Management (TCM) object model which is part of the TFS API to create a custom report. To be honest it is not too difficult going down this route. Read on for step by step of how I implemented this report…
If you are enjoying this blog post, don’t forget to subscribe to http://feeds.feedburner.com/TarunArora I’ll be exploring this space and posting further solutions…
=> Connect to TFS Programmatically using TFS API
I have a separate blog post on how to connect to TFS programmatically using the TFS API. In the below code snippet you can see I am getting a list of team projects using the VersionControlServerService.
public List<string> GetTeamProjects()
{
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<VersionControlServer>();
return service.GetAllTeamProjects(true).Select(i => i.Name).ToList();
}
=> Get all Test Plans in a team Project Programmatically using TFS API
I’ll be using the ITestManagementService to query for the test plans,
public List<string> GetTestPlans(string teamProject)
{
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<ITestManagementService>();
var testProject = service.GetTeamProject(teamProject);
return testProject.TestPlans.Query("SELECT * FROM TestPlan").Select(i => i.Name).ToList();
}
=> Get all Test Suits in a Test Plan Programmatically using TFS API
Now it gets slightly tricky the test suits can be nested.
// 1. Connect to TFS and Get Test Managemenet Service and Get all Test Plans
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<ITestManagementService>();
var testProject = service.GetTeamProject(teamProject);
var plan = testProject.TestPlans.Query("SELECT * FROM TestPlan").
.Where(tp => tp.Name == selectedTestPlan).FirstOrDefault();
List<ScoreBoard> scoreboards = new List<ScoreBoard>();
var testSuits = new List<IStaticTestSuite>();
if (plan.RootSuite != null)
{
testSuits.AddRange(TestSuiteHelper.GetTestSuiteRecursive(plan.RootSuite));
}
TestPlan testPlan = new TestPlan() { Id = plan.Id, Name = plan.Name, TestSuites = testSuits };
So, I’ll be using a simple stack to push a test suite get all related test suits and pop the original test suit and recursively perform this operation

static class TestSuiteHelper
{
public static List<IStaticTestSuite> GetTestSuiteRecursive(IStaticTestSuite b)
{
// 1. Store results in the IStaticTestSuit list.
List<IStaticTestSuite> result = new List<IStaticTestSuite>();
// 2. Store a stack of our TestSuite.
Stack<IStaticTestSuite> stack = new Stack<IStaticTestSuite>();
// 3. Add Root Test Suite
stack.Push(b);
// 4. Continue while there are TestSuites to Process
while (stack.Count > 0)
{
// A. Get top Suite
var dir = stack.Pop();
try
{
// B. Add all TestSuite at this directory to the result List.
result.Add(dir);
// C. Add all SubSuite at this TestSuite.
foreach (IStaticTestSuite ss in dir.SubSuites)
{
stack.Push(ss);
}
}
catch
{
// D. Fails to open the test suite
}
}
return result;
}
}
Now we have a collection of all Test suits under the test plan, lets process each test suit to get the test cases.
=> Get all Test Cases in a Test Plan Programmatically using TFS API
The Test suite has the TestCases property, you can recursively go through all test cases in the test suit.
for (int i = 0; i < testPlan.TestSuites.Count; i++)
{
var ts = testPlan.TestSuites[i];
foreach (var tc in ts.TestCases)
{
// tc is a test case object
}
}
=> Get all Test Steps in a Test Case Programmatically using TFS API
Now that we have all the test case we can go through and get the test steps inside the test case.
foreach (var tc in ts.TestCases)
{
foreach (var a in tc.TestCase.Actions)
{
// a is a test step
}
}
=> Get the Test Step results Programmatically using TFS API
The test results for a test case can be queried by using the TestResults property. Outcome is the property that will reveal the test outcome.
var testResults = testProject.TestResults.ByTestId(tc.TestCase.TestSuiteEntry.Id);
foreach (var result in testResults)
{
for (int actionIndex = 0; actionIndex < tc.TestCase.Actions.Count; actionIndex++)
{
var action = tc.TestCase.Actions[actionIndex];
if (!(action is ITestStep))
{
continue;
}
var step = action as ITestStep;
var topIteration = result.Iterations.FirstOrDefault();
if (topIteration == null)
continue;
testStepScores.Add(topIteration.Actions[actionIndex].Outcome.ToString());
}
}
=> Putting it all together
Now that we know what we need to do, let’s put it all together,
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.TestManagement.Client;
using Microsoft.TeamFoundation.VersionControl.Client;
namespace TestResults
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
cbTeamProjects.DataSource = GetTeamProjects();
}
public class TestPlan
{
public int Id { get; set; }
public string Name { get; set; }
public List<IStaticTestSuite> TestSuites { get; set; }
public override string ToString()
{
return String.Format("{0}, {1}, {2}", Id, Name, TestSuites.Count);
}
}
static class TestSuiteHelper
{
public static List<IStaticTestSuite> GetTestSuiteRecursive(IStaticTestSuite b)
{
// 1. Store results in the IStaticTestSuit list.
List<IStaticTestSuite> result = new List<IStaticTestSuite>();
// 2. Store a stack of our TestSuite.
Stack<IStaticTestSuite> stack = new Stack<IStaticTestSuite>();
// 3. Add Root Test Suite
stack.Push(b);
// 4. Continue while there are TestSuites to Process
while (stack.Count > 0)
{
// A. Get top Suite
var dir = stack.Pop();
try
{
// B. Add all TestSuite at this directory to the result List.
result.Add(dir);
// C. Add all SubSuite at this TestSuite.
foreach (IStaticTestSuite ss in dir.SubSuites)
{
stack.Push(ss);
}
}
catch
{
// D. Fails to open the test suite
}
}
return result;
}
}
public List<string> GetTeamProjects()
{
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<VersionControlServer>();
return service.GetAllTeamProjects(true).Select(i => i.Name).ToList();
}
public List<string> GetTestPlans(string teamProject)
{
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<ITestManagementService>();
var testProject = service.GetTeamProject(teamProject);
return testProject.TestPlans.Query("SELECT * FROM TestPlan").Select(i => i.Name).ToList();
}
public class ScoreBoard
{
public string TeamProject { get; set; }
public string TestPlan { get; set; }
public int Id { get; set; }
public string Title { get; set; }
public string State { get; set; }
public int TestCaseCount { get; set; }
public int TestCaseDesign { get; set; }
public int TestCaseReady { get; set; }
public int TestCaseClosed { get; set; }
public int TestStepCount { get; set; }
public int TestStepPassedCount { get; set; }
public int TestStepFailedCount { get; set; }
public int TestStepOutstandingCount { get; set; }
}
public List<ScoreBoard> GetTestStatistics(string teamProject, string selectedTestPlan)
{
// 1. Connect to TFS and Get Test Managemenet Service and Get all Test Plans
var tfs = TfsTeamProjectCollectionFactory
.GetTeamProjectCollection(new Uri(ConfigurationManager.AppSettings["TfsUri"]));
var service = tfs.GetService<ITestManagementService>();
var testProject = service.GetTeamProject(teamProject);
var plan = testProject.TestPlans.Query("SELECT * FROM TestPlan").Where(tp => tp.Name == selectedTestPlan).FirstOrDefault();
List<ScoreBoard> scoreboards = new List<ScoreBoard>();
var testSuits = new List<IStaticTestSuite>();
if (plan.RootSuite != null)
{
testSuits.AddRange(TestSuiteHelper.GetTestSuiteRecursive(plan.RootSuite));
}
TestPlan testPlan = new TestPlan() { Id = plan.Id, Name = plan.Name, TestSuites = testSuits };
for (int i = 0; i < testPlan.TestSuites.Count; i++)
{
var ts = testPlan.TestSuites[i];
var scoreboard = new ScoreBoard();
scoreboard.TeamProject = teamProject;
scoreboard.TestPlan = selectedTestPlan;
scoreboard.Id = ts.Id;
scoreboard.Title = ts.Title;
scoreboard.State = ts.State.ToString();
scoreboard.TestCaseCount = ts.TestCases.Count;
scoreboard.TestCaseDesign = ts.TestCases.Where(tcs => tcs.TestCase.State == "Design").ToList().Count; ;
scoreboard.TestCaseReady = ts.TestCases.Where(tcs => tcs.TestCase.State == "Ready").ToList().Count;
scoreboard.TestCaseClosed = ts.TestCases.Where(tcs => tcs.TestCase.State == "Closed").ToList().Count;
int totalTestSteps = 0;
List<string> testStepScores = new List<string>();
foreach (var tc in ts.TestCases)
{
foreach (var a in tc.TestCase.Actions)
{
totalTestSteps = totalTestSteps + 1;
}
var testResults = testProject.TestResults.ByTestId(tc.TestCase.TestSuiteEntry.Id);
foreach (var result in testResults)
{
for (int actionIndex = 0; actionIndex < tc.TestCase.Actions.Count; actionIndex++)
{
var action = tc.TestCase.Actions[actionIndex];
if (!(action is ITestStep))
{
continue;
}
var step = action as ITestStep;
var topIteration = result.Iterations.FirstOrDefault();
if (topIteration == null)
continue;
var test = topIteration.Actions[actionIndex];
testStepScores.Add(topIteration.Actions[actionIndex].Outcome.ToString());
}
}
}
scoreboard.TestStepCount = totalTestSteps;
scoreboard.TestStepPassedCount = testStepScores.Where(tsp => tsp == "Passed").ToList().Count;
scoreboard.TestStepFailedCount = testStepScores.Where(tsp => tsp == "Failed").ToList().Count;
scoreboard.TestStepOutstandingCount = testStepScores.Where(tsp => tsp == "None").ToList().Count;
scoreboards.Add(scoreboard);
}
return scoreboards;
}
public void button1_Click(object sender, EventArgs e)
{
IStaticTestSuiteBindingSource.DataSource =
GetTestStatistics(cbTeamProjects.SelectedValue.ToString(), cbTestPlans.SelectedValue.ToString());
this.reportViewer1.RefreshReport();
}
private void cbTeamProjects_SelectedIndexChanged(object sender, EventArgs e)
{
var teamProject = ((ComboBox)sender).SelectedItem.ToString();
if (!string.IsNullOrEmpty(teamProject))
{
cbTestPlans.DataSource = GetTestPlans(teamProject);
}
}
}
}
=> Conclusion
Microsoft has done a wonderful job making the TFS API available to us, all interactions with the TFS operational database should be done through the TFS API. What test report views do you use for your testing stand ups? If you have any recommendations on things that we could add to this report or any questions or feedback, feel free to add to this blog post. If you enjoyed the post, remember to subscribe to http://feeds.feedburner.com/TarunArora.
Cheers, Tarun