Compartilhar via

Create a Remaining vs Completed Work report using TFS, SQL Analysis Services, and Excel

  • Artigo

If you use the MSF Agile process template, you can leverage the Remaining Work and Completed Work fields defined in the Task work items.

 

I will show you how to create a report in Excel that shows Remaining Work and Completed Work values changing over time. The hope is that your project shows Remaining Work trending toward zero, preferably prior to your ship date. J

 

The following instructions are based on Excel 2007 but the prior version of Excel are not too different.

 

  1. Open a new Excel workbook.
  2. Connect to the TFS Warehouse data cube:
    1. Select the “Data” menu and click the “From Other Sources” button on the ribbon, and select “From Analysis Services”
    2. Enter the server name of the SQL Server used for the TFS data tier. If you are using a one server configuration (ATDT) for TFS, this would also be the name of your TFS server. Click Next
    3. Select the “Work Item History” perspective if available, or you can select the “Team System” cube. The various perspectives are simply subsets of the measures and dimensions grouped based on the type of analysis you are interested in. “Team System” is the superset. Since we are interested in analyzing how our task work items change over time, the “Work Item History” related measures and dimensions are appropriate. Click Finish
  3. On the “Import Data” dialog, select “PivotChart and PivotTable Report”. Click OK
  4. Select measures and dimensions from the data cube:
    1. In the PivotTable Field List pane, arrange the fields into the Report Filter/Series/Categories/Values sections.
      1. Drag the “Cumulative Completed Work” measure from the Work Item History dimension into the Values section.
      2. Drag the “Cumulative Remaining Work” measure from the Work Item History dimension into the Values section.
      3. Drag the “Date.Date” member from the Date dimension’s More fields into the Row Labels section
      4. Drag the “Team Project.Team Project” member from the Team Project dimension into the Report Filter section.
      5. Drag the “Area.Area” member from the Area Dimension to the Report Filter section.
      6. Drag the “Iteration.Iteration” member from the Iteration dimension to the Report Filter section.
    2. Let’s take a moment and reflect on what we have so far… The SQL Analysis Services has captured information from our TFS work items such as completed and remaining work, and aggregated these data points across various dimensions such as date, project, area, and iteration. All this aggregated data is provided for us in the form of a data cube. We have simply selected several measures and members from the cube to show in our pivot table and chart.
  5. Change chart to stacked area chart.
    1. Right click on the chart and select “Change Chart Type…”
    2. Select “Area” in the left. And select the Stacked Area chart. 
  6.  Filter the data
    1. You may want to filter the data by dates, project, area and/or iteration. We already put Team Project, Area, and Iteration into the Report Filter section so they should show up above the pivot table. You can also add Date.Date into the Report Filter section or you can filter the dates by clicking the down-arrow to the right of “Row Labels” header on the pivot table.

 

 

Next I'll go over how to do this same thing using SQL Reporting Services and Report Designer.

 

-Francis

Comments

  • Anonymous
    October 18, 2007
    Mike Azocar on Cool Continuous Integration Tool. Sudheer Kumar on TFS Alerts. Dave McKinstry on Itanium...

  • Anonymous
    April 20, 2009
    This is great - where are the steps to do the same thing in SSRS?