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