ADO Cross-Project Dependency Report
Weekly flow queries Azure DevOps for work items with cross-project links (predecessor/successor relations). Builds a dependency matrix showing which projects are waiting on deliverables from other projects. Exports to Excel on SharePoint and emails project leads a summary highlighting at-risk dependencies where the upstream item is past due.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
A scheduled, fully portable Power Automate cloud flow that iterates across multiple Azure DevOps projects, queries work items with cross-project relations (predecessor/successor/related links), builds a dependency matrix, saves an HTML report to SharePoint, and emails project leads a styled summary highlighting at-risk dependencies. Built with the FlowLibs publisher in the yourcompany (default) environment via the API-first build pattern.
Use Case
Project and program managers operating multi-team Azure DevOps organizations need visibility into work items that depend on deliverables owned by other projects. Cross-project dependencies are a common source of slipped milestones because they fall outside any single team's burndown. This flow runs every Monday morning, scans a configurable list of projects, and produces a one-glance dependency matrix so leads can flag at-risk upstream work before standup.
Flow Architecture
Recurrence Weekly
RecurrenceFires every Monday at 08:00 Eastern Standard Time.
Init varAdoOrganization
Initialize VariableADO organization name from the flowlibs_AdoOrganization environment variable.
Init varAdoProjectList
Initialize VariableComma-separated project list from the flowlibs_AdoProjectList environment variable.
Init varSharePointSiteUrl
Initialize VariableSharePoint site URL from the flowlibs_SharePointSiteURL environment variable.
Init varNotificationEmail
Initialize VariableReport recipient email from the flowlibs_NotificationEmail environment variable.
Init varExcelPath
Initialize VariableSharePoint file path from the flowlibs_DependencyReportExcelPath environment variable.
Init varDependencyRows
Initialize VariableEmpty array accumulator for dependency row objects across all projects.
Init varHtmlTable
Initialize VariableHTML string accumulator for the report body.
Init varAtRiskCount
Initialize VariableInteger counter for at-risk dependencies (predecessor not Active).
Split Project List Into Array
Composesplit(variables('varAdoProjectList'), ',') — converts the comma-separated env var into an iterable array of project names.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_AdoOrganization | String | <configure> | Azure DevOps organization name. Set this to your Azure DevOps organization (the value after dev.azure.com/ in your org URL). |
| flowlibs_AdoProject | String | FlowLibs | Primary Azure DevOps project (referenced in solution but project iteration uses flowlibs_AdoProjectList). Reused from prior FlowLibs solutions. |
| flowlibs_AdoProjectList | String | <configure> | Comma-separated list of Azure DevOps project names to scan for cross-project dependencies (e.g. ProjectA,ProjectB,ProjectC). |
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com | SharePoint site for report file output. Reused from prior FlowLibs solutions. |
| flowlibs_NotificationEmail | String | alerts@yourcompany.com | Recipient email address (or semicolon-separated list) for the dependency report. Reused from prior FlowLibs solutions. |
| flowlibs_DependencyReportExcelPath | String | /Shared Documents/FlowLibs - ADO Dependency Report.xlsx | SharePoint path for the output report file. New for this flow. |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Azure DevOps | shared_visualstudioteamservices | HttpRequest (POST WIQL queries and GET work items with $expand=relations) |
| SharePoint Online | shared_sharepointonline | CreateFile (Writes the HTML dependency report to the configured SharePoint document library) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (Sends the dependency report email to project leads) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Add more projects
- Update the flowlibs_AdoProjectList env var with a comma-separated list of project names. The flow will iterate each one automatically.
- Change schedule
- Modify the Recurrence trigger — adjust frequency, interval, weekDays, hours, minutes, and timeZone.
- Different recipients
- Update flowlibs_NotificationEmail. The Outlook SendEmailV2 To field accepts semicolon-separated addresses.
- Add Excel export
- Replace the HTML CreateFile with an Excel Online AddRowV2 action to write structured rows into a workbook table.
- Enrich linked items
- Inside Loop_Each_Cross_Link, add a second ADO HttpRequest GET to _apis/wit/workitems/{targetId}?$expand=all to fetch the linked item's title, state, and due date for richer reporting.
- At-risk highlighting
- After enrichment, use if(less(formatDateTime(item()?['dueDate'],'yyyy-MM-dd'), formatDateTime(utcNow(),'yyyy-MM-dd')), 'background-color:red', '') as a row style attribute to highlight overdue upstream items.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Split project list into array
Converts the comma-separated env var value into an iterable array of project names for the outer Apply to each.
EXPR.02Cap work item batch size
Limits the WIQL result set to the first 200 work items to stay within Azure DevOps batch GET limits.
EXPR.03Join work item IDs for batch GET
Joins the projected id array into a comma-separated string suitable for the batch work items GET call.
EXPR.04Null-safe relations access
Returns an empty array when a work item has no relations property, preventing null reference errors in the Filter Array.
EXPR.05Assigned-to fallback
Uses the work item's assignee display name when present, otherwise 'Unassigned'.
EXPR.06Email importance escalation
Sets the report email's importance to High when one or more at-risk predecessor dependencies were found.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.