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)
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
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).
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.
This article has been editorially reviewed by Suprotim Agarwal.
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!
Was this article worth reading? Share it with fellow developers too. Thanks!
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.