How to Create Reports for TFS 2010 Test Results

How to Create Reports for TFS 2010 Test Results

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 the database directly since then the reports could not be viewed by everyone. Instead, refer 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 

        

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Can you provide a list of permissions required for the user to perform these tasks? I've given myself publisher and report creator roles but no dice. I'm able to do so if I use the TFSService user, and then, only when logged directly onto the server. What's missing?

  • Can you try giving the follwoing permissions (Browser, Content Manager, My Reports, Publisher, Report Builder, Team Foundation Content Manager). You can narrow down once you are unblocked. You should also have read access to the TFS Warehouse

Page 1 of 1 (2 items)