Skip to content

sample-boards-openbugs.md

Latest commit

 

History

History
232 lines (176 loc) · 8.49 KB

sample-boards-openbugs.md

File metadata and controls

232 lines (176 loc) · 8.49 KB
title Open bugs sample Power BI report
titleSuffix Azure DevOps
description Learn how to generate an open bugs Power BI report.
ms.subservice azure-devops-analytics
ms.custom powerbisample, engagement-fy23
ms.author chcomley
author chcomley
ms.topic sample
monikerRange <=azure-devops
ms.date 04/07/2026
ai-usage ai-assisted

Open bugs or user stories sample reports

[!INCLUDE version-lt-eq-azure-devops]

To generate a report that lists open bugs or user stories, select the Matrix report in Power BI and use a query similar to the ones provided in this article. The report you generate lists open bugs or user stories broken down by State and Assigned To fields, as shown in the following image.

:::image type="content" source="media/reports-boards/open-bugs-report.png" alt-text="Screenshot of Open Bugs sample matrix report.":::

[!INCLUDE temp]

[!INCLUDE prerequisites-simple]

Sample queries

This article provides several queries that filter bugs or user stories by area path, iteration path, or team. All of these queries specify the WorkItems entity set as they return current and not historical data.

[!INCLUDE temp]

Bugs filtered by Area Path

Power BI query

[!INCLUDE temp]

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'Bug' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

[!INCLUDE temp]

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
        $filter=WorkItemType eq 'Bug'
            and StateCategory ne 'Completed'
            and startswith(Area/AreaPath,'{areapath}')
        &$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK
        &$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)

Substitution strings and query breakdown

[!INCLUDE temp]

  • {organization} - Your organization name
  • {project} - Your team project name, or omit "/{project}" entirely, for a cross-project query
  • {areapath} - Your Area Path. Example format: Project\Level1\Level2

Query breakdown

The following table describes each part of the query.

:::row::: :::column span="1"::: Query part :::column-end::: :::column span="1"::: Description :::column-end::: :::row-end::: :::row::: :::column span="1"::: $filter=WorkItemType eq 'Bug' :::column-end::: :::column span="1"::: Returns bugs. :::column-end::: :::row-end::: :::row::: :::column span="1"::: and StateCategory ne 'Completed' :::column-end::: :::column span="1"::: Filters out items that are completed. For more information on State Categories, see How workflow category states are used in Azure Boards backlogs and boards. :::column-end::: :::row-end::: :::row::: :::column span="1"::: and startswith(Area/AreaPath,'{areapath}') :::column-end::: :::column span="1"::: Filters work items under a specific Area Path. To filter by Team Name, use the filter statement Teams/any(x:x/TeamName eq '{teamname}'). :::column-end::: :::row-end::: :::row::: :::column span="1"::: &$select=WorkItemId, Title, WorkItemType, State, Priority, Severity, TagNames :::column-end::: :::column span="1"::: Selects fields to return. :::column-end::: :::row-end::: :::row::: :::column span="1"::: &$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath) :::column-end::: :::column span="1"::: Selects expandable property fields AssignedTo, Iteration, and Area. :::column-end::: :::row-end:::

Bugs filtered by teams

You can query for open bugs by one or more teams rather than Area Path.

Power BI query

[!INCLUDE temp]

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'Bug' "
            &"and StateCategory ne 'Completed' "
            &"and (Teams/any(x:x/TeamName eq '{teamname}') or Teams/any(x:x/TeamName eq '{teamname}') or Teams/any(x:x/TeamName eq '{teamname}')) "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

[!INCLUDE temp]

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
        $filter=WorkItemType eq 'Bug'
            and StateCategory ne 'Completed'
            and (Teams/any(x:x/TeamName eq '{teamname}') or Teams/any(x:x/TeamName eq '{teamname}') or Teams/any(x:x/TeamName eq '{teamname}'))
        &$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK
        &$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)

User stories in a specific Area Path and Iteration Path

The following query supports filtering user stories for a specific Area Path and Iteration Path.

Power BI query

[!INCLUDE temp]

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
        &"$filter=WorkItemType eq 'User Story' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and startswith(Iteration/IterationPath,'{iterationpath}') "
        &"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
        &"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

OData query

[!INCLUDE temp]

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
        $filter=WorkItemType eq 'User Story'
            and startswith(Area/AreaPath,'{areapath}')
            and startswith(Iteration/IterationPath,'{iterationpath}')
        &$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK
        &$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)

[!INCLUDE temp]

Expand columns in Power Query Editor

The &$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath) clause returns records that contain several fields. Before you create the report, you need to expand the record to flatten it into specific fields. In this instance, you want to expand the following records:

  • AssignedTo
  • Area
  • Iteration

For more information, see Transform Analytics data to generate Power BI reports.

[!INCLUDE temp]

[!INCLUDE temp]

Create the Matrix report

  1. In Power BI, under Visualizations, select the Matrix report.

    :::image type="content" source="media/reports-boards/open-bugs-selections.png" alt-text="Screenshot of Power BI Visualizations and Fields selections for Open Bugs report. ":::

  2. Add Assigned To to Rows.

  3. Add State to Columns.

  4. Add WorkItemId to Values. Right-click WorkItemId and make sure Count is selected.

The example report displays.

:::image type="content" source="media/reports-boards/open-bugs-report.png" alt-text="Screenshot of Sample Open Bugs matrix report.":::

Related articles

[!INCLUDE temp]