Creating Reports in Microsoft Excel and Visual Studio 2010 (Team Foundation Server 2010)

Posted by: Gouri Sohoni , on 6/22/2010, in Category VSTS & TFS (Azure DevOps)
Views: 84397
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.
NewQuery2

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

FlatList
OR

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).

NewWorkItem
3.    Click on Finish to view all these reports in separate worksheets. The first worksheet shows the table of contents as follows

NewQuery
 
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:

workItem
WorkItem_1

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.

CodeChurn

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
SSGS
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

SSGS_1 
 
which will reflect on the number of lines as follows
Value
 
d.    Further customization is done to add Month as Row Label and it looks as follows

UserInterface
 
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.

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Gouri is a Trainer and Consultant on Azure DevOps and Azure Development. She has an experience of three decades in software training and consulting. She is a graduate from Pune University and PGDCA from Pune University. Gouri is a Microsoft Most Valuable Professional (MVP)  - Developer Technologies (Azure DevOps), Microsoft Certified Trainer (MCT) and a Microsoft Certified Azure DevOps Engineer Expert. She has conducted over 150 corporate trainings on various Microsoft technologies. She is a speaker with Pune User Group and has conducted sessions on Azure DevOps, SQL Server Business Intelligence and Mobile Application Development. Gouri has written more than 75 articles on Azure DevOps, TFS, SQL Server Business Intelligence and SQL Azure which are published on www.sqlservercurry.com and www.dotnetcurry.com. You can connect with her on LinkedIn.


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Arun on Tuesday, February 8, 2011 12:22 PM
Thanks Gouri. One correction in bullet 1. It should say Select "Add query" option by right clicking on "WorkItems" to get a new Query.
Comment posted by Carol on Friday, February 11, 2011 6:39 AM
Thanks Arun. I have made the changes on the author's behalf.
Comment posted by Conner on Tuesday, April 26, 2011 3:41 PM
Thanks for the article Gouri.  But are we able to export into different file versions of Excel?

-Conner
http://www.oneclickcommissions.com/excel-reports.html
Comment posted by Kishor Mali on Wednesday, November 19, 2014 3:32 AM
Error after selection of query
TF208098 : excel unable to access Analysis Services DataBase TFS_Analysis on Server SQLSERVER