Creating Scrum Tools for Project Tracking with Diverse Teams (Part 1)

Feb

AKA Why Are We Talking About TFS, Excel, and Dynamic Named Ranges?

Part of my role as a Technical Project Manager for the Product Development team here at Infragistics is to assist with dissemination of information for all of the products and teams that I work with. In many cases, this involves organizing data from a variety of teams who manage themselves in various ways.

Luckily, all of our teams ascribe to the agile methodology and document their work in Team Foundation Server (TFS). So, what that means for me is that all the data I need is easily within my grasp… if I can figure out how to access it.

That’s where this post comes in. One of the data sets I was first asked to work with was bug metric statistics. This was to be used to create a scrum board for our cross-product release scrums. Prior to my joining the team, the scrum board that was used for the cross-product release scrums was a static document that had to be manually updated each morning before scrum. While the system was functional, I took it as a personal challenge to create a more streamlined method.

Here were the primary requirements:

Cover All Products for Release
Utilize Cube Updated Bug Metrics (Pulled from TFS)
Include a Sparkline for Each Product’s Daily Bug Trend

Clearly, #1 was simple. Just pay attention and include all of the products being released. Numbers 2 and 3, however, were brand new to me. After plenty of versions and countless hours of hard work (with excitement and success peppered in, don’t worry!). Here’s the final product that I came up with:

Nice, Dara. But, How Did You Get There?

After a week or so of exploring queries and other TFS goodies, I discovered the wonder of creating pivot tables directly with TFS data from those aforementioned queries. Since this is only part one of this series, I’m only going to address the first column of the scrum board, the sparkline.

With pivot table skills at my command, I needed to discern a way to take the data from the most recent seven entries of a self-expanding pivot and put them in a sparkline that also dynamically updated.

Wait, what?

Yeah, exactly. That’s a headache of a concept to get around because it’s a pretty severely compounded request, so I understand if you had to read that sentence twice… or even three times.

In an attempt to break this down into digestible chunks, I’ll take it one step at a time:

1. Provide the Most Recent 7 Entries

Of course the first step of this process was the most difficult to learn and therefore to explain, but here goes… In order to create a sparkline, a named range is required. A named range is exactly what it sounds like — it’s a selection of back-to-back cells on a single sheet that are grouped together with a unique name. That name can then be called upon and all of the cells in that range are referenced. Not too bad, right? Weeeeell unfortunately the scrum board I’m creating needs to be updated with new numbers every day, since two week old bug counts wouldn’t be very useful.

This is where the dynamic named range comes in. Again taking the concept of a named range, a dynamic named range takes the same principle but adds in the ability to move along with your data – hence being dynamic!

I accomplished the selection of the “last seven” by using the OFFSET formula that’s built into Excel. OFFSET(reference, rows to move, columns to move, height) is what allowed me to specify the cells I wanted Excel to bring into my range every time the data was refreshed. After much tweaking, the final formula I came to for my scrum board was: =OFFSET($D$8,COUNT($D:$D)-8,0,7)

In this example my reference cell is $D$8, thus designating the starting point in the specific column of data that I’m interested in. Next, my rows to move are determined by using the Count function. Count looks at the number of utilized rows from D8 until the end of the available data. You’ll see that after Count I also have a -8 for the row. This is because the final row in my pivot table is the grand total, which is a replication of the row above. Since I don’t want duplicate data, I have to start from 8 rows from the final number, even though I only will be selecting seven data points for my sparkline. Next up is columns to move, finally something easy! This parameter holds a 0 since we aren’t moving between columns. Finally we reach height, whose parameter holds a 7. This is where our last seven are! Counting down seven rows from rows to move, now you have the last seven, the most recent seven data points of your pivot table!

2. Self-Expanding Pivot Table

This is actually the easiest step, believe it or not. All I had to do to accomplish this was filter for all of the dates in the future that would have data I would be interested in. That’s it. Done.

3. Getting it into a Sparkline

Creating the sparkline is relatively simple once you have the dynamic named range created. On your Ribbon, you select the Insert tab, and then the “Line” option in the Sparkline area.

Next you’ll be given the following prompt:

In the Data Range section, you’ll simply put the NAME that you gave your dynamic named range. By doing this, the sparkline will be able to automatically update every time you refresh the data in your excel sheet. Hence the beauty of the dynamic named range. The Location Range is equally simple – this is just the cell where you want the sparkline to be displayed. This field will be automatically populated with the actively selected cell in your chart, so you can either leave it as-is if appropriate, or change it to the correct cell.

Phew.

So, the finished product after all that work is an adorable little sparkline that looks like this: