What's New for Reporting for Visual Studio ALM

By using the current release of Visual Studio Team Foundation Server, you can monitor team projects and track the quality of software under development by using several default reports and dashboards. In addition, you can quickly create current and historical trend reports from work item queries. In this topic, you can learn more about the new features and changes that are provided in the current release that support the creation and customization of reports.

In this topic

• Out-of-the-box Reports and Dashboards

• Generate Reports Quickly from Work Item Queries

• Change Reporting Attributes of Data Fields

• Generate Reports Across Team Projects

• Changes to the Data Warehouse

• Generate Reports Against the Relational Database

Out-of-the-Box Reports and Dashboards

You can view and track progress by using one of the many out-of-the-box reports that are provided with the Microsoft Solutions Framework (MSF) or Visual Studio process templates. In addition to the reports that you can view through Report Manager that was provided in earlier releases of Team Foundation, the current release provides additional reports and Excel versions of many reports that are based on SQL Server Reporting Services. By using the new reports, you can track the status of stories or requirements, bug trends, issue trends, and test progress and productivity.

For an overview of the reports and dashboards that are provided together with the current release of the MSF process templates or the Visual Studio Scrum 1.0 process template, review one of the topics in the following table:

The following table indicates the comparable report in the current release, which has either been renamed or reworked:

Generate Reports from Work Item Queries

You can generate several reports in Microsoft Excel that show current status and historical data based on the filter criteria that you specify in a flat-list work item query. This is useful to show the distribution of work items according to selected criteria or to view trends for the past several weeks. In addition, it is an effective way for you to quickly generate PivotTable and PivotChart reports that you can customize to support other report views.

When you create an Excel report from a query, you can choose which reports to generate based on the variables that are used to filter the query and the criteria that you select. By using these methods, you can generate the following types of reports:

• Current reports: Pie charts that show the count of work items according to the filter criteria that are specified in the work item query.

• Trend reports: Line charts that show the distribution of work items over the past six weeks according to the filter criteria that are specified in the work item query. After the reports are generated, you can easily change the date range.

Each report includes several worksheets, and each worksheet shows a PivotTable report and a PivotChart report that derives data from the SQL Server Analysis Services cube.

For more information, see Creating Reports in Microsoft Excel by Using Work Item Queries.

Change Reporting Attributes of Data Fields

In the current release, two reporting attributes have been added to the definition of work item fields, and the ability to change the attributes of an existing field has been added. The reporting attributes that have been added are as follows:

• reportingrefname. You can assign a different reference name to a field that is marked as reportable. If no value is specified, the value that is assigned to the refname attribute is used.

  You can use this attribute to either merge or diverge fields that are included in reports. To merge two fields that have distinct reference names and that are defined in different team project collections, you assign the same reportingrefname to both. To diverge two fields that have the same reference name but that are defined in different project collections, you assign a different reportingrefname to each field.

• reportingname. You can assign a different label to a field that is used to display data in reports. If you do not specify a value, the friendly name that is assigned for the name attribute is used. The value that is assigned to the reportingname appears in the cube. The value that is assigned to the reportingrefname does not appear.

You can use the witadmin changefield command to change an attribute assignment to a field. For more information, see Adding and Modifying Work Item Fields to Support Reporting.

Generate Reports Across Team Project Collections

You can now create reports that contain data that is collected from several team projects that are stored in different project collections. All reportable data from all team projects that are defined in all project collections for a deployment of Visual Studio Team Foundation Server is written to a single relational data warehouse. Data from that warehouse is then processed and written to the Analysis Services cube. Collecting data into a single data warehouse supports cross-group reporting. The following illustration shows some of the filters that you can use when you construct reports across team projects and project collections.

Changes to the Data Warehouse

All reportable data from all team projects that are defined in all project collections for a deployment of Team Foundation Server is written to a single relational data warehouse. Data from that warehouse is then processed and written to the Analysis Services cube. Collecting data into a single data warehouse supports reporting across team project collections.

Uniqueness of ID Values

With the introduction of team project collections in the current release, the names of team projects must be unique only in a collection. The IDs of work items are unique only in a collection but not across the deployment of collections. Because the data warehouse contains data from all team project collections, when you construct queries for work items against the warehouse, you must also include the GUID for the team project for that work item.

Changes and Additions to the Schema

In the current release, a number of changes were made to the schema for the Analysis Services cube. These changes were made to make the cube more usable, increase performance, and improve translation. In addition, many changes were introduced to support the expanded infrastructure that is now supported by Team Foundation Server and to support the addition of objects for tracking work items, such as link types, categories, and test cases. Some of the changes made to the schema are as follows:

• The number of dimensions has been reduced from slightly more than 60 to fewer than 25 dimensions.

• Many dimensions in the earlier schema versions are now attributes of the Test Case and Work Item dimensions.

• The Area and Iteration dimensions have been redesigned as attributes of the Test Case and Work Item dimensions as true hierarchies and are 14 levels deep, which resembles the depth that is provided by the Work Item Tracking operational store.

