Create status and trend reports from a work item query
TFS 2017 | TFS 2015 | TFS 2013
One of the quickest ways to generate a custom work tracking report is to use Excel and start with a flat list query. You can generate both status and trend charts. Also, once you've built a report, you can manipulate the data further by adding or filtering fields using the PivotTable.
Note
This feature is supported for the following versions and configurations:
- Azure DevOps Server 2020 and earlier versions configured with SQL Server Analysis Services.
- For Azure DevOps Server 2019 and Azure DevOps Server 2020, supports projects that are defined on project collections configured with the On-premises XML process model. If your collection is configured to support the Inheritance process model, you can use Analytics views to filter work items and generate Power BI reports. To learn more, see What are Analytics views? To learn more about process models, see Customize your work tracking experience.
If you want to export work items to Excel, see Bulk add or modify work items with Excel. To get the latest version of the Azure DevOps add-in for Office, install Azure DevOps Office® Integration 2019.
Here's an example of a status report generated from a flat-list query.
Prerequisites
You can generate these reports only when you work with an on-premises Azure DevOps Server that has been configured with reporting services.
Your deployment needs to be integrated with reporting services. If your on-premises 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 Azure DevOps Server.
A version of Excel that is compatible with your version of Azure DevOps, such as Office 2010 or later version. For a complete list of supported versions, see Azure DevOps client compatibility, Microsoft Office integration. If you don't have Excel, install it now.
A version of Visual Studio that supports the Team Explorer plugin, such as Visual Studio or Visual Studio Community. You can install Visual Studio from this download site. Team Explorer is free and requires a Windows OS.
To work from Microsoft Excel and use the Team menu, you'll need to install Azure DevOps Office® Integration 2019.
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.
Note
To view queries in Visual Studio 2019 and later versions, you must choose the Tools option Legacy experience (compatibility mode) as described in Set the Work Items experience in Visual Studio 2019.
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. The option to Create Report in Microsoft Excel only appears if all prerequisites are met.
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.
Note
The option New Report appears even if all prerequisites aren't met. Choosing it may cause Excel to stop responding or display the following error message:
If you don't see the Team menu, you'll need to install you'll need to install the Azure DevOps Office® Integration 2019. See Requirements listed earlier in this article.
Connect to the 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 Azure DevOps Open in Excel Marketplace extension. This extension adds an 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. To learn more about these operators, see Query fields, operators, and macros.
Q: Can I create reports if I'm working in Azure DevOps?
A: You can't create Excel reports; however, you can create query-based charts, generate Power BI reports using an Analytics views, or use the Analytics Service.
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 within Excel to update the data for the PivotTables in your workbook. To learn more, see Refresh PivotTable data.