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.

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.

Svarīgi

• Deleting Area Paths or reconfiguring Iteration Paths causes irreversible data loss. For example, burndown and burnup widget charts, sprint burndown, and velocity charts for teams with changed Area Paths won't display accurate data. Historical trend charts reference the Area Path and Iteration Path as they were defined at the time for each work item. Once deleted, you can't retrieve historical data for these paths.
• You can only delete area and iteration paths that are no longer used by any work items.

For more information about burndown and burnup, see Configure a burndown or burnup widget and Burndown and burnup guidance.

Piezīme

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

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.

Piezīme

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.

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.

Piezīme

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.

Power BI query

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/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

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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)
        )

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.

Power BI query

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/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

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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)
        )

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.

Power BI query

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/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

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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)
        )

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.

Power BI query

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/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

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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)
        )

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.

Power BI query

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/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

OData query

You can use the following OData query code to modify or test the query prior to using it in Power BI. For more information, see Connect using Power BI and OData queries.

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)
        )

(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.

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.

Create the clustered column chart

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

   

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.

Related articles

• Explore sample reports using OData queries
• Construct OData queries for Analytics
• Connect to Power BI using OData queries
• Refer to metadata for Azure Boards Analytics
• Access sample reports and quick reference index
• Add a Team slicer to an existing report