Rollup child work item values to parent sample report
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
Rollup provides support to show a count of work items or sum of Story Points, Remaining Work, or other custom field of child items. This article provides several examples of how to generate a tabular rollup report for Epics, Features, or User Stories that contain child work items. The following image shows an example of Story Points rolled up for their parent Features.
To learn more about rollup and options to show rollup, see Display rollup progress or totals in Azure Boards.
Note
This article assumes you've read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Prerequisites
- To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
- To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.
Sample queries
The following queries return data from the WorkItems entity set to support generating rollup matrix reports.
Note
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. To learn more about the data type of each value, review the metadata provided for the corresponding EntityType.
Rollup Story Points to Features of child User Stories based on 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/WorkItems?"
&"$filter=WorkItemType eq 'Feature'"
&" and State ne 'Cut'"
&" and startswith(Area/AreaPath,'{areapath}')"
&" and Descendants/any()"
&"& $select=WorkItemId,Title,WorkItemType,State,AreaSK"
&"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'User Story')"
&"/aggregate($count as CountOfUserStories, StoryPoints with sum as TotalStoryPoints)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
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.
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 Closed bugs.
and startswith(Area/AreaPath,'{areapath}')
Return work items under a specific Area Path, replacing 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()
Include all Features, even the ones with no User Stories. Replace with "any(d:d/WorkItemType eq 'User Story')" to omit Features that don't have child User Stories.
&$select=WorkItemId, Title, WorkItemType, State
Select fields to return.
&$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath),
Select expandable property fields AssignedTo, Iteration, Area.
Descendants(
Expand the Descendants clause.
$apply=filter(WorkItemType eq 'User Story')
Filter the descendants to only include User Stories (omits tasks and bugs).
/aggregate($count as CountOfUserStories, StoryPoints with sum as TotalStoryPoints)
For all descendants matching the filter clause, count them, and sum the StoryPoints property.
)
Close Descendants().
Rollup Story Points to Features of child User Stories based on Teams
The following queries show how to generate rollup reports filtering 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/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=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'User Story')"
&"/aggregate($count as CountOfUserStories, StoryPoints with sum as TotalStoryPoints)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Rollup Story Points to Epics
You can rollup story points to Epics using the following 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/WorkItems?"
&"$filter=WorkItemType eq 'Epic'"
&" and State ne 'Cut'"
&" and startswith(Area/AreaPath,'{areapath}')"
&" and Descendants/any(d:d/WorkItemType eq 'User Story')"
&"& $select=WorkItemId,Title,WorkItemType,State,AreaSK"
&"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'User Story')"
&"/aggregate(StoryPoints with sum as TotalStoryPoints)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Rollup Tasks Remaining Work and Completed Work to User Stories
The following query shows how to rollup Remaining Work and Completed Work assigned to child Tasks to User Stories in the hierarchy. These queries assume that Tasks are assigned as children of a User Story in the specified 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/WorkItems?"
&"$filter=WorkItemType eq 'User Story'"
&" and State ne 'Removed'"
&" and startswith(Area/AreaPath,'{areapath}')"
&" and Descendants/any()"
&"& $select=WorkItemId,Title,WorkItemType,State,AreaSK"
&"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'Task')"
&"/aggregate(RemainingWork with sum as TotalRemainingWork, CompletedWork with sum as TotalCompletedWork)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Rollup Bug count to Features
The following queries show how to rollup the count of Bugs assigned to Features. These queries assume that Bugs are defined as children of a Feature in the specified 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/WorkItems?"
&"$filter=WorkItemType eq 'Feature'"
&" and State ne 'Removed'"
&" and startswith(Area/AreaPath,'{areapath}')"
&" and Descendants/any()"
&"& $select=WorkItemId,Title,WorkItemType,State,AreaSK"
&"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath),"
&"Descendants("
&"$apply=filter(WorkItemType eq 'Bug')"
&"/aggregate($count as CountOfBugs)"
&")",
null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
(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 BI
The &$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath) clause returns records that contain several fields. Prior to creating the report, you need to expand the record to flatten it into specific fields. In this instance, you'll want to expand the following records:
AssignedToAreaPathIterationPath
To learn how, see Transform Analytics data to generate Power BI reports.
(Optional) Rename fields
Once you've expanded the columns, you may want to rename one or more fields. For example, you can rename the column AreaPath to Area Path. To learn how, see Rename column fields.
Replace null values in rollup fields
If a work item doesn't have any children, the rollup value may be null. For example, Descendants.CountOfUserStories is "null" if a Feature doesn't have any child User Stories.
For easier reporting, replace all nulls with zero by following these steps.
- Select the column by clicking the column header.
- Select the Transform menu.
- Select Replace Values. The Replace Values dialog appears.
- Enter "null" in Value to Find.
- Enter "0" in Replace With.
- Choose OK.
Repeat for all the rollup columns.
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 Table report
In Power BI, choose the Table report under Visualizations.
Add the following fields to Columns in the order indicated:
WorkItemI, choose Don't summarize to show ID if neededWorkItemTypeTitleStateCount of User StoriesTotal Story Points.
The example report displays.
Related articles
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for