How to Generate Workspace Metrics Such as Number of Files, Lines of Code, and Number of Projects

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

Create reports on various workspace metrics by connecting 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 http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc
  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 Code Churn cube from which to build a report, 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.

You can use the PivotTable Field List to drag and drop columns and measures into the pivot table.

To view a file count for each team project on your server
  1. Drag TeamProject.TeamProject into the Page Fields section of the pivot table.
  2. Drag File Name.FilePath into the Row Fields section of the pivot table.
  3. Use the Team Project drop-down list in the Page Fields section of the pivot table to filter each team project.
Note the number of rows displayed. This will give you your file count.

To view a line count for each team project on your server
  1. Drag TeamProject.TeamProject into the Column Fields section of the pivot table.
  2. Drag Total Lines into the Data Items section of the pivot table.

To view a team project count for your server
  • Drag TeamProject.TeamProject into the Row Fields section of the pivot table.
Note the number of rows displayed. This will give you your project count.

Additional Resources

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

Comments

No comments yet.