The Scenario
New features are being allocated for Q2 and the engineering director needs to know who is already overloaded before assigning anything. Open issues are spread across three Jira projects — WEB, API, INFRA — and there is no combined view that shows per-developer counts without clicking into each project separately. She has twelve developers and fifteen minutes before the allocation call starts.
The bad version:
- Open Jira, navigate to WEB, filter by assignee, click the first developer.
- Note the open issue count, switch to Excel, type it in.
- Repeat for API and INFRA.
- Multiply that sequence by twelve developers.
- Realize two developers appear under different display names in different projects and your count for "Alex" is wrong.
Getting a clean workload snapshot across three projects for twelve developers is a thirty-minute exercise before you can even begin the conversation about who gets what.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook. It reads the workbook and through the Jira integration queries all three projects and groups the results by assignee in one pass.
For each developer email in column A of my Team Capacity sheet, fetch their open Jira issue count across all projects and write the count into column B.
What You Get
- One row per developer with their total open issue count written into column B.
- Counts reflect all projects, not just the ones you named individually.
- If a developer has no open issues, the count is 0 rather than a blank.
- The workbook is ready to sort by count and share with the director before the call.
What If the Data Is Not Quite Ready
You don't have developer emails — you want a query across projects grouped by assignee display name
No email list exists. You just want the open issue count per assignee across WEB, API, and INFRA.
Search Jira for all open issues in projects WEB, API, and INFRA. Group by assignee and write a summary table into my sheet with columns: assignee name, project, open issue count.
You want separate columns for each project rather than a combined total
The director wants to see where each person's load is concentrated.
Search Jira for all open issues in projects WEB, API, and INFRA. For each unique assignee, write one row with columns: assignee name, WEB count, API count, INFRA count, total.
Some developers have different display name variations across projects
Two people appear as "Alex Smith" in WEB and "A. Smith" in INFRA. You want them merged under their email.
Search Jira for all open issues in projects WEB, API, and INFRA. Group by assignee email rather than display name. Write a summary table with columns: email, display name, project, open issue count.
Kill chain: fetch workload, flag anyone over 15 issues, sort descending, and highlight in Excel
Search Jira for all open issues across projects WEB, API, and INFRA. Group by assignee and write one row per person with their total open issue count. Sort by total descending so the most overloaded people appear at the top. In column C, flag anyone with more than 15 open issues with 'At capacity'.
One prompt builds the sorted, flagged capacity view — ready to share on the allocation call without additional formatting.
Try It
Get the 7-day free trial of SheetXAI and open your team capacity Excel workbook before the next resource planning session, then ask it to pull open issue counts per developer across all active Jira projects. See also: how to build a cross-project executive issue report and how to pull sprint velocity into a workbook.