Tracking your Agile project progress using Azure DevOps and Flow - The Sprint Backlog

This is the second post that discussed how information tracked in Azure DevOps, such as the sprint burndown chart and current sprint backlog can be exported to SharePoint using Flow.  This scenario applies to times when you want to share such information with clients without giving them direct access to Azure DevOps.
If you're new to Agile project management, I recommend you have a look at a book I recently published on this topic called Agile Office 365.  Besides providing you with an overview of Agile project management using the Scrum methodology, it provides you with lots of information on how to manage your Office 365 projects using these methods. 

To learn about ways to generate a sprint burndown chart, check out Part 1:  The Sprint Burndown Chart of this two-part series.

The Sprint Backlog

A product backlog is a tabular list that is used to capture the work that the team will be working on.  Throughout the life cycle of the project, the backlog gets re-prioritized over and over to ensure that the team's focus is on the scope that is most important to the business.  In Azure DevOps, the product backlog is typically broken down into 4 levels:

  • Epic
  • Feature
  • User Story / Bug
  • Task

During sprint planning, the top items from the product backlog are further broken down and scoped out to form the next sprint backlog.

Microsoft Flow includes 13 actions that allow you to query and update the items.  You can use those existing actions and leverage the Azure DevOps Analytics extension to make your life a little easier.  

The Ingredients

To build out the solution, I used 3 technologies from the Office 365/Azure stack

Azure DevOps

For those new to Azure DevOps, I recommend reading Introducing Azure DevOps.  One key assumption in my process was that all the tasks within a User Story or Bug would be completed within a single sprint.  During my sprint planning meeting, the User Stories/Bugs would be assigned to a specific sprint.

In order to build the sprint backlog with the User Stories and Bugs assigned to the current sprint, I created a custom query.  From those work items, I was able to retrieve all the tasks underneath them well as the parent Feature and Epic they belonged to.  To get the results, I had to compose the following query:

(Work Item Type = Bug) OR (Work Item Type = User Story) AND (Iteration Path = @CurrentIteration)

To build the proper hierarchy and report on progress, I needed to pull the ID, Work Item Type, Title, and State in my query. 

This query, which I called Current Sprint Activities is a key component to my Flow that I'm discussing further down in this article.

SharePoint List

The goal of the SharePoint list was to reconstruct the hierarchical nature of the epic/feature/user story or bug/task relationship.  To do so, I leveraged a custom SharePoint list with the following fields:

  • Title - to display the User Story or Bug name
  • Feature - to display and group the User Stories and Bugs by Feature
  • Epic - to display and group the Features by Epic
  • Status - to  display the current status of the item


Flow is used to populate the SharePoint list with the sprint backlog items.  The flow is broken down into 4 steps:
  1. Delete all existing list items
  2. Get the information from Azure DevOps
  3. Update the SharePoint list
In my case, step 2 was done by a separate Flow as Azure DevOps was on a different tenant.

Get the information from Azure DevOps

To make it easy to pass back the Azure DevOps information to the calling Flow, I was generating a JSON object that contained the information using the following structure:

  { "User Story Title" : "US1",
    "User Story State" : "S1",
    "Feature" : "F1",
    "Epic" : "E1"
  { "User Story Title" : "US2",
    "User Story State" : "S2",
    "Feature" : "F2",
    "Epic" : "E2"


  { "User Story Title" : "USn",
    "User Story State" : "Sn",
    "Feature" : "Fn",
    "Epic" : "En"

The first step involved calling the query to get all the User Stories and Bugs in the current sprint.  

Next, I leveraged the Azure DevOps Analytics extension to make it easier to get the ID, title, and work item type of the parent item (Feature or Epic).  I could have used the Send an HTTP request to Azure DevOps action to get this information ,but would have required a bit more manipulation of the results to parse out the values.

By looping through the items from the first, I was able to compose the JSON object discussed earlier.

As a safeguard, I'm using replace() to escape any double quotes and avoid invalid JSON.  For example, for the title, I used replace(items('Apply_to_each')?['System.Title'],'"','\"')

As well, I used coalesce() in case the Feature or Epic were not set for a User Story or Bug.

Update the SharePoint list

Now that I have the Epic, Feature, User Story or Bug names and Status for each item, I can create new list items. In this case, I have chosen to use other Status terms (hence the if-expression)

To get the hierarchical view, I created a list view that is grouped by Epic and Feature.

The flow was originally set up to update nightly.  However, it could also be set up to run as soon as any changes are made to the backlog.


Using this method, I was able to share the current sprint backlog with my clients without giving them direct access to Azure DevOps.  This approach can be used to share other information from DevOps without giving any access beyond the project team.

No comments:

Post a Comment

Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...