Reporting in Team Foundation Server – Part 7: Excel Reports from Work Item Queries
In my last post in this series, I covered the new SQL RS reports we have in TFS 2010. Let’s look at a cool new feature for generating a report in Microsoft Excel based on a work item query.
- Walkthroughs for out-of-the-box experience including customizations
- SharePoint Dashboard
- Rich SQL Reporting Services Reports
- Excel Reports from Work Item Queries
- Custom report authoring tools and walkthroughs
- An overview of the reporting architecture
I know how to get this list of results using a work item query:
But, I want a chart like these without having to ask my developers:
Let’s look at how to get this done in TFS 2010:
Perfect – a cool report generated with just a few mouse clicks! Now, let’s backup and see how this works.
First, I ran a query to get Active Tasks in my project showing Assigned To, Remaining Work, Completed Work etc. in my query results:
In the new menu bar that’s on the query results window, I have a new option to create a report in Excel:
The work item query is translated to figure out the reports that can be generated and I get report options based on columns included in my work item query. I have the option to generate Current as well as Trend reports.
Let’s pick current and trend reports for remaining work by assignment:
The selected reports are generated:
and Trend report:
Now, that was super easy! The first worksheet generated includes a useful table of contents with links to reports:
The reports that are generated are not static and can be modified so it is very powerful. Let’s say I want to make some simple adjustments to the current report so it’s a bar chart and also tweak some colors. I can just as easily tweak the Team Project filter to make it a cross project report or drag additional fields like Completed Work to show more information.
Similar to what we saw in my previous posts, I can easily publish this report to my team dashboard to share this with my team. I can use the Excel Services Options button to publish the entire workbook or specific charts. I’ll publish the customized report to the Excel Reports document library like the out-of-the-box Excel reports shown on the MOSS dashboard.
Then, I all have to do is use standard SharePoint functionality to edit my team dashboard, add an Excel Web Access web part to display the report from my newly published workbook:
So, it was easy to go from here…
In the next topic I’ll cover custom reporting including walkthroughs for tools of choice. If you have questions or feedback please leave me a comment or send me an email at sunder.raman at microsoft.com