Flicking through my blog stats on a Friday shows an interesting trend. On Friday my most common search term is generally “connecting to TFS in Excel”. This tells me 2 things – lots of reports are being written on a Friday, and there’s some demand for a starter guide for TFS and Excel integration!
First things first – licensing. The licensing requirements to connect to TFS from Excel and generate reports is fairly simple. You need a CAL. The people you distribute your reports to as either an xls(x) or hard copy won’t require a CAL, with the restriction that they can’t access the server to refresh/update/create data. For more info on licensing you can get the appropriate white papers for 2005 from here, and for 2008 from here.
Right, now we’ve made sure we’re covered by the right license we need a way to connect to TFS.
1. Install Team Explorer
If you’re using 2005, and have the TFS install media you can find it in the TFC directory of the media. Don’t have access to the install media? It’s also available for download from here.
If you’re looking for the 2008 version, you can get it from here.
The downloads both come in the form of an ISO, so you’ll need some kind of ISO reader. WinRAR and 7-zip will both let you extract what you need to do the install. Once you’ve got the files out, it’s a matter of following the bouncing ball through the setup wizard.
2. Open Excel
Now we’ve got a way to connect to TFS, the next step is to open your flavour of Excel. If you’re working with 2003 you should see a toolbar similar to the image below:
Excel 2003 Team Toolbar
If you’re using 2007, you’ll get a ribbon that looks like this:
Excel 2007 Team Ribbon
If you don’t see one of the above, check your install to make sure it completed successfully. You should see an item in your add/remove programs that looks a bit like this
Team Explorer
–Update–
If you’re running Office 2007 you can also check the status of the add-in in Excel. Instructions on the WIT Tools blog here.
3. Get a list of work items
To get the list of work items you’d like to work with you’ll need to click the ‘New List’ button in the toolbar installed with Team Explorer. Depending on your configuration, you might get a credential prompt at this point. If you do, enter the credentials that you usually use to connect to TFS (if you’re unsure, best to contact your TFS admin to find out which set you should be using).
You should then see a dialog asking you to pick a server, and team project to connect to. You’ll need to specify a server if you haven’t already. You can do this by clicking the servers button, then the Add… button.
Connect to TFS Server
Finally enter the URI and the configuration used to connect to your TFS instance. Once again, if you’re unsure – best to contact your TFS admin for these values.
Once you’ve set up a server, pick the team project you’re interested in reporting on and click OK. Double clicking the team project also works.
You’ll now be prompted to pick a query that will form the basis of your work item list. If you like, you can also pick the ‘Input List’ option to enter work items. The queries listed are those specified against the team project you selected. This means if none of those presented suit your needs and you’re not that keen on re-filtering every time you open excel you can go define a new work item query that you can use later to generate lists.
Once you’ve picked a query and clicked OK, Excel will head off to TFS and start building your list. From here on in you’re in Excel land and can generate pivot tables, charts and filtered lists all based off the data you’ve extracted. You can also add work items by entering data into the blank row at the bottom of your list that is denoted with an asterisk.
If you are adding new data – or updating items via the Excel list, you’ll need to hit the publish button to push this data back to TFS before it’s in the system though. An important thing to note here is that if you refresh your data set before publishing any changes you’ve made will be lost. This also applies to any custom formatting you’ve applied to the work item list (though this scenario is addressed in the 2010 integration model).
Well that’s about that. A basic guide to getting a work item list from TFS in Excel that will hopefully simplify the reporting story that most of us go through at some point.
