Sprint burndown sample reports

Azure DevOps Services | Azure DevOps Server 2019

This article shows you how to display the burndown of User Stories for the current sprint. The following image shows a burndown both by Story Points and User Stories count.

sprint burndown by total story points and count of user stroies

Note

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

Goal

Burndown User Stories in the current Sprint

Sample queries

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 startswith(Area/AreaPath,'{areapath}') "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate le now()  "
            &"and Iteration/EndDate ge now() "
        &") "
        &"/groupby( "
            &"(DateValue,State,WorkItemType,Priority,Area/AreaPath,Iteration/IterationPath), "
            &"aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Substitution strings

Each query contains the following strings that you must substitute with your values. Do not 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 partDescription
$apply=filter(Start filter()
WorkItemType eq 'User Story'Burndown on User Stories
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 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 DateValue ge Iteration/StartDateBegin trend at Iteration start.
and DateValue le Iteration/EndDateEnd trend at Interation end.
and Iteration/StartDate le now()Select current Iteration.
and Iteration/EndDate ge now()Select current Iteration
)Close filter()
/groupby(Start groupby()
(DateValue, State, WorkItemType, Priority, Area/AreaPath, Iteration/IterationPath), 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()

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.

    Power BI + OData - expanding an entity column

  2. Select the fields to flatten.

    Power BI + OData - expanding an entity column

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

    Power BI + OData - expanding an entity column

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

    Power BI Rename Columns

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

    Power BI Rename Query

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

    Power BI Close & Apply

Create the report

Power BI shows you the fields you can report on.

Note

The example below assumes that no one renamed any columns.

Power BI + OData - expanding an entity column

For a simple report, perform 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, which displays burndown on both Story Points and Count of Stories.

sprint burndown by total story points and count of user stroies

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

Additional queries that can be used to create different, but similar reports. You can use these queries with the steps defined above.

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.

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 (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate le now()  "
            &"and Iteration/EndDate ge now() "
        &") "
        &"/groupby( "
            &"(DateValue,State,WorkItemType,Priority,Area/AreaPath,Iteration/IterationPath), "
            &"aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

All Sprints since the beginning of the year

You may want to view a burndown of all the sprints in a single report. These queries pulls in sprint burndowns (by story points) for all the sprints since the beginning of the year 2019.

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 startswith(Area/AreaPath,'{areapath}') "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate ge 2019-01-01Z "
        &") "
        &"/groupby( "
            &"(DateValue,Iteration/EndDate,Area/AreaPath,Iteration/IterationPath,State,WorkItemType,Priority,AreaSK), "
            &"aggregate($count as Count, StoryPoints with sum as TotalStoryPoints) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Burndown by Tasks' Remaining Work

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.

Doc-ready Power BI Query (anonomized)
let
   Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? "
        &"$apply=filter( "
            &"startswith(Area/AreaPath,'{project}') "
            &"and StateCategory ne 'Completed' "
            &"and DateValue ge Iteration/StartDate "
            &"and DateValue le Iteration/EndDate "
            &"and Iteration/StartDate le now()  "
            &"and Iteration/EndDate ge now() "
            &"and WorkItemType eq 'Task' "
        &") "
            &"/groupby( "
            &"(DateValue,State,WorkItemType,Activity,Priority,Area/AreaPath,Iteration/IterationPath,AreaSK), "
            &"aggregate($count as Count, RemainingWork with sum as TotalRemainingWork) "
        &") "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Full list of sample reports