Excel reports

TFS 2017 | TFS 2015 | TFS 2013

Important

Excel reports, Reporting Services reports, and SharePoint dashboards are only supported for on-premises TFS. For information on what is supported for VSTS, see Dashboards, charts, & widgets.

TFS 2018 and later versions no longer support native integration with SharePoint products. If you're planning to upgrade to TFS 2018, read About SharePoint integration to learn about the options available to you.

You can use the Microsoft Excel reports to display information from the data warehouse for your team project. This topic describes reports that are provided with the Agile process template. For a summary of reports provided with the CMMI process template, see Excel reports (CMMI).

If you want to export work items to Excel, see Bulk add or modify work items with Excel. Or, if you want to create an Excel report from a query, see Create Excel reports from a work item query.

Note

These reports require your team project portal to be configured with SharePoint Server Enterprise Edition. Also, you can view these reports in your team project's dashboards.

If the project collection where your team project is stored is provisioned with SQL Server Reporting Services, you can use Report Manager to view and filter the same information that the Excel reports provide. See Reporting Services Reports.

Excel Reports (TFS)

You can track your team project's burn rate, bug backlog, software quality, test progress, and other metrics by viewing a default Excel report.

Here are examples of two of the Excel reports.

Bug trends example report Build status example report

Default Excel reports require that your team project is configured with SharePoint Products and SQL Server Analysis Services. If these reports aren't available to you, you can create ad-hoc reports in Excel from a work item query or by connecting to the Analysis Services cube.

Adhoc status and trend reports Build and code quality
Progress Bug tracking Test tracking

Project management

Team members can use the reports in the following table to help track the rate of the team's progress toward completing Tasks, implementing User Stories, and meeting their iteration plan.

Data in the report Report name and related topic
A visual representation of the cumulative count of all hours for all tasks for the past four weeks.

Burndown chart
Burndown
A visual representation of the cumulative count of all active and closed tasks for the past four weeks.

Task Progress Excel Report
Task Progress
A visual representation of the cumulative count of all user stories, grouped by their state, for the past four weeks.

Example User Stories report
User Story Progress
Line chart that shows the rolling average of the number of issues that the team has opened and closed for the past four weeks. The rolling average is based on the seven days before the date for which it is calculated.

7-Day Issue Trend Excel Report
Issue Trends

Bug backlog management

Team members can use the reports in the following table to help track the bugs that the team finds and the progress that the team makes toward fixing them. These reports appear in the Bugs dashboard. For more information, see Bugs.

Data in the report Report name and related topic
A visual representation of the cumulative count of all bugs, grouped by their state for the past four weeks.

Bug Progress Excel Report
Bug Progress
Line chart that shows the rolling average of the number of bugs that the team has opened, resolved, and closed for the past four weeks. The rolling average is based on the seven days before the date for which it is calculated.

Bug Trends report
Bug Trends
A visual representation of the cumulative count of all bugs, grouped by their priority for the past four weeks.

Bugs by priority chart
Bugs by Priority
A horizontal bar chart with the total count of Bugs that each team member has currently assigned to them in the active state, grouped by priority.

Bugs by Assignment chart
Bugs by Assignment
A stacked area graph of the number of bugs that have been reactivated from the resolved or closed state within the past four weeks.

Bug Reactivations Excel Report
Note: The Bug Reactivations report appears on the Quality dashboard. For more information, see Quality.
Bug Reactivations

Build management

Team members can use the reports in the following table to help track how source files are changing over time and how well the source code is being tested over time. These reports appear in the Build dashboard. For more information, see Build.

Data in the report Report name and related topic
Line chart that depicts the percentage of code that was tested under Build Verification Test over the last four weeks.

Code Coverage Report
Code Coverage
Stacked area chart that depicts the number of lines of code that the team added, removed, and changed in the checkins before the build within the last four weeks.

Code Churn Report
Code Churn
Stacked column that shows the count of builds that Failed or Succeeded within the last four weeks.

Build Status report
Note: The Builds Status report appears on the Quality dashboard. For more information, see Quality.
Build Status

Test management

Team members can use the reports in the following table to help monitor test activities, report on progress, find gaps in test coverage, and identify test areas that may require further investigation. These reports appear in the Test dashboard. For more information, see Test.

Data in the report Report name and related topic
Stacked area graph of the test results grouped into their last recorded outcome - Never Run, Blocked, Failed, or Passed - within the past four weeks.

Test Plan Progress Excel Report
Test Plan Progress
Stacked area graph that shows how many test cases are in the Design or Ready state for the past four weeks.

Test Case Readiness Excel Report
Test Case Readiness
Horizontal bar chart that shows the count of test results for each test case and test configuration combination that is defined for each user story. The chart groups the test results according to their most recent test run, where the options are Passed (green), Failed (red), Blocked (purple), or Not Run (gray).

User Story Test Status Excel Report
User Story Test Status
Line chart that shows the cumulative count of all results run for manual tests during the past four weeks.

Test Activity Excel Report
Test Activity
Stacked area graph that shows the cumulative count of all failed outcome results for tests, according to their failure type of Regression, New Issue, or Known Issue, during the past four weeks.

Failure Analysis Excel Report
Failure Analysis

Software quality and release management

