Release burndown sample report

Azure DevOps Services | Azure DevOps Server 2020 | Azure DevOps Server 2019

This article shows you how to display the burndown of User Stories for a release based on work items tagged with a release tag. The following image shows a burndown both by Story Points and User Stories count.

Note

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

Prerequisites

• You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
• For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
• To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
• Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
• To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your *Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

• You must be a member of a project with Basic access or higher. If you haven't been added as a project member, get added now. Anyone with access to the project, except stakeholders, can view Analytics views.
• Verify that Analytics is installed, and if not, then enable it. You must be an account owner or a member of the Project Collection Administrators group to add extensions or enable the service.
• For Analytics data to be available, the corresponding service must be enabled. For example, to query work tracking data, Boards must be enabled. If it is disabled, Analytics views won't be displayed. To re-enable a service, see Turn an Azure DevOps service on or off
• To use Analytics views, enable the Analytics Views preview feature either for individual users or for the organization.
• Also, you must have your *View Analytics permission set to Allow. For more information, see Grant permissions to access the Analytics service.
• To use Power BI for Azure DevOps or to exercise an OData query for Analytics, you must must have your View Analytics permission set to Allow. By default, all Contributors with Basic access are granted access. To edit shared Analytics views, you must have your Edit shared Analytics views permission set to Allow. For more information, see Grant permissions to access the Analytics service.

Sample queries

Power BI query

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

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

Each query contains the following strings that you must substitute 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.
• {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}')

Work items under a specific Area Path. Replacing with Area/AreaPath eq '{areapath}' returns items at a specific Area Path.

To filter by Team Name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'

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

Specifies the Tag that represents the Release to burndown, 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().

/groupby (

Start groupby().

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

Query filters

To determine available query filters, query the metadata as described in Explore the Analytics OData metadata. You can filter your queries using any of the NavigationPropertyBinding Path values listed under an EntitySet. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType. Each EntitySet corresponds to an EntityType.

For example, the EntitySet Name="WorkItemSnapshot" corresponds to the EntityType Name="WorkItemSnapshot". The OData metadata for EntitySet Name="WorkItemSnapshot" is as shown below for v4.0-preview. You can add filters based on any of the listed NavigationPropertyBinding Path values.

<EntitySet Name="WorkItemSnapshot" EntityType="Microsoft.VisualStudio.Services.Analytics.Model.WorkItemSnapshot">
  <NavigationPropertyBinding Path="Date" Target="Dates"/>
  <NavigationPropertyBinding Path="RevisedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="Teams" Target="Teams"/>
  <NavigationPropertyBinding Path="Processes" Target="Processes"/>
  <NavigationPropertyBinding Path="Project" Target="Projects"/>
  <NavigationPropertyBinding Path="Area" Target="Areas"/>
  <NavigationPropertyBinding Path="Iteration" Target="Iterations"/>
  <NavigationPropertyBinding Path="AssignedTo" Target="Users"/>
  <NavigationPropertyBinding Path="ChangedBy" Target="Users"/>
  <NavigationPropertyBinding Path="CreatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ActivatedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ClosedBy" Target="Users"/>
  <NavigationPropertyBinding Path="ResolvedBy" Target="Users"/>
  <NavigationPropertyBinding Path="Tags" Target="Tags"/>
  <NavigationPropertyBinding Path="ChangedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ClosedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CreatedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="ResolvedOn" Target="Dates"/>
  <NavigationPropertyBinding Path="StateChangeOn" Target="Dates"/>
  <NavigationPropertyBinding Path="InProgressOn" Target="Dates"/>
  <NavigationPropertyBinding Path="CompletedOn" Target="Dates"/>
</EntitySet>

Power BI transforms

Expand Area, Iteration, AssignedTo columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

1. Choose the expand button.

   

2. Select the fields to flatten.

   

3. The table now contains entity field(s).

   

4. Repeat steps 1 through 3 for all fields representing entities: Area, Iteration, AssignedTo.

Rename fields and query, then Close & Apply

When finished, you may choose to rename columns.

1. Right-click a column header and select Rename...

   

2. You also may want to rename the query from the default Query1, to something more meaningful.

   

3. Once done, choose Close & Apply to save the query and return to Power BI.

   

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

For a simple report, do the following steps:

1. Select Power BI Visualization Clustered column chart.
2. Add the field "DateValue" to Axis
   • Right-click "DateValue" and select "DateValue", rather than Date Hierarchy
3. Add the field "TotalStoryPoints" to Values
4. Add the field "Count" to Values

The example report displays burndown on both Story Points and Count of Stories.

To pivot burndown by Area Path, do the following steps:

1. Select Power BI Visualization Stacked bar chart.
2. Add the field "DateValue" to Axis.
   • Right-click "DateValue" and select "DateValue", rather than Date Hierarchy.
3. Add the field "TotalStoryPoints" or "Count" to Values. You can't have two fields in Values.
4. Add the field "Area.AreaPath" to Legend.

The example report displays burndown pivoted by Area Path.

To pivot the burndown by State, add the field "State" to Values, replacing "Area.AreaPath".

Pull in data from multiple teams

Oftentimes, you want to aggregate and compare data from multiple teams. If you are pulling in items from multiple teams, consider adding a Team slicer to your report. A Team slicer allows you to filter the existing report by Team Name.

Additional queries

You can use the following additional queries to create different but similar reports using the same steps defined previously in this article.

Filter by Teams, rather than Area Path

This query is the same as the one used above, except it filters by Team Name rather than Area Path.

Power BI query

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

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

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

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 based off an 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 Stories by Iteration Path.

Power BI query

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

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 based off a custom field

Some organizations use a custom field to mark Stories for a Release. For example, they may have a field called "Milestone". This query shows you how to select Stories by a custom field. You'll need to replace both {customfield} and {releasevalue} in the query. To determine the name of your custom field, explore the Analytics metadata. You'll use the Property Name as {customfield}.

Power BI query

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

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

Full list of sample reports

• Open bugs
• Bug trend
• Rollup
• Feature progress
• Work items and direct links
• Release burndown
• Sprint burndown
• Cumulative Flow Diagram (CFD)
• Lead/Cycle Time

Related articles

• Add a Team slicer to an existing report
• Overview of sample reports using OData queries
• Connect using Power BI and OData queries
• Analytics OData query quick reference