How to create a Cumulative Flow Diagram in Excel and TFS 2010

Cumulative Flow Diagrams (CFDs) are valuable tools for tracking and forecasting the performance of your processes. In this post I will show you how you can create a Cumulative Flow Diagram in Excel that fetches the data from Team Foundation Server 2010 (TFS 2010).

Update
As been pointed out on the KanbanDev mailing list by Daniel Vacanti the approach of counting items per process stage is a simplified way to construct CFDs. Daniel points in his book Actionable Agile Metrics for Predictability out that this approach only works when the following two conditions are satisfied:

  1. Work items are not moved backward in your workflow.
  2. Work items are never completely removed before they are completed.

Keep this in mind if you use this approach.

Result

In this example I ‘m using a modified MSF for Agile Software Development v5.0 template. I have added the following states to the User Story work item workflow:

  • Analysis
  • Analysis Done
  • Development
  • Development Done
  • Test
  • Deployed

Let’s get started.

1. Open a new Excel worksheet

2. Open the “Team” tab in the Excel ribbon

GoToTeamTab

3. Create a new report by clicking on the “New Report” button on the Excel ribbon

SelectTeamProject

4. Select your “Team Project” and click the “Connect” button

SelectTeamQuery

5. Select a Work Item Query to generate your report from. In our case what query you select doesn’t really matter but I select the “Product Backlog” query as this closes to what we will be using in the Cumulative Flow Diagram.

SelectWorkItemCountStateTrendReport

6. Deselect all reports

7. Expand the “Trend Reports” and “Work Item Count” tree nodes. Check the “State” report

8. Click the “Finish” button

ReportTableOfContent

9. Select the “1.1 State Trend” sheet.

StateTrendSheet

You will now se the default trend report generated by TFS. The generated report are using weeks for the rows(horizontal axis). I usually use one date for each row. Lets change to using dates for each row.

10. Remove the “Year, Week, Date” from the Axis Fields.

11. Add the “Date” field to the Axis Fields

ChangeAxisFieldToDate

Now the chart will show all the dates that have values in the data warehouse. I ‘m only interested in the last few dates. Lets filter the date rows.

12. Filter the date rows by selecting “Last week” from the “Date Filters”

SelectDateFilter

13. Now deselect the states that you don’t want to be part of the diagram.

SelectStateFilter

Now we come to the most important part to make the Cumulative Flow Diagram work. Change the order of the state columns to be in the reverse order. That means that the last state of the process should be first and vise versa.

14. For the first state column (in our example this is the Active state column), right click and use the move column and move it’s new position.

ChangeStateOrder01

Repeat this for every state column and you should end up with something like this:

ChangeStateOrder02

15. Now make style changes to the diagram and the result may look something like this:

Result

Note: You have to refresh the diagram to show updated data in the data warehouse. The data warehouse updates on a regular basis and the default update interval is 7200 sec.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s