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.
For more information about rollup and options to show rollup, see Display rollup progress or totals in Azure Boards.
Note
This article assumes you read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Prerequisites
- Access: Be a member of a project with at least Basic access.
- Permissions: By default, project members have permission to query Analytics and create views.
- For more information 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
. For more information 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
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see 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:
AssignedTo
AreaPath
IterationPath
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 neededWorkItemType
Title
State
Count of User Stories
Total Story Points
.
The example report displays.