Create Excel reports from a work item query
TFS 2017 | TFS 2015 | TFS 2013
Feature availability: You can use this feature only when you connect to a team project hosted an on-premises Team Foundation Server (TFS) and with SQL Server Analysis Services configured.
If you want to export work items to Excel, see Bulk add or modify work items with Excel.
One of the quickest ways to generate a custom report in Excel is to start with a flat list query. You can generate both status and trend charts. Also, once you've build a report, you can manipulate the data further by adding or filtering fields using the PivotTable.
Here's an example of a status report generated from a flat-list query.
You can generate these reports only when you work with an on-premises TFS that has been configured with reporting services.
Your deployment needs to be integrated with reporting services. If your on-premises TFS application-tier server hasn't been configured to support reporting services, you can add that functionality by following the steps provided here: Add reports to a team project.
You must be a member of the TfsWarehouseDataReader security roles. To get added, see Grant permissions to view or create reports in TFS.
A version of Excel that is compatible with Office 2007, Office 2010, or Office 2013. If you don't have Excel, install it now.
Either Visual Studio or the Team Explorer plug-in for Visual Studio, which you can install from this download site. Team Explorer is free and requires a Windows OS.
You need to install Team Explorer to get the Team Foundation add-in for Excel.
Create an Excel report from a flat-list query
Use this procedure when you work from the Team Explorer plug-in for Visual Studio.
Create or open a flat-list query that contains the work items that you want to include in the report.
Choose the fields you want to base reports on and include them in the filter criteria or as a column option. For non-reportable fields, see Q: Which fields are non-reportable?
Create a report in Excel From the query results view.
Select the check boxes of the reports that you want to generate.
Wait until Excel finishes generating the reports. This step might take several minutes, depending on the number of reports and quantity of data.
Each worksheet displays a report. The first worksheet provides hyperlinks to each report. Pie charts display status reports and area graphs display trend charts.
To view a report, choose a tab, for example, choose the State tab to view the distribution of work items by State.
You can change the chart type and filters. For more information, see Use PivotTables and other business intelligence tools to analyze your data.
Create a query-based report by using Excel
Use this procedure when you work from the web portal or the Team Explorer plug-in for Visual Studio.
Open an Office Excel workbook and choose New Report.
If you don't see the Team menu, you'll need to install Team Explorer to get the Team Foundation add-in to Excel. See Requirements listed earlier in this topic.
Connect to the team project and choose the query.
If the server you need isn't listed, add it now.
Choose the reports to generate (steps 3 and 4 from the previous procedure).
Q & A
Q: Can I export a query to Excel?
A: If you want to export a query to Excel, you can do that from Excel or Visual Studio/Team Explorer. Or, to export a query directly from the web portal Queries page, install the VSTS Open in Excel Marketplace extension. This extension will add in Open in Excel link to the toolbar of the query results page.
Q: Which fields can't I use to generate a report?
A: Even though you can include non-reportable fields in your query field criteria or as a column option, they won't be used to generate a report.
Description, History, and other HTML data-type fields. These fields won't be added to the PivotTable or used to generate a report. Excel does not support generating reports on these fields.
Fields with filter criteria that specify the Contains, Contains Words, Does Not Contain, or Does Not Contain Words operators will not be added to the PivotTable. Excel does not support these operators.
Q: Can I create reports if I'm working in VSTS?
A: You can't create Excel reports; however, you can create query-based charts.
Q: How do I refresh the report to show the most recent data?
A: At any time, you can choose Refresh on the Data tab to update the data for the PivotTables in your workbook. To learn more, see Update (refresh) data in a PivotTable.
Q: Can I upload these reports to a project portal?
A: Yes. See Manage documents and document libraries.