Feature progress rollup sample report

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

This article shows you how to create a stacked bar report to display progress of Features based on completed child User Stories. The report displays the percentage complete by rollup of Story Points for a given set of active Features. An example is shown in the following image.

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/WorkItems?"
        &"$filter=WorkItemType eq 'Feature' "
            &"and State ne 'Cut' "
            &"and startswith(Area/AreaPath,'{areapath}') "
            &"and Descendants/any()"
            &"&$select=WorkItemId,Title,Area,Iteration,AssignedTo,WorkItemType,State,AreaSK"
            &"&$expand=Descendants( "
            &"$apply=filter(WorkItemType eq 'User Story') " 
                &"/groupby((StateCategory), "
                &"aggregate(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/WorkItems?
        $filter=WorkItemType eq 'Feature'
            and State ne 'Cut'
            and startswith(Area/AreaPath,'{areapath}')
            and Descendants/any()
        &$select=WorkItemId,Title,Area,Iteration,AssignedTo,WorkItemType,State,AreaSK
        &$expand=Descendants(
            $apply=filter(WorkItemType eq 'User Story')
                /groupby((StateCategory),
                aggregate(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

Query breakdown

The following table describes each part of the query.

Query part

Description

---

---

$filter=WorkItemType eq 'Feature'

Return Features.

and State ne 'Cut'

Omit Features marked as Cut.

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 Descendants/any()

Filters out any work item that has at least one or "any" descendant. Includes all Features with at least one Child WIT. To get all work items with their descendants, even if they don't have any, run a query without the Descendants/any() filter. To omit Features that don't have child User Stories, replace with any(d:d/WorkItemType eq 'User Story').
For all work items with and without descendants:
$filter=endswith(Area/AreaPath,'suffix') &$select=WorkItemId,Title,WorkItemType,State,Area, Descendants &$expand=Descendants($select=WorkItemId)

For all work items with at least one descendant: $filter=endswith(Area/AreaPath, 'suffix')and Descendants/any() &$select=WorkItemId,Title,WorkItemType,State,Area, Descendants &$expand=Descendants($select=WorkItemId)

&$select=WorkItemId, Title, WorkItemType, State

Select fields to return.

&$expand=Descendants(

Expand Descendants.

$apply=filter(WorkItemType eq 'User Story')

Filters the descendants. Only include User Stories (omits Tasks and Bugs).

/groupby((StateCategory),

Group the rollup by StateCategory. For more information on State Categories, see How workflow states and state categories are used in Backlogs and Boards.

aggregate(StoryPoints with sum as TotalStoryPoints))

Aggregate sum of Story Points.

)

Close Descendants().

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

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. Expand the record to flatten the entity into its fields.

For the Feature Progress report, you'll need to carry out the following transforms:

• Expand the Descendants column into two columns: Descendants.StateCategory and Descendants.TotalStoryPoints
• Apply Pivot Column transform on Descendants.StateCategory column to separate out individual State categories
• Replace null values in pivoted columns
• Add a custom column to represent percentage complete.

Note

In this example, the State values for User Story include Proposed, In Progress, and Completed.

Expand Descendants column

The Descendants column contains a table with two fields: State and TotalStoryPoints. Expand it.

1. Select the expand button on the Descendants column.

   

2. Check all the fields and choose OK.

   

3. Table now contains rollup fields.

   

Pivot Descendants.StateCategory column

1. Select the Descendants.StateCategory column header to select it.

2. Select Transform menu and then Pivot Column.

3. For Values select "Descendants.TotalStoryPoints".

4. Press OK. Power BI creates a column for every StateCategory value.

   

Replace null values in pivoted columns

Some of the new Pivoted StateCategory columns will have null values. For easier reporting, replace the nulls with zeroes.

1. Select the column by clicking the column header.
2. Select the Transform menu.
3. Select Replace Values. The Replace Values dialog appears.
4. Enter "null" in Value to Find.
5. Enter "0" in Replace With.
6. Choose OK.

Repeat for every Pivoted StateCategory column.

Create a percentage complete computed column

1. Select Add Column menu.

2. Select Custom Column.

3. Enter PercentComplete for New column name.

4. Enter the following in Custom column formula.

   = [Completed]/([Proposed]+[InProgress]+[Resolved]+[Completed])
   

   

   Note

   It is possible you won't have a Resolved column, if the work items don't have States mapped to the Resolved State Category. If so, then omit "[Resolved]" in the above formula.

5. Press OK.

6. Select Transform menu.

7. Select Data Type and select Percentage.

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 the report, do the following steps:

1. Under Visualizations, choose the Stacked Bar Chart.
2. Add the Title field to Axis.
3. Add the PercentComplete to Values.
   • Right-click PercentageComplete and select Sum.

The example report displays.

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/WorkItems?"
        &"$filter=WorkItemType eq 'Feature' "
            &"and State ne 'Cut' "
            &"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 Descendants/any() "
        &"&$select=WorkItemId,Title,WorkItemType,State,AreaSK "
        &"&$expand=Descendants( "
            &"$apply=filter(WorkItemType eq 'User Story') "
                &"/groupby((StateCategory), "
                &"aggregate(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/WorkItems?
        $filter=WorkItemType eq 'Feature'
            and State ne 'Cut'
            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 Descendants/any()
        &$select=WorkItemId,Title,WorkItemType,State,AreaSK
        &$expand=Descendants(
            $apply=filter(WorkItemType eq 'User Story')
                /groupby((StateCategory),
                aggregate(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