Creating Reports in Microsoft Excel and Visual Studio 2010 (Team Foundation Server 2010)
Posted by: Gouri Sohoni
in Category VSTS & TFS
Abstract: In this article we will focus on how a new report can be created with Excel and also how an existing report can be modified using Excel and Visual Studio 2010.
There are a lot of out-of-box reports available with Visual Studio 2010. These reports vary depending upon the Process Template selected to create Team Project (MSF for Agile Version 5.0 or MSF for CMMI version 5.0). With Visual Studio 2010, dashboards reports are also added. There are many reports which are in Microsoft Excel. We can differentiate all these reports under 3 categories, Report Manager Reports, Excel Reports and Dashboards.
In this article, we will focus on how a new report can be created with Excel and also how an existing report can be modified using Excel.
Let us see how to create a report using WorkItem Query and view in Excel
We can select flat list query to generate report.
1. Select ‘Add Query’ right clicking on "WorkItems" from Team Explorer and select Type of Query as Flat List (the default choice) as follows.
You will observe that the query is referring to the current project. Run the query for all WorkItems for which the state is Active. Select the option of Create Report in Microsoft Excel
If a query is already created and saved in Team Foundation Server, open a new workbook in from Microsoft Excel and select New Report tab. Connect to Team Foundation Server and then select query.
2. You can generate 2 kinds of reports. One for current and the other for trend reports (6 reports in all).
3. Click on Finish to view all these reports in separate worksheets. The first worksheet shows the table of contents as follows
4. Now we can view all the reports by just selecting the one. The last link for ‘How to: Edit reports in Microsoft Excel’ requires internet connection or connectivity to the proxy server.
Some of the reports are as follows:
The last report showing trend for work items assigned.
5. The ‘Column Options’ provide a way to customize the columns to be added to report.
6. This new report can be saved along with other existing reports which are then reflected on the project portal with Excel reports.
We can do the modification to the existing report by opening it in Microsoft Excel and doing changes. Follow these steps:
a. Connect to the Team Project by using Team Explorer. Double click the report to be changed from Documents – Excel Reports.
The report will be opened after giving a warning for opening the file.
b. The default report is shown for last 4 weeks. I removed the Row label and it shows as follows
It shows total number of lines added, modified and deleted for all the projects in DefaultCollection.
c. We can change the parameter selection to suit our requirement. In this case it is selected for a particular team project and only for one for one of the area hierarchy from it
which will reflect on the number of lines as follows
d. Further customization is done to add Month as Row Label and it looks as follows
e. The modified report can be replaced with the existing report or a new report can be saved keeping the original intact.
Note: Microsoft Office with Excel should be installed on the box which has Team Explorer installed and is in the same domain as Team Foundation Server.
In next article we will discuss how to add a report to Project Dashboard as a web part.