Using Excel to easily create reports from TFS Work Items
I want to highlight an easy way to create project reports from data held in Team Foundation Server 2010 using Excel. There are a series of reports provided in the standard process templates (MSF for Agile and MSF for CMMi) in both SQL Server Reporting Services (SSRS) format and Excel. SRSS reports provide the ultimate power in reporting although they carry the cost of requiring SRSS skills if you want to modify them or create your own. The Excel route provides an equivalent set of reports that are perhaps more easily editable by the majority of people with PivotTable skills. What I want to cover here is a really easy way to create your own reports in Excel starting with Work Item Queries.
Let’s take a very simple imaginary scenario; I have a query that returns an interesting set of data from TFS (alright not very exciting in this case but just as an example this query returns all work items in the project):
I may be happy with results of this query – a list of work items that match the criteria – but I’d like to have an actual report based on this query. There is an extremely easy means of doing this – just right-click on the query in the Team Explorer and select Create Report in Excel:
Note that this option is only enabled for Flat List queries, not for Trees.
Having selected this option the query is analysed to determine the reports that could be created:
After which you can select which of the possible reports to include:
The next step does two useful things:
1. Connects Excel to the TFS reporting cube, in my case saving me from looking up or copying the connection string each time:
2. Creates a number of reports that use the data connection to provide PivotTable access to the reporting cube:
You can now alter the data in the report using the PivotTable Field List and Filters – giving you access to any data available in the cube, and of course you can change the graph format from the default pie chart to whatever form suits you better. You could also go on to publish the Excel chart to a SharePoint dashboard using Excel Data Services.
So – hopefully this highlights a quick and easy way to create custom reports from TFS, requiring minimal reporting skills and Excel.
Cheers,
Giles