TFS 2010: How to Create Reports for Test Results

  • Article

TFS 2010 provides an excellent mechanism to build, deploy and test in an automated fashion. Default reports are provided and can be seen in the reports section of Team Explorer. However, the default reports will not necessarily serve all your reporting needs.

You can develop custom reports using the TFS_Warehouse database. However, it’s recommended that you don’t refer to the database directly since then the reports could not be viewed by everyone. Instead, refer to the shared connection TFS2010ReportDS which can be found under http://<server_name>/ReportServer.

Steps to create custom reports

Installing Report Builder

  1. Download and install the Report Builder from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en.

Creating a Data Source

  1. Open the Report Builder to create a new report
  2. In the left pane called “Report Data”, click on New->Data Source
  3. Enter a name for the Data Source
  4. Select the option “Use a shared connection or report model”
  5. Browse to http://<server_name>/ReportServer.
  6. Select TFS2010ReportDS
  7. Click OK.

Adding a Dataset

  1. Right-click on the data source and select “Add Dataset…”
  2. Enter a name for the Dataset
  3. Enter a query

Designing Report Page

  1. Click on Inset tab
  2. You can then add labels, tables, charts, etc. which refer the Dataset

A working example for reporting TFS 2010 Lab Management test results

  1. Create a Data Source named “SummaryDS” as Described above

  2. Add a Dataset named “CIRun” with Query as Select TOP 50  ``DimBuild.BuildName as CIRunTitle FROM  ``DimBuild WHERE DimBuild.BuildName LIKE '%<``Build Definition name which used LabDefaultTemplate>%' ORDER BY DimBuild.BuildStartTime DESC

  3. Add a Dataset named “SuiteResults” with Query as

    Declare @TestPlanName varchar(100)
  
Declare @TestResultAttributesReadinessState varchar(100)
  
  
Set @TestPlanName = '<Test Plan name>'
  
  
Declare @ResultTable TABLE
  
(
  
 BUILD varchar(100),
  
 SUITE varchar(100),
  
 PASS int,
  
 FAIL int,
  
 ABORTED int,
  
 UNKNOWN int
  
)
  
  
Declare @suite_name varchar(100)
  
  
Declare result_cursor CURSOR for
  
Select SuiteName as SUITE FROM DimTestSuite where SuitePath like '%Immediate Parent Test Suite Path e.g. TestPlan\BVT%'
  
Open result_cursor
  
Fetch next  from result_cursor into @suite_name
  
While @@FETCH_STATUS = 0
  
Begin
  
Insert into  @ResultTable(BUILD, SUITE, PASS, FAIL, ABORTED, UNKNOWN)
  
Select BUILD = (Select Distinct  BuildName from  TestResultView where  TestRunTitle = @CIRun),
  
  
SUITE = @suite_name,
  
  
PASS = (Select count(DISTINCT TestCaseId) from TestResultView where TestRunTitle = @CIRun and TestSuiteSuiteName = @suite_name and TestPlanName = @TestPlanName and ResultOutcome = 'Passed'),
  
  
FAIL = (Select count(DISTINCT TestCaseId) from TestResultView where TestRunTitle = @CIRun and TestSuiteSuiteName = @suite_name and TestPlanName = @TestPlanName and ResultOutcome = 'Failed'),
  
  
ABORTED = (Select count(DISTINCT TestCaseId) from TestResultView where TestRunTitle = @CIRun and TestSuiteSuiteName = @suite_name and TestPlanName = @TestPlanName and ResultOutcome = 'Aborted'),
  
  
UNKNOWN = (Select count(DISTINCT TestCaseId) from TestResultView where TestRunTitle = @CIRun and TestSuiteSuiteName = @suite_name and TestPlanName = @TestPlanName and ResultOutcome = 'Unknown')
  
  
Fetch next  from result_cursor into @suite_name
  
End
  
Close result_cursor
  
  
Select * from @ResultTable

  4. Design the report as 

     

See Also