Create status and trend reports from a work item query

Azure DevOps Server 2020 | Azure DevOps Server 2019 | TFS 2018 - TFS 2013
Azure DevOps Server was previously named Visual Studio Team Foundation Server.

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 only available with an on-premises Azure DevOps Server 2019 and later versions configured with SQL Server Analysis Services. Also, this feature only supports projects that are defined on project collections configured with the On-premises XML process model. If you're 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.

Note

This feature is available with an on-premises Team Foundation Server (TFS) configured with SQL Server Analysis Services.

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.

Excel State pie chart report

Prerequisites

You can generate these reports only when you work with an on-premises Azure DevOps Server that has been configured with reporting services.

Create an Excel report from a flat-list query

Use this procedure when you work from the Team Explorer plug-in for Visual Studio.

  1. 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?

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

    Create Report in Microsoft Excel

  3. Select the check boxes of the reports that you want to generate.

    Expanded nodes, New Work Item Report dialog box

    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.

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

  1. 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:
    Error message displayed when prerequisites aren't met.

    New Report option from Team menu.

    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.

  2. Connect to the project and choose the query.

    If the server you need isn't listed, add it now.

  3. 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 Update (refresh) data in a PivotTable.