Create, customize, and manage reports for Visual Studio ALM

To better track the progress of your team in Visual Studio Application Lifecycle Management (ALM), you can create reports that highlight the data that is most important for your project. By creating your own reports, you can track specific information that the default reports in Team Foundation Server (TFS) do not show. In addition, you can customize how members of your organization run, display, publish, and share project-specific data.

To create simple charts from flat-list queries, go here.

When you create or customize a report, you will want to consider the data source, the report type, the report format, and how you will use and share the report with your team or others outside your immediate organization. Your choices of report type, format, and sharing will influence the data source and authoring tool that you will use. Also, access to the databases might influence your decision. To refreshing Excel reports, you must grant more extensive permissions to the data warehouse than what security policies for your organization might allow.

If you want to use reports for self-service analysis only, you can view or customize one of the default Excel reports, or you can create an Excel report that is based on a work item query. If you must create business reports that users share widely, that must be refreshed regularly, and that must be available on-demand, you might want to customize or create reports in SQL Server Reporting Services.

Create reports from Team Foundation data

As the following illustration shows, you create reports from data in one of three databases. You can also create, customize, and view reports by using Excel, Project, or Reporting Services. Your team project includes built-in reports in Excel and Reporting Services, or you can quickly generate reports by using Microsoft Excel or Microsoft Project.

A single relational data warehouse contains all reportable data from all team projects that are defined in all project collections for a deployment of Team Foundation Server. Data from that warehouse is then processed and written to the OLAP cube. Because data is collected into a single data warehouse, you can report across multiple team project collections.

For information about interdependencies among objects that track work, reports, and team processes, see An end-to-end view of what you can configure and customize in Visual Studio TFS.

For more information about interactions between TFS and SQL Server, see Understanding SQL Server and SQL Server Reporting Services.

Create reports from the relational warehouse database

You can track progress and highlight other important trends by querying for data in the relational warehouse database and creating reports that show status. As the following illustration shows, the data in the warehouse is collected from the operational stores and organized in a set of tables, views, and table-valued functions from which you can design reports.

The warehouse contains data about builds, source code, test results and code coverage, and work items such as tasks and bugs. Both the raw data captured in Visual Studio ALM and relationships between these data sets move into the data warehouse. You can explore relationships between the integrated data sets by directly querying and creating reports from data that is stored in the relational warehouse database.

For more information, see Table reference for the relational warehouse database for Visual Studio ALM.

Create reports from the Analysis Services cube

The Team System cube, as the following illustration shows, provides all metrics that are defined for all measure groups. By using the Analysis Services cube for TFS, you can generate reports of aggregated information about the data that is stored in team project collections. You can easily use this data to create PivotTable and PivotChart reports in Microsoft Excel.

Note If your TFS data warehouse is using SQL Server Enterprise Edition, the list of cubes will include Team System and a set of perspectives. The perspectives provide a focused view of the data so that you do not have to scroll through all of the dimensions and measure groups that are defined for the whole Team System cube. For more information, see the following topics: Perspectives and measure groups Shared dimensions Build perspective Code churn and run coverage perspectives Test perspective Work item perspective Note If you use Git for version control, code churn and code coverage data are not available.

Data 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 and the OLAP cube every two minutes and two hours, respectively.

The reportable attribute assigned to each work item field determines whether data is written to only the relational warehouse database or to both the relational warehouse and the OLAP cube. Reportable fields have their reportable attribute set to detail, dimension, or measure. All reportable data from all team projects that are defined in all project collections for a TFS deployment is written to a single relational data warehouse. Data from that warehouse is then processed and written to the OLAP cube. Collecting data into a single data warehouse supports reporting across team project collections.

The following illustration emphasizes that work item fields, field attributes, and global lists, which are displayed in blue boxes, apply across all team projects within a team project collection. The orange-colored boxes indicate WIT objects that are defined for a team project.

Select the software tool to create or customize a report

You can create TFS reports by using any authoring tool that can connect to the Analysis Services relational database or the OLAP cube. For example, you can use Excel, Report Builder, and Report Designer. By using Excel, you can quickly generate reports that access data for tracking work items or data that is stored in the cube. By using Project, you can create reports that show information such as dependencies, schedules, resource usage, and timelines. By using Report Builder or Report Designer, you can allow users to update reports without granting them read access to the databases.

In addition to these tools, Microsoft provides additional tools and plug-ins that extend your capability to customize and publish reports. For more information, see the following pages on the Microsoft website: Choose the right business intelligence technology to suit your style (white paper) and Microsoft Business Intelligence.

