Create a Report in Microsoft Excel for Visual Studio ALM
You can use Microsoft Excel to build a report that is based on the data in the data warehouse for Team Foundation. You build the report by creating a PivotTable report in the Microsoft Excel workbook and connecting the report to the data warehouse in SQL Server. When you create a PivotTable report, you specify which fields from your data source that you are interested in, how you want the table organized, and what kinds of calculations you want the table to perform. For more information, see the following page on the Microsoft Web site: Create a PivotTable report.
After you have built the PivotTable report, you can rearrange it to view your data from alternative perspectives. This ability to pivot the dimensions of the table (for example, to transpose column headings to row positions) gives the PivotTable report tool its name and its unusual analytical power. For more information about manipulating the columns and rows in a PivotTable report, see the following page on the Microsoft Web site: Analyze data with PivotTable reports.
Required Permissions
To perform these procedures, you must be a member of the TfsWarehouseDataReaders security role in SQL Server Analysis Services. For more information, see Grant Access to the Databases of the Data Warehouse for Visual Studio ALM.
To build a report in Microsoft Excel 2007
Open the workbook where you want to create the PivotTable report, and click the Data tab.
In the Get External Data group, click From Other Sources, and then click From Analysis Services.
The Data Connection Wizard - Connect to Server dialog box opens.
In the Server name box, type the name of the server that is running Analysis Services and the database instance (Server/Instance), and then click Next.
In the Data Connection Wizard - Select Database and Table dialog box, select the Tfs_Analysis database, select the Team System cube, and then click Next.
Note
If your server uses SQL Server Enterprise edition, you will have the option of selecting a perspective such as Work Item History that provides a more focused view of the cube.
In the Data Connection Wizard - Save Data Connection File and Finish dialog box, click Finish.
In Import Data, select PivotTable report, and click OK.
In the PivotTable Field List pane, in the Show fields related to: box, select a measure group such as Current Work Item, and then select a measure such as Current Work item Count.
In the PivotTable Field List pane, drag a field such as Assigned To.Person to the Row Labels box.
In the PivotTable Field List pane, drag a field such as Work Item.State to the Column Labels box.
To filter the report, drag a field such as Area.Area into the Report Filter box, and then use the dropdown that appears on the sheet to select the appropriate values.
Repeat steps 7, 8, and 9 until the worksheet is completed.
Save the workbook.
Note
If you want to save the report in a static form that saves the current data, save the file in .xlsx format. If you want to save the report as a template that will update the data every time it is opened, save the file in .xltx format.
To publish a report
In Team Explorer, click the team project node.
On the Team menu, click Show Project Portal.
On the project portal Home page, click Shared Documents.
In the document library folder, click Upload Document.
In the Name box, click Browse, and locate the report.
In the Choose file dialog box, type the full path of the Excel workbook or browse to the location, and then click Open.
Click Save and Close.
See Also
Concepts
Perspectives and Measure Groups Provided in the Analysis Services Cube for Team System
Creating Reports in Microsoft Excel by Using Work Item Queries