How to Create an ADO Work Item Progress Report

Create an ADO Work Item Progress Report with Analytics Views

Enhance your team’s project management by using Analytics Views and Power BI to track the status of child work items, such as user stories or tasks. The ADO work item progress report helps your team quickly display completed work per sprint, identify overdue work items, and serve as an audit tool to highlight incorrectly assigned features per sprint. By monitoring the completion of these child work items, teams can accurately assess overall progress, identify potential bottlenecks, and ensure that all project aspects are on track.

The Azure DevOps help file provides guidance on setting up the Feature Progress and Rollup reports using ODATA queries. This blog post demonstrates how to create the report using Analytics Views as the data source. Using an Analytics View makes it easier to get started and offers flexibility in reporting, moving away from the specialized ODATA queries to making data available for a wider range of reporting options.

This blog post will guide you through the process of setting up the Analytics View and adding the necessary DAX code for calculations to provide the data for a report similar to this:

NOTE: The data in this report only goes one level below the parent for a parent/child relationship. Meaning, if the top level work item is an Epic, then it will report on the associated Features or User Stories. If your report requires children and grandchildren work item reporting, then review this blog on other data model options.

CREATE the ANALYTICS VIEWS:

Create an Analytics Views for the parent work item type, such as Epic or Feature. The ADO work item progress report uses the Feature work item type as the parent work item type. It will report on a single team’s work items. If you are new to using Analytics Views and Power BI reports, check out this blog post on getting started.

NOTE: Leave out the filter by Team if your report will contain the features for the ADO organization. Review this blog post if you need to report across different teams or ADO organizations. Please note, throttling will be enabled by the ADO analytics if trying to return too many work items for the report.

Add the fields to use in the report.

Use the Current work item option for this report.

Save the Analytics Views with a name to help differentiate between the Features and the User Story analytics views.

Create the User Story Analytics Views

Follow the same steps as the Feature work item AV and change the work item type to User Stories. Or, Product Backlog items if using the Scrum template.

The only difference is to select the Stories Backlog instead of just the User Story (or PBI) work item type. This ensures the other work item types, such as bugs, which are on the backlog are included in the report.

Add the Story Points field if you are reporting on the completion by points in your report. You can also add the Original Estimate, Completed Work and Remaining Work fields if your teams utilize these fields.

Power BI Report

Follow the steps in this blog post on connecting to Azure DevOps and downloading the work items from the Analytics Views.

Create the Relationships between the Analytics Views Tables

Power BI should detect the relationship between the Analytics Views and create a one to many relationship on the Work Item ID and the Parent Work Item ID field. If not, create one and should look like the following:

Data Transformations

In the Power Query editor, change the Analytics Updated Date field from Date/Time zone to Date/Time

Add a Work item Search field to allow use in a slicer by following the instructions in this blog post.

Shorten the Area Path node to create a slicer for filtering by team or Area Path by following the instructions in this blog post.

DAX Measures

The following DAX measures are are variations on counting child work items. This is used for the rollup metrics on the report. The pattern used in the calculcation should be adjusted to meet your reporting needs.

The final DAX measure provides the percent completed calculation for the user stories or bugs associated to the parent feature work item.

NOTE: You will need to rename the tables used in the DAX code to the names used in your report.

Count the Total Child Work Items for the Feature:

The DAX code is completing the following:

Obtain the Feature’s work item ID for the current work item in the table. Think of it as the current row in Excel.
Count all the children work items where the Feature work item is the parent work item.
Return a 0 if the count is blank. Otherwise, return the count.

Feature-TotalChildren-Count = 
//Counts the user stories under a Feature
var _workitemID = SELECTEDVALUE('Features'[Work Item Id])
var _wiTotal = CALCULATE(
        COUNTROWS('UserStories'),
        'UserStories'[Parent Work Item Id] = _workitemID            
    )
var _result = IF(ISBLANK(_wiTotal ),0,_wiTotal )
return _result

Count the User Stories associated to the Feature:

This DAX calculation counts only the User Stories for the parent Feature. This is only needed if there are Bugs or other work items included in the child work item analytics view.

Feature-UserStory-Count = 
//Counts the User Story work items under a Feature
var _workitemID = SELECTEDVALUE('Features'[Work Item Id])
var _wiTotal = CALCULATE(
        COUNTROWS('UserStories'),
        'UserStories'[Parent Work Item Id] = _workitemID,
        'UserStories'[Work Item Type] = "User Story"            
    )
var _result = IF(ISBLANK(_wiTotal ),0,_wiTotal )
return _result

Count the Bugs associated to the Feature:

Feature-Bug-Count = 
//Counts the User Story work items under a Feature
var _workitemID = SELECTEDVALUE('Features'[Work Item Id])
var _wiTotal = CALCULATE(
        COUNTROWS('UserStories'),
        'UserStories'[Parent Work Item Id] = _workitemID,
        'UserStories'[Work Item Type] = "Bug"            
    )
var _result = IF(ISBLANK(_wiTotal ),0,_wiTotal )
return _result

Count the Completed User Stories and Bugs associated to the Feature:

Same as the other DAX calculations with the difference that it is including the work item’s State as a filter.

Feature-Completed-Count = 
//Counts the closed User Stories and Bugs
var _workitemID = SELECTEDVALUE('Features'[Work Item Id])
var _effortTotal = CALCULATE(
        COUNTROWS('UserStories'),
        'UserStories'[Parent Work Item Id] = _workitemID,
        'UserStories'[State] in {"Closed","Completed","Resolved"}            
    )
var _result = IF(ISBLANK(_effortTotal),0,_effortTotal)
return _result

Count just the completed User Stories associated to the Feature:

Feature-UserStory-Completed-Count = 
//Counts the closed User Stories and Bugs
var _workitemID = SELECTEDVALUE('Features'[Work Item Id])
var _effortTotal = CALCULATE(
        COUNTROWS('UserStories'),
        'UserStories'[Parent Work Item Id] = _workitemID,
        'UserStories'[Work Item Type] = "User Story",
        'UserStories'[State] in {"Closed","Completed","Resolved"}            
    )
var _result = IF(ISBLANK(_effortTotal),0,_effortTotal)
return _result

Completed % of Completed Work for the Feature:

Feature-Completed-% = 
//Calculate the % completed by the count of closed User Stories and Bugs
var _completedPerc = DIVIDE([Feature-Completed-Count],[Feature-TotalChildren-Count],0)

return _completedPerc

Uses for the Report

Feature % Complete and Rollup or Count of Child Workitems

(1) This is the percentage completed of the user story, PBI, work items per feature.
(2) Use conditional formatting on the column to change the font to red to show there are open bugs.
(3) Use conditional formatting on the column to show there are no associated user stories to the feature. This might be a requirement for work to be completed. Could indicate a Feature is moved to a future iteration or kept on the backlog until there is work assigned at a lower level, such as the user story.
(4) Set the Iteration status to view the current iteration and view the status of the work in the current sprint
(5) View the status all up for Features in the sprint. This shows the total number of user stories and how many are completed for the sprint.

Audit Report

(1) Set the Iteration Status to Past
(2) Select all of the New or In Progress work item states. This shows the Features assigned to past sprints that are still open.
(3) Use conditional formatting on the column to show there are no assoicated user stories to the feature. This might be a requirement for work to be completed. Maybe a Feature is moved to a future iteration or kept on the backlog until there is work assigned at a lower level, such as the user story.

Reach out to me at blog@azeeki.com if you want a link to the Power BI report.

Hope this helps with your ADO reporting journey!