Creating Excel reports from Queries in VSTS

//Creating Excel reports from Queries in VSTS

Recently I needed a quick way to pull up information on a project, specifically hours worked and the PBIs that were “done” in that time frame as well as a list of new, approved and committed PBIs in Excel format. The easiest way to do this is if you already have Visual Studio installed and if you have Office 2016. Once you have Visual Studio 2017 installed and you have upgraded to Office 2016, you can easily create Excel reports from Team Services (VSTS).

In this particular case, the information I needed was already in a shared query but you can easily create new queries by right clicking either “My Queries” or “Shared Queries,” selecting the green plus sign for new query and adding the necessary clauses to filter the information you need.



Once you have the query created, go to a new blank workbook in Excel and select “Team” at the top, then select new list (do not select “new report”).


Then you will need to select a Team Foundation Server. Select “Servers,” then a new window will appear where you can add/remove Team Foundation Servers from a list.


Once you have added the Team Foundation Server and you have signed in with your credentials, the Team Project Collection and Team Projects will appear. From here you you can select “new list” and select the correct team project that you are pulling the query from and then you can select the correct query that you need.

Select “OK” and then your Excel workbook will be populated with the information from the query.

Team Services is not just for Developers or the IT team, it can add value to everyone in your organization. If you don’t know how to optimize the value that VSTS provides, Nebbia Technology can help by providing hands on training for Developers as well as for Agile Practitioners. Contact us today!

By |2017-05-29T17:58:21+00:00May 29th, 2017|

About the Author:

Director of Business Development at Nebbia Technology. Prior to Nebbia Technology, she worked in Information Technology Staffing for over 5 years and held various roles including Technical Recruiter, Executive Recruiter and Technology Division Director for Orlando. She serves on the board of the Orlando .NET User Group and has a Bachelor’s Degree in Marketing and Human Resources from the Florida State University. She can be found on twitter @ChelseaZCoster.