Viewing Code Analysis Counts in Excel PivotTables

You can use Microsoft Office Excel to display information related to analysis services that is stored in the Team Foundation Server Data Warehouse. The analysis services database includes information about code analysis warnings and errors detected during builds. This topic shows you how to use Excel 2007 to display code analysis counts in a PivotTable. For information about how to use Excel 2003 to create PivotTables, see the section "To build a report in Microsoft Excel 2003" in How to: Create a Report in Microsoft Excel for Team System.

Required Permissions

To perform these procedures, you must be a member of the Microsoft Analysis Services TfsWarehouseDataReaders security role. For more information, see Securing Access Through Analysis Services.


This article is based on the Visual Studio Code Analysis Team blog entry Reporting Code Analysis Defect Counts.

To connect Excel 2007 to the Team Foundation Server Data Warehouse

  1. Open the workbook where you want to create the PivotTable report.

  2. Click the Data tab.

  3. In the Get External Data section, click From Other Sources and then click From Analysis Services.

  4. In Server name, type the name of the data-tier server that stores the Team Foundation data warehouse. In a single server scenario, this computer is the computer that has Team Foundation Server installed on it.


    If a "connection attempt failed" error message appears, your firewall might not have the correct ports listed for the Team Foundation Server services. To fix this issue, see How to: Verify or Correct Port Assignments.

  5. Choose how you want to log on to the server and then click Next.

  6. From the Select the database that contains the data you want drop-down list, select the name of the data warehouse, such as TfsWarehouse.

  7. In Connect to a specific cube or table, select Team System and then click Next.

  8. On the Save Data Connection and Finish page, provide information for each field and then click Finish.

    The Import Data dialog box appears.

After you have connected to the data warehouse, you can access the data in Excel and define how you want the data displayed.

To include code analysis fields in the PivotTable

  1. In the Import Data dialog box, select PivotTable Report and then click OK.

    PivotTable Tools appears.

  2. In the PivotTable Fields List window, expand Build Project so that the available fields are visible.

  3. Select Static Analysis Warnings to include this field in the PivotTable.

    Alternatively, you can choose to include Static Analysis Errors in the PivotTable.

  4. Expand Build and select the Build field to display one row per build in the PivotTable.

  5. Make additional customizations as needed.

    Figure 1: Example

    PivotTable Example

This data can be combined with other build data and charted according to your needs.

See Also

Other Resources

Team Foundation Server Data Warehouse
Choosing the Source of Data in a Report for Team System
How to: Create a Report in Microsoft Excel for Team System