Edit

Share via

Open bugs or user stories sample reports

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

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.

Screenshot of Open Bugs sample matrix report.

Note

This article assumes you read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.

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.

Sample queries

Several queries are provided which 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.

Note

To determine available properties for filter or report purposes, see Metadata reference for Azure Boards. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. For more information about the data type of each value, review the metadata provided for the corresponding EntityType.

Bugs filtered by Area Path

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.

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

Substitution strings and query breakdown

Substitute the following strings with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.

  • {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.

Query part

Description

$filter=WorkItemType eq 'Bug'

Return Bugs.

and StateCategory ne 'Completed'

Filter out items that are completed. For more information on State Categories, see How workflow category states are used in Azure Boards backlogs and boards.

and startswith(Area/AreaPath,'{areapath}')

And filter work items under a specific Area Path. To filter by Team Name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'.

&$select=WorkItemId, Title, WorkItemType, State, Priority, Severity, TagNames

Select fields to return.

&$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath)

Select expandable property fields AssignedTo, Iteration, Area.

User stories filtered by teams

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

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.

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

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.

Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.

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

(Optional) Rename query

You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.

Screenshot of Power BI query menu options, rename query.

Expand columns in Power Query Editor

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

  • AssignedTo
  • AreaPath
  • IterationPath

To learn how, see Transform Analytics data to generate Power BI reports.

(Optional) Rename fields

Once you've expanded the columns, you may want to rename one or more fields. For example, you can rename the column AreaPath to Area Path. To learn how, see Rename column fields.

Close the query and apply your changes

Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.

Screenshot of Power Query Editor Close and Apply option.

Create the Matrix report

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

    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 1WorkItemId1 to Values, and right-click 1WorkItemId` and ensure Count is selected.

The example report displays.

Screenshot of Sample Open Bugs matrix report.