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
Important
Most reports in Excel require that the team project collection that hosts your team project is provisioned with SQL Server Analysis Services. All reports in Reporting Services require the collection to be provisioned with both Analysis Services and Reporting Services. Also, to access default reports in Excel, your team project must have a project portal enabled. If these services are not configured, you can access only current status reports.
You can easily generate current status reports from a work item query that you open in Excel, and you can use Excel tools to chart the data. For more information, see Finding Bugs, Tasks, and Other Work Items.
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.
.png "Team Foundation Data Stores and Reports Overview")
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.
.png "Team Foundation Warehouse")
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.
.gif "Note") 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:
|
.png "Analysis Services Data Cube Measure Groups") |
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:
Locating Reports After the Upgrade to Team Foundation Server 2010
Changes and Additions to the Schema for the Analysis Services Cube
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.
.png "Tools to Generate Team Foundation Reports")
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.
Software tool |
Data accessed |
Useful for |
Notes |
|---|---|---|---|
Excel |
Data store for tracking work items OLAP data cube (TFS_Analysis) |
Self-service analysis Managing small to medium data sets (100s - approximately 1MM records) Flexible, free-form report layout and format |
To update or refresh an Excel report, the user must have the appropriate permissions. One permission must grant access to the data warehouse, which stores data for the whole deployment of project collections. The user must refresh data manually or by using macros. |
Project |
Data store for tracking work items Project schedule and resource data |
Displaying Gantt reports that show dependencies, timeline reports, resource usage, and allocation. For enterprise deployments, Project Server provides a variety of reports that you can use to analyze project and resource performance within a project or across multiple projects. Also, you can use PivotTable and PivotChart reports to work interactively with the reports and change some of the fields that structure the report. |
You can access a report in Project immediately by opening a work item query in Project. For more information, see Scheduling Tasks and Assigning Resources Using Microsoft Project. To access enterprise-level reports in Project, you must install and configure the Visual Studio Team Foundation Server 2010 and Project Server Integration Feature Pack. For more information, see Microsoft Team Foundation Server 2010 and Microsoft Project Server Integration Feature Pack. |
Report Builder |
OLAP data cube (TFS_Analysis) Relational warehouse database (TFS_Warehouse) |
Creating sophisticated, consistently formatted reports. Adding sparklines, bar charts, and indicators to reports. Using parameterized views. |
SQL Server 2008 R2 Report Builder 3.0 provides an intuitive, full-featured reporting environment with which users can develop highly formatted reports by using a ribbon that resembles similar functionality in Excel. You can download this tool and access additional resources from the following page on the Microsoft website: Getting Started with Report Builder 3.0. |
Report Designer |
OLAP data cube (TFS_Analysis) Relational warehouse database (TFS_Warehouse) |
Creating sophisticated, consistently formatted reports. |
Report Designer is a collection of graphical query and design tools that are hosted within the Visual Studio environment. Report Designer provides a Report Data pane, so that you can organize data that is used in your report, and tabbed views for Design and Preview, so that you can design a report interactively. Report Designer also provides query designers, with which you can more easily specify data to retrieve from data sources, and the Expression dialog, with which you can specify report data to use in the report layout. For more information, see the following page on the Microsoft website: Report Designer. |
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.
Report area |
MSF for Agile Software Development v5.0 |
MSF for CMMI Process Improvement v5.0 |
|---|---|---|
Project Management |
||
Bug Management |
||
Test Management |
||
Software Quality and Release Management |
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 .gif "Report"){kind=icon}
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).
Note
If you create or customize reports in Reporting Services, you can save those changes to a process template. For more information, see Adding Reports to the Process Template.
Report area |
MSF for Agile Software Development v5.0 |
MSF for CMMI Process Improvement v5.0 |
Visual Studio Scrum 1.0 |
|---|---|---|---|
Project Management |
|||
Bug Management |
|
||
Test Management |
|
||
Software Quality and Release Management |
|
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:
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