Overview of sample reports using OData queries

Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020 | Azure DevOps Server 2019

In this article, we provide samples for the most popular reports:

---

Azure Boards

Azure Test Plans

---

• Open bugs
• Bug trend
• Rollup
• Feature progress
• Work items and direct links
• Release burndown
• Sprint burndown
• Cumulative Flow Diagram (CFD)
• Lead/Cycle Time

• Progress status
• Requirements tracking
• Requirements tracking - Rollup
• Execution Trend
• Test suites aggregated view
• Tester by outcome matrix
• Configuration by outcome matrix

---

Pipeline

Pipeline and test

---

• Outcome summary
• Outcome summary for all pipelines
• Pass rate trend
• Stage wise failures
• Duration
• Duration trend
• Task duration
• Task duration trend

• Test summary
• Test summary trend
• Failed tests
• Flaky tests
• Test duration
• Test duration trend
• Pass rate trend of a test

---

---

• Open bugs
• Bug trend
• Rollup
• Feature progress
• Work items and direct links
• Release burndown
• Sprint burndown
• Cumulative Flow Diagram (CFD)
• Lead/Cycle Time

All sample report articles provide the following sections and information:

• Sample queries - The Power BI Query and raw OData query used to pull data into Power BI
• Power BI transforms - Steps to transform the data into a reportable format
• Create the report - Steps to create a simple report from the data
• Additional queries - Other sample queries for similar, but different reports.

Prerequisites

• You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
• For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
• To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
• Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
• To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your *Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

• You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
• Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
• For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
• To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
• Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
• To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

A Power BI query

Get started quickly with the following steps that embed the OData query in the Power BI query. The query can be pasted directly into Power BI's Advanced Query editor as follows:

1. Select Get Data, and then Blank Query.

   

2. From the Power BI Query editor, select Advanced Editor.

   

3. The Advanced Editor window opens.

   

4. Replace the contents with one of the sample queries provided in the linked articles listed at the top of this article.

   

5. The sample queries have strings that must be substituted with your values, such as {organization} and {project}.

   • {organization} - Your organization name
   • {project} - Your team project name. Or omit "/{project}" entirely, for a cross-project query.

   Depending on the sample query, there may be one or more other strings to replace, like:

   • {areapath} - Your Area path. Format: Project\Level1\Level2
   • {iterationpath} - Your Iteration path. Format: Project\Level1\Level2
   • {startdate} - The date to start your trend report on. Format: YYYY-MM-DDZ. Example: 2019-04-01Z represents 2019-April-01. Don't enclose in quotes.

   

6. To execute the query, select Done.

   If you've never connected to your account, Power BI may require you to authenticate. For details, see Client authentication options.

Raw OData query

The OData Query is provided in case you want to modify and test the OData query separately, before using it in Power BI. This method provides more flexibility, but involves other steps before using the OData Query in Power BI as described in Connect using Power BI and OData queries

For more information on how to write OData queries against Analytics, check out the OData Query Quick Reference

Related articles

• Data available from Analytics
• Data model for Analytics
• Analytics views dataset design
• OData API versioning