Team members can use the reports in the following table to obtain an overview of progress occurring in the test, development, and build areas. These reports appear in the Quality dashboard. For more information, see Quality.

Data in the report Report name and related topic
Stacked area graph of the test results grouped into their last recorded outcome - Never Run, Blocked, Failed, or Passed - within the past four weeks.

Test Plan Progress Excel Report
Test Plan Progress
Stacked column that shows the count of builds that Failed or Succeeded within the last four weeks.

Build Status report
Build Status
A stacked area graph of the cumulative count of all Bugs, grouped by their state for the past four weeks.

Bug Progress Excel Report
Bug Progress
A stacked area graph of the number of bugs that have been reactivated from the resolved or closed state within the past four weeks.

Bug Reactivations Excel Report
Bug Reactivations
Line chart that depicts the percentage of code that was tested under Build Verification Test over the last four weeks.

Code Coverage Report
Code Coverage
Stacked area chart that depicts the number of lines of code that the team added, removed, and changed in the checkins before the build within the last four weeks.

Code Churn Report
Code Churn

Requirements and permissions

To access and use the Microsoft Excel reports, the following configurations must be met:

  • To access an Excel report, your team project must have been provisioned with a project portal.

    Excel reports are stored on the server that hosts SharePoint Products for your team project. If a project portal has not been enabled for your team project, you cannot access the workbook. For more information, see Configure or add a project portal.

  • To open a report in Excel that connects to the operational data store for Team Foundation, you must have the Team Foundation Office Integration add-in installed on your client computer. This add-in is installed when you install any version of Visual Studio or Team Explorer.

    To view or modify an Excel report that is stored under the Documents node for a team project, you must be assigned or belong to a group that has been assigned Read permissions for Team Foundation. You must also have Visitors or Members permissions, respectively, in SharePoint Products for the team project.

    In addition, all Excel reports that appear in the enterprise dashboards contain data from the Analysis Services cube. You can view enterprise dashboards only if the team project portal is hosted on a server that is running SharePoint Server Enterprise Edition.

    To view, refresh, or create an Excel report from an enterprise dashboard, you must configure the SharePoint web application definition to either use Single Sign-On or Windows Authentication. The following restrictions apply based on the authentication service that you configure.

    You must configure the SharePoint web application for Single Sign-On if you do not want to add users as members of the TfsWarehouseDataReaders security role in SQL Server Analysis Services. In addition, you must configure the application for Single Sign-on if the team project portal is configured to use NTLM authentication and is not installed on the data-tier server.

  • For Single Sign-On Authentication (Recommended)

    To authenticate viewers of enterprise dashboards by using Single Sign-On, you must perform the following actions:

    • You must configure the SharePoint web application to use Single Sign-on. For more information about how to configure Single Sign-on, see Configure authentication infrastructure in SharePoint 2013.

    • You must add dashboard viewers to a group that is granted access to the Single Sign-on enterprise application definition.

  • For Windows Authentication

    To authenticate viewers of enterprise dashboards by using Windows Authentication, you must perform the following actions:

    • You must either host the team project portal on the data-tier server, or you must configure the SharePoint web application to use Kerberos authentication.

    • You must add users as members of the TfsWarehouseDataReaders security role in SQL Server Analysis Services.

    The following table summarizes the required permissions based on activity. For information about how to grant these permissions to team members, see Add users to team projects. For information about permissions that are required to work with Excel reports in general, see Grant permissions to view and manage reports.

Activity Team Foundation Server Team project portal SQL Server Analysis Services cube (Tfs_Analysis) Notes
View or refresh an Excel report that is opened from the Documents node of Team Explorer Readers Visitors TfsWarehouseDataReader role To access the Documents node for a team project, you must be a member of the Team Foundation Valid Users security group. If the required security permissions are set explicitly, your View project-level information permission on the team project must be set to Allow.
View or refresh an Excel report that appears in an enterprise dashboard Visitors Requirements depend on the authentication that is configured for the SharePoint web application In addition to Visitors or Read permissions, you must belong to a group that is granted access to the TfsWarehouseDataReader role or to the Single Sign-on enterprise application definition for the SharePoint web application.
Run a work item query, and use the Create Report in Microsoft Excel feature Readers TfsWarehouseDataReader role In addition to these permissions, you may need permission to open a team query. For more information, see Set permissions on queries.

Also, if you want to save the resulting workbook to the project portal, you belong to the Members group for the portal.
Open a dashboard and use the New Excel Report feature Visitors TfsWarehouseDataReader role The New Excel Report button is available only if reporting is configured for the project collection that hosts the team project.
Create a report from Microsoft Excel that connects directly to the Analysis Services cube TfsWarehouseDataReader role If you want to save the resulting workbook to the project portal, you must belong to the Members group for the portal.
Manage Excel reports from the Documents node Readers Members To save files under the Documents node, you must be a must belong to the Members group for the team project portal. For more information, see Manage documents and document libraries.

Refresh frequency

All data captured for work items is written to the WIT data store, but only select data is written to the Analysis Services data warehouse. The WIT data store is updated in real-time as team members create and modify work items. Incremental updates are then written to the relational warehouse database every two minutes and the OLAP cube every two hours. To change these settings, see Change the Data Warehouse Refresh Frequency.