This project is read-only.

How to Create a New Report in Excel

- J.D. Meier, Jason Taylor, Alex Mackman, Prashant Bansode

You can create ad-hoc reports by connecting Microsoft Office Excel® directly to the TFS Reporting OLAP cube. By using Excel, you can display report data in the form of pivot tables or pivot charts.

To create an Excel pivot table report
  1. Ensure that you have the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider installed. You can install it from
  2. Start Excel.
  3. Select the worksheet to which you want to add the pivot table report.
  4. On the Data menu, select PivotTable and PivotChart Report.
  5. Select External Data Source.
  6. Click Next.
  7. Click Get Data.
  8. Click the OLAP Cubes tab.
  9. Select <New Data Source> and then click OK.
  10. Enter a name for the data source.
  11. Select the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB provider.
  12. Click Connect.
  13. Select Analysis Server.
  14. Enter the name of your reporting server; for example, TFSRTM.
  15. Click Next.
  16. Select TFSWarehouse and then click Finish.
  17. Select the cube from which you want to build a report (e.g., Code Churn, Work Items, and Test Result) and then click OK.
  18. Click OK again to return to the Pivot Table and Pivot Chart Wizard.
  19. Click Finish to add the pivot table to the worksheet.

Use the PivotTable Field List to drag and drop columns and measures into the pivot table. For example, to see a line count for each Team Project on your server:
  1. Choose the Code Churn cube in step 17 above.
  2. Drag TeamProject.TeamProject into the Column Fields section of the pivot table.
  3. Drag Total Lines into the Data Items section of the pivot table.

Additional Resources

Last edited Jul 25, 2007 at 10:52 AM by prashantbansode, version 1


No comments yet.