How to: Create a Report in Microsoft Excel for Team System
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 "Create a PivotTable report" on the Microsoft Web site.
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 "Analyze data with PivotTable reports" on the Microsoft Web site.
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 How to: Grant Access to the Databases of the Data Warehouse for Team System.
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 TFSWarehouse 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 build a report in Microsoft Excel 2003
Open the workbook where you want to create the PivotTable report.
Note
To set up the data warehouse as a data source for Microsoft Excel, you will need the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider installed on the computer where you use Microsoft Excel. The driver is available online from the Microsoft Download Center.
On the Data menu, click PivotTable and PivotChart Report.
In PivotTable and PivotChart Wizard - Step 1 of 3, under Where is the data that you want to analyze?, select External data source.
Under What kind of report do you want to create? select PivotTable, and then click Next.
On the Pivot Table and Pivot Chart Wizard - Step 2 of 3 page, click Get Data.
In the Choose Data Source dialog box, click the OLAP cubes tab, click <New data source>, and then click OK.
In the Create New Data Source dialog box, under What name do you want to give your data source?, type a name that describes your team project or report.
Under Select an OLAP provider for the database you want to access, select Microsoft OLE DB Provider for Analysis Services 9.0.
Click Connect.
In the Multidimensional Connection 9.0 dialog box, select Analysis Server.
In the Server box, type the name of the Team Foundation Server data-tier server and instance (Server/Instance), and then click Next.
Under Database, select TFSWarehouse, and then click Finish.
In the Create New Data Source dialog box, under Select the Cube that contains the data that you want, select Team System, and then click OK.
In the Choose Data Source dialog box, click the name of data source that you typed in step 7, and then click OK.
In PivotTable and PivotChart Wizard - Step 2 of 3, click Next.
In PivotTable and PivotChart Wizard - Step 3 of 3, select Existing Worksheet.
On the existing worksheet, highlight the cells where the PivotTable is to be put.
In PivotTable and PivotChart Wizard - Step 3 of 3, click Finish.
On the Pivot Table Field List, select a measure and drag it to the Drop data items here area of the worksheet.
On the Pivot Table Field List, select a dimension and drag it to either the left or above the measure on the worksheet.
Repeat steps 19 and 20 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 .xls 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 .xlt 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.