Creating, Customizing, and Managing 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 Visual Studio Team Foundation Server do not show. In addition, you can customize how members of your organization run, display, publish, and share project-specific data.

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 may 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 may want to customize or create reports in SQL Server Reporting Services.

In this topic

• Creating Reports from Team Foundation Data

• Selecting the Software Tool to Create or Customize a Report

• Viewing, Creating, and Managing Reports in Excel

• Viewing, Creating, and Managing Reports in Reporting Services

• Adding Data to Support Reporting Requirements

• Resolving Schema Conflicts and Managing the Data Warehouse

• Additional Resources

Creating 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 more information about interactions between Team Foundation Server and SQL Server, see Understanding SQL Server and SQL Server Reporting Services.

Back to top

Creating 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 Generating Reports Using the Relational Warehouse Database for Visual Studio ALM.

Back to top

Creating 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 Visual Studio ALM, 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 data warehouse for Visual Studio ALM 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 Provided in the Analysis Services Cube for Team System Working with Shared Dimensions in the Analysis Services Cube Analyzing Build Details and Build Coverage Using the Build Perspective Analyzing Code Churn and Code Coverage Using the Code Churn and Run Coverage Perspectives Analyzing Test Results Using the Test Perspective in the Analysis Services Database for Visual Studio ALM Analyzing Work Item and Test Case Data Using the Work Item Perspective

For more information about changes in the data architecture since the previous release of Team Foundation Server and how data flows into the data warehouse, see the following topics:

• What's New for Reporting for Visual Studio ALM

• Locating Reports After the Upgrade to Team Foundation Server 2010

• Changes and Additions to the Schema for the Analysis Services Cube

• Components of the Data Warehouse for Team Foundation

Back to top

Selecting the Software Tool to Create or Customize a Report

You can create reports for Visual Studio Application Lifecycle Management (ALM) 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 reports in Team Foundation.

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

Back to top

Viewing, Creating, and Managing 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.

In this section:

• Default Excel Reports

• Creating Current and Trend Reports from Work Item Queries

• Creating Reports Based on Metrics Available from the Analysis Services Cube

• Sharing and Managing Excel Reports

• Additional Resources for Working with Excel Reports

For information about the permissions that are required to view, update, create, and manage Excel reports, see Assigning Permissions to View and Manage Reports for Visual Studio ALM.

Back to top

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 Access a Team Project Portal and Process Guidance.

• To open a report in Microsoft 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 product in Visual Studio ALM.

From the following table, you can quickly access information about each Excel report that the process templates for Microsoft Solutions Framework (MSF) v5.0 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 (Agile), Excel Reports (CMMI), or Edit a Report in Microsoft Excel for Visual Studio ALM.

Back to top

Creating 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 Creating Reports in Microsoft Excel by Using Work Item Queries.

Back to top

Creating Excel Reports Based on Metrics Available from the Analysis Services Cube

By using the Analysis Services cube for Visual Studio Application Lifecycle Management (ALM), 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 Office 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 Team System and Create a Report in Microsoft Excel for Visual Studio ALM.

Back to top

Sharing and Managing 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. For more information, see Upload and Refresh Excel Reports in the Team Project Portal for Visual Studio ALM.

Back to top

Viewing, Creating, and Managing 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 to Your Deployment.

In this section:

• Default Reports in Reporting Services

• Creating and Customizing Reports in Reporting Services

• Managing Reports in Reporting Services

• Additional Resources for Working with Reports in Reporting Services

For information about the permissions that are required to view, update, create, and manage reports in Reporting Services, see Assigning Permissions to View and Manage Reports for Visual Studio ALM.

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) 5.0 or Visual Studio Scrum 1.0. 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 (Agile) or Reports (CMMI).

Back to top

Creating and Customizing 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

• Generating Reports Using the Relational Warehouse Database for Visual Studio ALM

• Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System

Managing 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, click  Reports.

As more reports are listed under the Reports node, you may 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 Manage Reports and View, Organize, and Configure Reports Using Report Manager for Visual Studio ALM.

Back to top

Adding 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.

Back to top

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 Adding and Modifying Work Item Fields to Support Reporting and Defining Work Item Fields.

Back to top

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

Back to top

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.

Back to top

Resolving Schema Conflicts and Managing the Data Warehouse

Schema conflicts occur when a set of attributes for reportable fields differs across team project collections. Schema conflicts may 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 Resolving Schema Conflicts That Are Occurring in the Data Warehouse.

You can manage the data warehouse and the Analysis Services cube for Team Foundation 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 may 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.

Back to top

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 for MSF v5.0. 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.

Back to top

See Also

Concepts

Locating Reports After the Upgrade to Team Foundation Server 2010

Changes and Additions to the Schema for the Analysis Services Cube

Customizing Team Projects and Processes

Planning and Tracking Projects