Choosing the Source of Data and Authoring Tool for Your Reports for Visual Studio ALM

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.

In this topic

• Data Sources and Authoring Tools

• Generating Reports Based on Lists of Work Items Using Excel

• Creating Reports Based on the OLAP Data Cube

• Generating Reports Based on Warehouse Data

• Additional Resources

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.

Generating Reports Based on Lists of Work Items by Using Excel

You can create a list in Excel that uses a work item query or a static set of work items. In some senses, this list is already a detailed report. For more information, see Create, Open, and Modify Work Items Using Office Excel. You can also use the work item query as the basis for generating a set of reports that use the analysis services database.

You can also create PivotTable and PivotChart reports from a list of work items. For more information, see the following page on the Microsoft Web site: PivotTable reports 101 (Microsoft Office Online).

To refresh the contents of the list, click Refresh on the Team toolbar, or open the Team menu and click Refresh. To refresh PivotTable and PivotChart reports, click Refresh Data on the Data toolbar, or open the Data menu and click Refresh Data.

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

For more information about this source of data, see Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System.

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

Additional Resources

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

• Charts (Microsoft Office Online)

• Report Designer (SQL Server Development Center)

• Report Builder 2.0

See Also

Concepts

Finding Bugs, Tasks, and Other Work Items

Creating, Customizing, and Managing Reports for Visual Studio ALM