Release burndown sample queries and reports

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

Burndown and burnup reports show how much work is getting completed over time. The following image shows burndown both by count of User Stories and sum of Story Points.

Screenshot of Power BI Release burndown clustered column chart report.

Burndown charts only make sense if you plan your work for the time period of the chart. Otherwise, if you plan on a sprint-by-sprint basis, you won't see a standard burndown. In these instances, a burnup chart makes more sense to see how progress is made over time.

Important

Deleting Area Paths or reconfiguring Iteration Paths can cause a loss of data and can't be reverted. For example, burndown or burnup widget charts, sprint burndown, and velocity charts for teams whose Area Paths are changed won't reflect correct data. Historical trend charts reference the Area Path and Iteration Path as defined at a point in the past for each work item. When an Area Path or Iteration Path is deleted, then the historical data for it can't be retrieved.

To learn more about burndown and burnup, see Configure a burndown or burnup widget and Burndown and burnup guidance.

Note

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

Prerequisites

  • To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
  • To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Sample queries

The queries in this section show how to generate burndown charts of work items based on historical data. All of these queries specify the WorkItemSnapshot entity set.

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. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.

Burndown of User Stories in an area path from start and end date

The following query shows you how to return historical data of User Stories for a release based on work items tagged with a release tag.

Note

For reports based on filtering a tag, the tag must be assigned to the work item at the start of the release or burndown/burnup start date. Otherwise, the work item isn't included in the returned data.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,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
  • {tag} - A tag that represents your release. All work items tagged with {tagname} are included in the report
  • {startdate} - The date to start the burndown report with the format: YYYY-MM-DDZ. For example: 2022-04-01Z represents 2022-April-01. Don't enclose in quotes.
  • {enddate} - The date to end the burndown report.

Query breakdown

The following table describes each part of the query.

Query part

Description

$apply=filter(WorkItemType eq 'User Story'

Include User Stories in burndown.

and StateCategory ne 'Completed'

Filters out items that are completed. For more information on State Categories, see How workflow states and state categories are used in Backlogs and Boards.

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

Include only User Stories under a specific Area Path replacing '{areapath}'.
To filter by a team name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'.

and Tags/any(x:x/TagName eq '{tagname}').

Specifies the tag label that represents the release to burndown on, and to include all work items tagged with {tagname} in the report.

and DateValue ge {startdate}

Start burndown on or after the specified date. Example: 2019-04-01Z represents 2019-April-01.

and DateValue le {enddate}

Start burndown on or before the specified date. Same format as {startdate}.

)

Close filter() clause.

/groupby (

Start groupby() clause.

(DateValue, State, Area/AreaPath),

Group by DateValue (used for trending), and any fields you want to report on.

aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints)

Aggregate by count of user stories and sum of Story Points.

)

Close groupby() clause.

Burndown of User Stories for a team

The following query returns burndown data filtered by team name rather than Area Path.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"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}') "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown User Stories with a snapshot every Friday

Using a weekly snapshot reduces the amount of data pulled into Power BI and increases query performance.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Tags/any(x:x/TagName eq '{tagname}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &"and Date/DayName eq 'Friday' "
        &") "
        &"/groupby ( "
            &"(DateValue,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown User Stories based on an area and iteration path

Some organizations use Iteration Paths to mark Stories for a release. For example, they may have an Iteration Path of MyProject\Release1. The following query shows how to select User Stories by Iteration Path.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and startswith(Iteration/IterationPath,'{iterationpath}') "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &") "
        &"/groupby ( "
            &"(DateValue,StateCategory,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown User Stories with a custom field value

Some organizations use a custom field to mark User Stories for a release. For example, they may have a field called Milestone. This query shows you how to select User Stories by a custom field.

You'll need to replace both {customfieldname} and {releasevalue} in the query. To determine the name of your custom field, see Metadata reference for Azure Boards, Custom properties. You'll replace {customfieldname} with the custom property name, for example Custom_Milestone.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review Overview of sample reports using OData queries.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot?"
        &"$apply=filter(WorkItemType eq 'User Story' "
            &"and StateCategory ne 'Completed' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and {customfieldname} eq '{releasevalue}' "
            &"and DateValue ge {startdate} "
            &"and DateValue le {enddate} "
            &") "
        &"/groupby ( "
            &"(DateValue,StateCategory,State,Area/AreaPath,AreaSK), "
            &"aggregate ($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,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

Prior to creating the report, you'll need to expand columns that return records containing several fields. In this instance, you'll want to expand the following records:

  • Links
  • Links.TargetWorkItem
  • Area
  • Iteration
  • AssignedTo

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

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 clustered column chart

  1. In Power BI, choose Clustered column chart under Visualizations.

    Screenshot of Power BI Visualizations and Fields selections for Release burndown clustered column chart report.

  2. Add DateValue to X-Axis, right-click and select DateValue, rather than Date Hierarchy

  3. Add Count to Y-Axis.

  4. Add TotalStoryPoints to Y-Axis.

The example report displays burndown on both the count of Stories and sum or Story Points.

Screenshot of Power BI Sample Release burndown clustered column chart report.