Edit

Share via

Overview of sample reports using OData queries

  • Article

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

You can use Open Data Protocol (OData) queries to generate Power BI reports. For various services in Azure DevOps, sample queries are available for generating numerous popular reports. This article provides an overview of the sample reports and shows you how to get started with them.

Sample reports

The following articles provide queries and steps for generating the sample reports.

Azure Boards

Azure Test Plans

Azure Pipelines

Azure Pipelines tests

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
  • A section about transforming data in Power BI - Steps to transform the data into a reportable format by expanding columns or changing column data types
  • Create the report - Steps to create a basic report from the data
Category Requirements
Access levels - Project member.
- 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.

Run a Power BI query

To get started embedding an OData query in a Power BI query, take the following steps. You can paste queries directly into the Advanced Editor in Power BI.

  1. In Power BI, select Get data, and then select Blank query.

    Screenshot of a blank report in Power BI Desktop. On the ribbon, Get data is expanded. Get data and Blank query are highlighted.

  2. In the Power BI Power Query Editor, select Advanced Editor.

    Screenshot of the Power BI Power Query Editor. On the ribbon, in the Query group, Advanced Editor is highlighted.

    The Advanced Editor window opens.

    Screenshot of the Advanced Editor window in Power BI. A sparsely populated field is available for editing a query.

  3. Replace the contents with a sample query. For sample queries, see the linked articles listed earlier in this article.

    Screenshot of the Advanced Editor window in Power BI. The query field contains a query for bugs. The query syntax includes placeholders.

  4. Replace any placeholder strings in the query with your values:

    • {organization} - Your organization name.
    • {project} - Your team project name. For a query that spans multiple projects, omit /{project} entirely.

    Some sample queries contain other placeholders that you also need to replace, such as the following strings:

    • {areapath} - Your area path, in the following format: Project\Level1\Level2.
    • {iterationpath} - Your iteration path, in the following format: Project\Level1\Level2.
    • {startdate} - The date to start your trend report on, in the following format: YYYY-MM-DDZ. For example, 2025-04-01Z represents April 1, 2025. Don't enclose the date in quotation marks.

    Screenshot of the Advanced Editor window. The organization, project, area path, and start date use actual values and are highlighted.

  5. To run the query, select Done.

    When you connect to your account for the first time, Power BI requires you to authenticate. For detailed information, see Enter client credentials in Power BI or Excel.

Run a raw OData query

Besides a Power BI query, most sample reports also provide an OData query. You can modify and test the OData query separately before you use it in Power BI. This method provides more flexibility, but it involves extra steps. For more information, see Connect with data by using Power BI and OData queries.

For more information on how to write OData queries against Analytics data, see Sample reports and quick reference index.

Related content