• Many measure groups, measures, dimensions, and attributes have been renamed.

• New dimensions have been added to support the new functionality for work item tracking.

• To support usability, display folders have been added to the Test Case and Work Item dimensions. Fields are grouped under folders based on the reference names that are assigned to each field. Dimension attributes are organized into folders that are based on the reporting reference name that is assigned in the work item type definition.

For a comprehensive description of all changes and additions to the cube, see Changes and Additions to the Schema for the Analysis Services Cube.

Processing of the Data Warehouse

You can manage the data warehouse by using the Warehouse Control Web service. This Web service has been renamed and redesigned in the current release. In addition, the default refresh frequency of the cube is set to two hours, which is a change from the previous release. You can modify this value by using the ChangeSetting service. For more information, see Managing the Data Warehouse and Analysis Services Cube.

Generate Reports Against the Relational Database

Creating reports against the relational database is now officially supported. In general, you will want to use the Analysis Services cube for historical reports, or reports that require segmenting multiple parameters of aggregate data. The cube is the best choice to handle this type of report. However, the relational database lets you create reports that pull loosely related data together in ways that are not possible by using the cube.

Views

A number of public views now help you work with the warehouse. All these public views have names that end with "View," such as WorkItemHistoryView. The following illustration shows the public views against which you can generate reports.

Table Names

In the current version of the relational database, most table names have been changed. In earlier versions, many table names contained spaces in their names that created problems with some reporting tools. The renamed tables let you more easily determine the connection between tables in the data warehouse and their corresponding entities in the cube.

In the new data warehouse, names do not have spaces, and they also have a prefix to indicate their use, as described in the following table.

Prefix	Description
dbo.Dim	The table contains dimension data, which means that the data will appear in the dimensions part of the cube. One column is usually provided for each attribute or hierarchy in the dimension. The following illustration shows the dimension tables for Team Foundation. In the cube, the DimWorkItem table appears in the dimension hierarchy under Work Item.
dbo.Fact	Fact tables contain values of cells that appear as measures in the cube. The following illustration shows the fact tables for Team Foundation. Fact tables have foreign keys to various dimension tables. As an example, the FactWorkItemHistory table contains a column that is called StateChangeCount that appears in the cube under measures, in the Work Item measure folder.

Compensating Records

When you work with historical data in the warehouse, you have to be aware of compensating records. Compensating records are defined in tables and views that include historical information, such as WorkItemHistoryView. You use compensating records to generate an aggregate of data.

Whenever a team member updates a work item, a revision is created, and a pair of records is added to the warehouse. One record undoes the most recent previous record, and the next record adds the modified values for the work item. Each one effectively negates, or compensates, the previous revision of that work item.

Also connected with compensating records are two columns: System_ChangedDate and System_RevisedDate. The first column, System_ChangedDate, indicates when the change to the work item was made. The other date tells you when the work item was next changed. As an example, suppose that you created a task at 10:53 on 5/15/2009, and you modified that work item at 11:23 on the next day. You would then see records similar to those listed in the following table.

The first record, which was revised at 11:23, was the original record. The next record cancels out the first record and is added at the same time as the third record. Finally, the third record is the current record, as indicated by the System_RevisedDate, which is set to the maximum value for a DATETIME column.

Sum-based Query

You can determine the current remaining work as of a specific date as indicated by the following SUM-based query.

SELECT SUM(Microsoft_VSTS_Scheduling_RemainingWork)
  FROM WorkItemHistoryView WHERE System_Id = 108
   AND ProjectNodeGUID = 'A8657108-E085-4DE5-B14C-97DAA378D46E'

Although you can construct this SUM-based query, you may achieve better performance if you use an "As Of" query.

As Of Query

You can construct an "as of" query that returns only the last records for each work item that was modified before a certain date. For example, the following "as of" query returns the remaining work as of the end of 5/16/2009.

SELECT System_Id, Microsoft_VSTS_Scheduling_RemainingWork
  FROM WorkItemHistoryView WHERE System_ChangedDate < '5/16/2009'
   AND System_RevisedDate >= '5/16/2009'
   AND RecordCount > 0
   AND ProjectNodeGUID = 'A8657108-E085-4DE5-B14C-97DAA378D46E'

This is the same result that you would get when you query the cube. This query returns only the last records for each work item that was modified before 5/16/2009. The System_RevisedDate clause retrieves only the last record that was modified before 5/16/2009 because the query finds the one record that was both modified before and revised after this date. If the record was not revised at all, the date recorded is in the year 9999. In addition, this query tests for a positive RecordCount. Compensating records that cancel a previous record always have a RecordCount of -1.

See Also

Concepts

Adding and Modifying Work Item Fields to Support Reporting

Managing the Data Warehouse and Analysis Services Cube

Locating Reports After the Upgrade to Team Foundation Server 2010

Changes and Additions to the Schema for the Analysis Services Cube

Creating, Customizing, and Managing Reports for Visual Studio ALM

Change History