List linked work items sample queries and reports
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
This article shows you how to create a report that lists work items linked to other work items. For example, the following report shows a list of Features that are linked to User Stories with the parent-child link type.
For more information about link types and linking work items, see Link user stories, issues, bugs, and other work items.
Other sample queries include listing bugs with a Duplicate link to another bug, and listing bugs that don't contain a Duplicate link to another bug.
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
Several queries are provided which show how to filter linked work items. All of these queries specify the WorkItems entity set as they return current data.
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.
Return Features and their child User Stories
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 'Closed' and State ne 'Removed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"&$select=WorkItemId,Title,WorkItemType,State,AreaSK "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
&"Links( "
&"$filter=LinkTypeName eq 'Child' "
&"and TargetWorkItem/WorkItemType eq 'User Story'; "
&"$select=LinkTypeName; "
&"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
&") "
,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 User Stories.
and State ne 'Closed' and State ne 'Removed'
Omit Features whose State is set to Closed or Removed.
and startswith(Area/AreaPath,'{areapath}')
Include only Features under a specific Area Path replacing '{areapath}'.
To filter by a team name, use the filter statement Teams/any(x:x/TeamName eq '{teamname})'.
&$select=WorkItemId,Title,WorkItemType,State,AreaSK
Select fields to return.
&$expand=AssignedTo($select=UserName), Iteration($select=IterationPath), Area($select=AreaPath),
Specify fields to use to expand AssignedTo, Iteration, and Area entities.
Links(
Expand the Links entity.
$filter=LinkTypeName eq 'Child'
Filter linked work items to only those with Child link type. Other examples are Parent, Child, Duplicate, Duplicate Of, Affects, Affected By.
and TargetWorkItem/WorkItemType eq 'User Story';
Only include linked User Stories.
$select=LinkTypeName;
Select the LinkTypeName property to return.
$expand=TargetWorkItem($select=WorkItemType, WorkItemId, Title, State)
Select the properties of the linked work item to return.
)
Close the Links() clause.
Return User Stories linked with the Related link type
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 'Closed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"&$select=WorkItemId,Title,WorkItemType,State,AreaSK "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
&"Links( "
&"$filter=LinkTypeName eq 'Related' "
&"and TargetWorkItem/WorkItemType eq 'User Story'; "
&"$select=LinkTypeName; "
&"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Return linked User Stories filtered by Teams
The following query is the same as the one used previously in this article, except it filters 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 'User Story' "
&"and State ne 'Closed' "
&"and (Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) or Teams/any(x:x/TeamName eq '{teamname}) "
&"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
&"Links( "
&"$filter=LinkTypeName eq 'Related' "
&"and TargetWorkItem/WorkItemType eq 'User Story'; "
&"$select=LinkTypeName; "
&"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Return bugs with a Duplicate link to another bug
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 'Bug' "
&"and State ne 'Closed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"and Links/any(x:x/LinkTypeName eq 'Duplicate' and x/TargetWorkItem/WorkItemType eq 'Bug') "
&"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath), "
&"Links( "
&"$filter=LinkTypeName eq 'Duplicate' "
&"and TargetWorkItem/WorkItemType eq 'Bug'; "
&"$select=LinkTypeName; "
&"$expand=TargetWorkItem($select=WorkItemType,WorkItemId,Title,State) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Return bugs that don't have a Duplicate link to another bug
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 'Bug' "
&"and State ne 'Closed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"and not (Links/any(x:x/LinkTypeName eq 'Duplicate' and x/TargetWorkItem/WorkItemType eq 'Bug')) "
&"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
,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.
Transform data in Power Query Editor
Prior to creating the report, you'll need to expand columns that return records containing several fields. In this instance, you'll want to expand the following records:
LinksLinks.TargetWorkItemAreaIterationAssignedTo
To learn how to expand work items, 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. You can rename them in the data table view, or later when you create the report. To learn how, see Rename column fields.
In this example, the following fields have been renamed:
| Original field name | Rename |
|---|---|
| Links.TargetWorkItem.ID | Target ID |
| LinksLinkTypeName | Link Type |
| Links.TargetWorkItem.State | Target State |
| Links.TargetWorkItem.Title | Target Title |
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 a table report to list linked work items
In Power BI, choose Table report under Visualizations.
Add the following fields in the order indicated to Columns:
- ID, right-click and select Don't summarize
- State
- Title
- Target ID, right-click and select Don't summarize
- Link Type
- Target State
- Target Title
The example report displays.