Export (0) Print
Expand All

How to: Create a Report in Microsoft Excel for Team System

You can use Microsoft Excel to build a report based on the data in the Team Foundation data warehouse. You build the report by creating a PivotTable in the Microsoft Excel workbook and connect the PivotTable to the data warehouse in Microsoft 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 about how to create a PivotTable report, see "Create a PivotTable report" in Microsoft Excel Help (http://office.microsoft.com/en-us/assistance/HP051995561033.aspx) online.

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 tool its name and its unusual analytical power. For more information about manipulating the columns and rows in a PivotTable, see "Analyze data with PivotTable reports" in Microsoft Excel Help (http://office.microsoft.com/en-us/assistance/HA012071641033.aspx).

Contact your Team Foundation Server administrator or your team project administrator to make sure that your user account has the permissions described in the following section.

Required Permissions

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

To build a report in Microsoft Excel 2007

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

  2. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

  3. In Data Connection Wizard - Connect to Server, in the Server name box, type the name of the Team Foundation Server data-tier server, and then click Next.

  4. In Data Connection Wizard - Select Database and Table, select the TFSWarehouse database, select the Team System cube, and then click Next.

    NoteNote

    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.

  5. In Data Connection Wizard - Save Data Connection File and Finish, click Finish.

  6. In Import Data, select PivotTable report, and click OK.

  7. 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.

  8. In the PivotTable Field List pane, drag a field such as Assigned To.Person to the Row Labels box.

  9. In the PivotTable Field List pane, drag a field such as Work Item.State to the Column Labels box.

  10. 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.

  11. Repeat steps 7, 8, and 9 until the worksheet is completed.

  12. Save the workbook.

    NoteNote

    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

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

    NoteNote

    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 through the Microsoft Download Center (http://go.microsoft.com/fwlink/?LinkId=85567).

  2. On the Data menu, click PivotTable and PivotChart Report.

  3. In PivotTable and PivotChart Wizard - Step 1 of 3, under Where is the data that you want to analyze?, select External data source.

  4. Under What kind of report do you want to create? select PivotTable, and then click Next.

  5. On the Pivot Table and Pivot Chart Wizard - Step 2 of 3 page, click Get Data.

  6. In the Choose Data Source dialog box, click the OLAP cubes tab, click <New data source>, and then click OK.

  7. 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.

  8. Under Select an OLAP provider for the database you want to access, select Microsoft OLE DB Provider for Analysis Services 9.0.

  9. Click Connect.

  10. In the Multidimensional Connection 9.0 dialog box, select Analysis Server.

  11. In the Server box, type the name of the Team Foundation Server data-tier server, and then click Next.

  12. Under Database, select TFSWarehouse, and then click Finish.

  13. 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.

  14. In the Choose Data Source dialog box, click the name of data source that you typed in step 7, and then click OK.

  15. In PivotTable and PivotChart Wizard - Step 2 of 3, click Next.

  16. In PivotTable and PivotChart Wizard - Step 3 of 3, select Existing Worksheet.

  17. On the existing worksheet, highlight the cells where the PivotTable is to be put.

  18. In PivotTable and PivotChart Wizard - Step 3 of 3, click Finish.

  19. On the Pivot Table Field List, select a measure and drag it to the Drop data items here area of the worksheet.

  20. On the Pivot Table Field List, select a dimension and drag it to either the left or above the measure on the worksheet.

  21. Repeat steps 19 and 20 until the worksheet is completed.

  22. Save the workbook.

    NoteNote

    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

  1. In Team Explorer, click the team project node.

  2. On the Team menu, click Show Project Portal.

  3. On the project portal Home page, click Documents and Lists.

  4. In the Document Libraries list, click the folder where you want to store the Excel workbook. If you want to create a new folder, click Create, click Document Library, and then follow the instructions on the screen.

  5. In the document library folder, click Upload Document.

  6. In the Name box, either:

    1. Type the name of the report and then click Browse or

    2. Click Browse and locate the report.

  7. In the Choose file dialog box, type the full path of the Excel workbook or browse to the location, and then click Open.

  8. Click Save and Close.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft