Choosing the Source of Data in a Report for Team System
When you create reports that show data from Visual Studio Team System, you specify one of three sources of data. Two of the sources are databases in the data warehouse: an analysis services database and a relational database. The analysis services database is best suited to reports that provide aggregated information, such as the number of work items that meet a set of criteria. The relational database is best suited to reports that provide line-item details, such as the titles of work items.
The third source of data is a list of work items. These lists are best suited to tables and charts that handle no more than a few hundred work items.
As the following table shows, your choice of data source depends on not only 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 relational database effectively. If you use Report Designer, you cannot use lists of work items.
Analysis Services |
Relational |
Work Item List |
|
Excel |
Yes |
No |
Yes |
Report Designer |
Yes |
Yes |
No |
For more information about reporting tools, see Choosing a Tool to Create Reports for Team System.
If you want to create reports that show trends over time, such as a burn-down chart, you can most easily create them from the analysis services database. The relational database is a poor source for trends, and you cannot use work item lists to show trends.
The Analysis Services Database
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.
Note
Some measures, such as Work Item History.Cumulative Count, are not pre-aggregated. They are calculated when the query is performed.
You can easily build PivotTable and PivotChart reports in Excel using the analysis services database. For more information about this source of data, see OLAP Design.
The Relational Database
The relational database organizes data in a set of related tables. If you are familiar with writing Transact-SQL queries, you can create reports using the relational database.
The relational database might contain detailed data that is not present in the analysis services database, depending on the work items that your project uses. For more information about how work item fields are mapped to the warehouse, see Using Fields for Reporting.
For more information about the relational database, see Relational Schemas.
Lists of Work Items
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 How to: Create a Work Item List.
You can also create PivotTable and PivotChart reports from a list of work items. For more information, see 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.
See Also
Concepts
Choosing a Tool to Create Reports for Team System
Getting Started With Custom Reports for Team System
Other Resources
Working with Work Items in Microsoft Excel and Microsoft Project