ADO Cross-Project Dashboards

Covers AB#407 — cross-project work-item visibility across all hybridcloudsolutions ADO projects.

---

Context

The hybridcloudsolutions org uses a 1:1 ADO project-per-repo model (see ADO project strategy). With 52+ projects there is no built-in unified view — answering "what is open across all projects?" or "what has gone stale?" means navigating each project individually.

This dashboard lives in Platform Engineering and pulls cross-project data via WIQL queries that omit any [System.TeamProject] predicate, returning results across every project the authenticated user can see. This is a reporting and visibility concern only — work items remain owned by their individual projects. It is consistent with the cross-project linking model described in Work item sync.

---

What was created

Shared queries

All five queries are stored under Shared Queries / Platform Cross-Project in the Platform Engineering project. They are flat (non-tree) queries, which is required by the chart and scalar dashboard widgets.

Query name	Purpose	Key WHERE clause
Open items by project	All open items org-wide — backs the column chart + scalar tile	State NOT IN (Closed, Resolved, Removed, Done)
Open items by state	All open items — backs the state pie chart	State NOT IN (Closed, Resolved, Removed, Done)
Unassigned open items	Open items with no assignee — things that need attention	+ AssignedTo = ''
Aging items — 14+ days no update	Open items not touched in 14+ days	+ ChangedDate < @Today - 14
Recently closed — last 7 days	Work completed this week	State IN (Closed, Resolved, Done) + ChangedDate >= @Today - 7

Dashboard widgets

Dashboard name: Platform — Cross-Project Health (Platform Engineering project, default team).

Widget	Type	Backing query	Grid position / size
Total open work items	Query Scalar	Open items by project	row 1, col 1 — 1×2
Aging items (14+ days)	Query Scalar	Aging items — 14+ days no update	row 1, col 3 — 1×2
Open items by project	Chart (ColumnChart, group by TeamProject)	Open items by project	row 2, col 1 — 2×4
Open items by state	Chart (PieChart, group by State)	Open items by state	row 2, col 5 — 2×4
Unassigned open items	Query Results grid	Unassigned open items	row 4, col 1 — 2×6
Recently closed — last 7 days	Query Results grid	Recently closed — last 7 days	row 6, col 1 — 2×6

---

Full WIQL

These queries can be pasted into ADO's query editor or used to hand-recreate them if the script is lost.

-- Open items by project
SELECT [System.Id],[System.WorkItemType],[System.Title],[System.State],
       [System.TeamProject],[System.AssignedTo],[System.ChangedDate]
FROM WorkItems
WHERE [System.State] NOT IN ('Closed','Resolved','Removed','Done')
ORDER BY [System.TeamProject] ASC,[System.ChangedDate] ASC

-- Open items by state
SELECT [System.Id],[System.WorkItemType],[System.State],[System.TeamProject]
FROM WorkItems
WHERE [System.State] NOT IN ('Closed','Resolved','Removed','Done')
ORDER BY [System.State] ASC

-- Unassigned open items
SELECT [System.Id],[System.WorkItemType],[System.Title],[System.State],
       [System.TeamProject],[System.ChangedDate]
FROM WorkItems
WHERE [System.State] NOT IN ('Closed','Resolved','Removed','Done')
  AND [System.AssignedTo] = ''
ORDER BY [System.TeamProject] ASC,[System.ChangedDate] ASC

-- Aging items — 14+ days no update
SELECT [System.Id],[System.WorkItemType],[System.Title],[System.State],
       [System.TeamProject],[System.AssignedTo],[System.ChangedDate]
FROM WorkItems
WHERE [System.State] NOT IN ('Closed','Resolved','Removed','Done')
  AND [System.ChangedDate] < @Today - 14
ORDER BY [System.ChangedDate] ASC

-- Recently closed — last 7 days
SELECT [System.Id],[System.WorkItemType],[System.Title],[System.State],
       [System.TeamProject],[System.AssignedTo],[System.ChangedDate]
FROM WorkItems
WHERE [System.State] IN ('Closed','Resolved','Done')
  AND [System.ChangedDate] >= @Today - 7
ORDER BY [System.ChangedDate] DESC

---

How to recreate

The script scripts/ado/New-ADOCrossProjectDashboard.ps1 is idempotent — re-running it skips everything that already exists.

# Preview (no changes)
pwsh -NoProfile -File scripts/ado/New-ADOCrossProjectDashboard.ps1 -DryRun

# Create (first run or after a project reset)
pwsh -NoProfile -File scripts/ado/New-ADOCrossProjectDashboard.ps1

# Rebuild the dashboard from scratch (deletes and recreates the dashboard; queries are patched)
pwsh -NoProfile -File scripts/ado/New-ADOCrossProjectDashboard.ps1 -Force

Prerequisites: az login as kris@hybridsolutions.cloud, or set AZURE_DEVOPS_EXT_PAT in the environment (the script loads it automatically from kv-hcs-vault-01 / hcs-platform-ado-platform-pat if not already set).

---

Decisions and follow-on paths

Power BI — ADO Analytics OData connector

Decision: pursue as a follow-on.

ADO Analytics (_odata/v3.0-preview) exposes a rich OData feed that Power BI can query directly. It supports cross-project entity sets (WorkItems, WorkItemBoardSnapshot, etc.) and enables richer visualisations, drill-through, and date-range slicing that the built-in ADO dashboard widgets cannot provide. A Power BI report could eventually supersede this dashboard for richer analysis.

Work item: AB#412 — Evaluate ADO Analytics OData + Power BI connector for org-level work item rollups

Custom portal — platform.hybridsolutions.cloud

Decision: pursue as a follow-on.

The ADO REST API exposes the same cross-project data that powers this dashboard. Embedding a custom portal on platform.hybridsolutions.cloud (option 3 from the original work item) would provide a fully branded, filterable view without requiring ADO access for stakeholders who only need read-only visibility. The effort is non-trivial and should be evaluated against actual demand.

Work item: AB#413 — Evaluate cross-project dashboard on platform.hybridsolutions.cloud via ADO REST API

---

Maintenance

• The @Today - N macros in the aging and recently-closed queries roll automatically — no scheduled refresh is needed.
• To change queries or widget layout: edit $script:Queries or Build-Widgets in the script, then re-run with -Force.
• The shared queries are stored in Platform Engineering and are visible to any user who has access to that project.