Custom Burnup chart with TFS and Excel
There are many things that I like in working with tools of Visual Studio ALM system, but whenever I’d like to have decent chart of where my project really is going it feels like being lots of manual work.
What I have most used to see, are the burndown charts and I really like to add more details such as some trendlines to give me positive and negative estimates for project schedule.
When I was once again starting to go through manual work of copy&pasting values in Excel to get my burndown work nicely, I decided that I’ll jump into adventure of figuring out something new and try to make thing simpler for me to update.
I pondered for a while of which kind of chart I could easily make of existing data that can be imported into Excel from TFS, and somehow burnup chart seemed like natural. So, it was time to say goodbye for my long lasting love with burndowns and welcome newly found affair with burnups!
I’ll describe step by step, how I did my burnup and summarize my impressions with the method I used.
Export report to Excel
First thing to do was of course to get some data to Excel. Luckily it is very easy, at least through Visual Studio Team Explorer. I clicked right button on query of work items that included all the user stories in the project I included. From context menu I pick “Create Report in Microsoft Excel”
.. and get Excel opened.
I have option to select from few predefined report types, of which I also include Trend reports, as it is a Trend report that I am really looking for.
Picking right values in Excel
There are few default charts now available for me, but one that is most close to what I’m willing to have is the one titled “Work Item Count by State”, which gives me a following chart.
There are few things that are not as I’d like them to be though. I’d prefer of having total story points instead of number of user stories, and in addition the chart does not include timeline of whole project but only part of it.
Luckily, the report is imported as a PivotTable, which makes changing it quite easy.
Taking quick look at the structure, to get values to be story point totals all I have to do now is to change Values to be Estimated Effort instead of work item count.
Now the values seem to be good for me, so next step to take is to change timeline for my chart.
I can do this by picking filtering option on the “Trending Date” column.
Here we can see that “Custom Filter” has been defined, so we remove it and also set our date filter to be after starting date of the project. This is done because some of the user stories had been created before the project and having those included very early on would give us distorted burnup.
So, click-click and it is done.
And here we have it. What I’d want to have next, is to include trendlines for done work and total work. That would be a good way to get estimated finish date.
However, including trendlines seems to be impossible task to do, because Excel does not allow trendlines for stacked graphs and we cannot add custom value fields to our PivoTtable when data is from TFS datacube. Blasted!
This means that only way to work forward with Excel would be to separate values from data by copying and creating table for that.
So we copy PivotTable values to new sheet with “paste special->values” and make a new chart with few modifications to include both stackable selection and trendlines (I don’t go into details of how to add those there, but in short it is done by having duplicated series for some of the values and placing also non-stacking line diagrams to be used as basis for trendlines).
And that’s it! It is our Excel based burn-up report from TFS.
Now, whenever we want latest graph of status, we can open this Excel and copy paste values from PivotTable to next sheet and get up to date status with burn-up that describes when our project is estimated to be finished if scope stays as it is and if when we are there if the scope growth stays as it has been.
It is pretty easy and straight forward to get data from TFS to Excel and very basic burn-up chart set up. However, when you want more details such as trendlines, or maybe baselines, the things get more complicated and you need to give up of some of the automation.
To have the chart on this level allows me to update the current situation with only one copy-paste, which is something I can tolerate.
Excel is not of course the only option for reporting with TFS, and maybe I will next time take look at MS Report Builder 2.0, which should provide more in-depth possibilities on handling TFS data than Excel. Maybe I find even fully automated way to do all the magic!