As the following illustration shows, you can create Excel reports that access data from the Analysis Services cube or the data store for tracking work items. These reports are useful for ad-hoc and self-service analysis, sharing information with your team through a portal or dashboard, and integrating with other data sources by using PowerPivot for Excel. For more complex business and operational reports, you can use Report Builder or Report Designer to create or customize reports in Reporting Services.

The following table provides information about the tools, data, advantages, and resources for using the authoring tools that you can use to create and customize TFS reports.

To further compare the features of Report Designer and Report Builder, see the following page on the Microsoft website: Comparing Report Authoring Environments

View, create, and manage Excel reports for self-service analysis

You can analyze the progress and quality of your project by using the default Excel reports for your team project. As an alternative, you can create ad-hoc reports in Excel from a work item query or by connecting to the Analysis Services cube.

For information about the permissions that are required to view, update, create, and manage Excel reports, see Assign permissions to view and manage reports for Visual Studio ALM.

Default Excel reports

You can track your team project’s burnrate, bug backlog, software quality, test progress, and other metrics by viewing a default Excel report. To view this kind of report, the following configurations must be met:

• 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 these reports. For more information, see Configure or add a project portal.

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

From the following table, you can quickly access information about each Excel report that the process templates for Microsoft Solutions Framework (MSF) provides. From the topics to which the table links, you can learn about what data is available, what information the project team must track, and how to interpret, update, and customize each report. For more information, see Excel reports, Excel reports (CMMI), or Create Excel reports from a work item query.

Create current and trend reports from work item queries

You can use the Create a Report in Microsoft Excel tool to quickly generate reports that show current status or historical trends based on a work item query. These reports automatically generate a set of PivotTable and PivotChart reports based on your query data and data in the Analysis Services cube. In addition, you can use this tool to quickly generate PivotTable and PivotChart reports that you can customize to support other report views.

For more information, see Create Excel reports from a work item query.

Create Excel reports based on metrics available from the Analysis Services cube

By using the Analysis Services cube, you can generate reports that aggregate information about the data in team project collections. You can easily use this data to create PivotTable and PivotChart reports in Excel. You can drag cube elements onto PivotTable or PivotChart reports to formulate questions and retrieve answers quickly. The cube is optimized to answer the following kinds of questions:

• How many bugs were active, resolved, and closed on each day of the project?

• How many user stories or requirements were active each month for a given time period?

• What was the cumulative count of test results for all build definitions for a team project?

For more information, see Perspectives and measure groups provided in the Analysis Services cube for Visual Studio.

Share and manage Excel reports

You can share Excel reports in one of the following ways:

• Email report data. No special permissions are required to view the data.

• Email an Excel workbook.

• Upload an Excel report to the project portal.

• Add an Excel report to a dashboard.

• Upload an Excel report to a shared network site, and add the URI of that location to a work item form.

Users must have the required permissions to refresh data and the required Team Foundation add-in to open a report whose data is connected to Team Foundation Server.

View, create, and manage reports in Reporting Services

By using Reporting Services, you can exert more control over the format of reports, and both team members and other members within your organization can access them more easily. You can analyze the progress and quality of your project by using the default reports for your team project. These reports aggregate metrics from work items, version control, test results, and builds, helping to answer the following kinds of questions about the actual state of your project:

• Is the team likely to finish the iteration on time?

• Will the team complete the required work, based on the current burn rate?

• Is the team resolving and closing reactivated bugs at an acceptable rate?

• How often are tests passing, and how much of the code is being tested?

• What is the status of all builds over time?

Reports in Reporting Services are report definition (RDL) files, which are files in XML format that contain information about the data sources, the data, and the layout. A report definition does not contain data. When the report is processed, data and layout are combined and passed to a report renderer that determines which data and layout elements fit on each page.

For you to access reports in Reporting Services, the team project collection that contains your team project must be provisioned with Reporting Services. These reports are not available if  Reports does not appear when you open Team Explorer and expand your team project node. The administrator for Team Foundation Server can add a report server to your deployment after your team project has been created. For more information, see Add a report server.

For information about the permissions that are required to view, update, create, and manage reports in Reporting Services, see Grant permissions to view or create reports in TFS.

Default reports in Reporting Services

From the following table, you can quickly access information about each report that is created automatically in Reporting Services when a team project is created from a process template for Microsoft Solutions Framework (MSF) or Visual Studio Scrum. By reading these topics, you can learn about what data each report offers, what information the project team must track, and how to update, filter, and interpret each report. Filters include time period, iteration and area paths, work item types, and work item states. For more information, see Reports (SQL Server Reporting Services).

Create and customize reports in Reporting Services

To create more complex reports that you will host through Reporting Services, you can use either Report Builder or Report Designer. You can use these authoring tools to access both the relational data warehouse and the Analysis Services cube. If possible, you should use Report Builder because it is simpler. You should create a report in Reporting Services when you want to perform one or more of the following tasks:

