Choose the source of data and authoring tool

When you create reports that show data from Visual Studio Application Lifecycle Management (ALM), you specify one of three sources of data and use one of three authoring tools. The choice of data source and authoring tool are interrelated.

The simplest reports that you can generate are based on work item lists. You can create reports about work items by exporting a work item query to Microsoft Excel. Work item lists are best suited to tables and charts that handle no more than several hundred work items.

You can create current status and historical trend data by using the Online analytical processing (OLAP) data cube (TFS_Analysis), which is optimized for reporting. The OLAP data cube is best suited to reports that provide aggregated information, such as the number of work items that meet a set of criteria. If you want to create reports that show trends over time, such as burn-down or progress charts, you can most easily create them from the OLAP data cube.

You can use the relational warehouse database (TFS_Warehouse) to create reports that provide line-item details. These include reports that contain titles of work items and more complex reports that do not include trends or historical data.

Data sources and authoring tools

As the following table shows, your choice of data source depends not only on the kind of data that you want to show but also on the tool that you use to create reports. If you use Excel, you cannot use the warehouse database effectively. If you use Report Builder or Report Designer, you cannot use lists of work items.

For more information about how you can create reports that access the three sources of data, see the related topics in the following table.

Creating Reports That are Based on the OLAP Data Cube

The analysis services database is a multidimensional database that aggregates the data from the warehouse database for more efficient analysis. This data source works especially well with Microsoft Excel.

The analysis services database organizes data in a cube structure. The cube contains measures that are aggregated against many dimensions. This structure provides aggregate values, such as the hours of work for a set of work items. The values are selected directly from the cube instead of calculated in the query.

You can easily build PivotTable and PivotChart reports in Excel by using the analysis services database. For more information, see Create Excel reports from a work item query.

For more information about this source of data, see Perspectives and measure groups provided in the Analysis Services cube for Visual Studio.

Creating Reports Based on Warehouse Data

The warehouse database is a relational database that organizes data in a set of related tables and provides views and table-valued functions for accessing that data. Data from the team project collections is gathered and maintained in the warehouse database. If you are familiar with writing Transact-SQL queries, you can create reports by using the warehouse database.

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

Additional Resources

For more information, see the following pages on the Microsoft Web site:

• PivotTable reports 101 (Microsoft Office Online)

• Charts (Microsoft Office Online)

• Report Designer (SQL Server Development Center)

• Report Builder 2.0

See Also

Concepts

Query for work items

Create, customize, and manage reports for Visual Studio ALM