Overview of sample reports using OData queries
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
In this article, we provide samples for the most popular reports:
Azure Boards
Azure Test Plans
Pipeline
Pipeline and test
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 along with other sample queries.
- Transform data in Power BI - Steps to transform the data into a reportable format
- Create the report - Steps to create a simple report from the data.
Prerequisites
- Access: Be a member of a project with at least Basic access.
- Permissions: By default, project members have permission to query Analytics and create views.
- For more information about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.
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:
Select Get Data, and then Blank Query.
From the Power BI Query editor, select Advanced Editor.
The Advanced Editor window opens.
Replace the contents with one of the sample queries provided in the linked articles listed earlier in this article.
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-01Zrepresents 2019-April-01. Don't enclose in quotes.
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
Most sample reports provide an OData query 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, see OData Query Quick Reference