• Allow users to update the report without granting them read access to the databases.

• Share your reports in Team Explorer under the Reports folder.

• Support subscriptions to reports that can be sent daily over email.

• Manage the properties of your reports so that they return results faster and use fewer server resources.

• Use Transact-SQL queries to retrieve the data for your reports.

For more information, see one of the following topics:

• Create a Detailed Report using Report Designer

• Table reference for the relational warehouse database for Visual Studio ALM

• Perspectives and measure groups provided in the Analysis Services cube for Visual Studio

Manage Reporting Services reports

You can access reports in Reporting Services through Team Explorer, Team Web Access, or the team report site. You can use either Team Explorer or Report Manager to manage reports and report folders. To access the team report site from Team Web Access, on the Home page, choose  Reports.

As more reports are listed under the Reports node, you might want to create subfolders to group or organize the reports. Also, you can use the My Favorites folder to store shortcuts to the reports that you use most frequently.

For more information, see View, upload, and organize reports (Reporting Services Reports).

Add data to support reporting requirements

You can add data to support reporting requirements in the following ways:

• Add work item fields to the data store, relational database, or data warehouse.

• Create an adapter to add data to the data warehouse.

• Incorporate data from other resources to an Excel report using PowerPivot.

Add work item fields

You use work item fields to track data for a work item type, to define the criteria for queries, and to design reports. To support reporting, you can add fields or change the attributes of existing fields. Any field, except a system field, that you want to appear in a report must be defined in the definition file for the types of work items that the field will track. System fields are automatically defined for every type of work item. However, they must be included in the work item form to support data entry. When you add or change fields, you should apply systematic naming conventions to make sure that data is logically grouped into folders in the Analysis Services cube.

For more information, see Add or modify work item fields to support reporting.

Create a warehouse adapter

If you want to add data types to the warehouse, you will probably need to create an adapter, which is a managed assembly that implements IWarehouseAdapter. An adapter uses the warehouse object model to interact with the warehouse for Team Foundation Server. When an adapter adds data fields to the warehouse, it programmatically extends the schema that defines data that is moved to the warehouse. For more information, see Data Warehouse Extensibility and How to: Create an Adapter

PowerPivot

PowerPivot for Excel 2010 is a data analysis add-in for Microsoft Excel 2010. By using this add-in, you can generate reports that combine data from other data stores or databases with data from Team Foundation Server. For more information, see Microsoft PowerPivot.

Resolve schema conflicts and manage the data warehouse

Schema conflicts occur when a set of attributes for reportable fields differs across team project collections. Schema conflicts might block processing of the data warehouse and the Analysis Services cube. You must correct conflicts to unblock processing of the warehouse and for reports to work correctly. For more information, see Resolve schema conflicts that are occurring in the data warehouse.

You can manage the data warehouse and the Analysis Services to address the following scenarios:

• Resolve a problem with reports that are not up to date or that contain missing data.

• Process the warehouse or cube after you resolve schema conflicts that occurred in the warehouse.

• Change the refresh frequency for processing the warehouse or cube. By default, the data is extracted and written to the relational database every two minutes. The cube is updated every two hours. If you make frequent changes and want to view reports that reflect them, you might want to increase the refresh frequency.

• Troubleshoot errors that appear in the event viewer for an application-tier server and that relate to warehouse processing jobs.

• Rebuild the warehouse and the cube after you move, restore, rename, or fail over the data-tier server for Team Foundation. 

You use the Warehouse Control Web Service to perform several of these tasks. For more information, see Managing the Data Warehouse and Analysis Services Cube.

Additional resources

You can get more information from the following resources on the Microsoft website:

• Excel Services Overview
  Provides information about how to load, calculate, and display Excel workbooks in SharePoint Server 2010.

• Creating Reports for Team Foundation Server 2010
  Describes how to create reports that you can view by using Report Manager.

• Customizing Reports for Team Foundation Server 2010
  Provides information about how to customize the default reports for Reporting Services that are provided with each process template. These reports use queries that are written in either SQL or Multidimensional Expressions (MDX).

• Team Foundation Server Reporting & Warehouse (forum)
  Describes how to create custom reports by using the data warehouse for Team Foundation.

• SQL Server Reporting Services (forum)
  Describes how to use Report Designer and other components of Reporting Services.

• Getting Started with Report Builder 3.0
  Describes Report Builder 3.0, which you can use to author reports in an environment that resembles Microsoft Office. This tool offers features such as enhanced data layout, data visualization, richly formatted text, and on-demand rendering.

See Also

Concepts

Components of the TFS data warehouse

An end-to-end view of what you can configure and customize in Visual Studio TFS

Track work with Visual Studio ALM and TFS