Feedback Editja

Sprint burndown sample reports

  • Artiklu
  • 10 minuti biex taqra

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

This article shows you how to display the current sprint's burndown of User Stories. The following example shows a burndown of both a sum of Story Points and a count of User Stories.

sprint burndown by total story points and count of user stories

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.

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

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

Begin trend at Iteration start.

and DateValue le Iteration/EndDate

End trend at Iteration 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()

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.

    Expand an entity column, Power BI + OData

  2. Select the fields to flatten.

    Select the fields to flatten.

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

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

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

sprint burndown by total story points and count of user stories

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