The Scenario
You are a PMO lead at a professional services firm. The quarterly resource review is in three days. Your firm runs all project work through ERPNext — projects, tasks, assignees, estimated hours, actual hours. Somewhere in that data is the answer to the question you get every quarter: who is over-allocated and who has capacity?
The ERPNext project reports answer this for one project at a time. You need to see it across all active projects, in one view, so you can have a real conversation with the delivery leads about reshuffling assignments before the next quarter starts.
The manual path: open ERPNext, run the Project report for project 1, note the task assignees and hours. Open project 2, repeat. You have 22 active projects. By the time you have noted down all the hours for all 22 projects, your notes are already out of date and you have not done any analysis yet.
The bad version of the quarterly review:
- Open ERPNext project list, filter to active projects
- Click into each project, note task assignees, estimated hours, actual hours
- Paste into a working Google Sheet, 22 projects' worth
- Build a pivot manually to sum actual hours by assignee
- Notice three projects are missing task assignees because nobody updated ERPNext
- Chase the delivery leads to fill in the blanks
- Walk into the resource review with a spreadsheet that is three days old and a pivot that took four hours to build.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI queries ERPNext for all active projects and their task details in one call, writes everything into the sheet, and can build the summary view on the same pass.
Open the SheetXAI sidebar and type:
List all active ERPNext projects and for each project fetch its tasks including assignee, status, estimated hours, and actual hours. Write everything into this sheet with one row per task: project name, task name, assignee, status, estimated hours, actual hours. Leave assignee blank if unassigned.
SheetXAI queries ERPNext, iterates through all active projects and their tasks, and writes one row per task. A 22-project ERPNext instance with 8 tasks per project average gives you 176 rows written and ready, with no ERPNext navigation required.
What You Get
A complete cross-project task list with one row per task:
- Project name — which project the task belongs to
- Task name — the specific task
- Assignee — who it is assigned to, blank if unassigned
- Status — Open, Working, Pending Review, or Overdue
- Estimated hours — from the ERPNext task
- Actual hours — logged hours from ERPNext Timesheets
Unassigned tasks are visible without filtering. You see the gaps immediately, not after sorting.
From this view, a pivot table on assignee gives you total actual hours per person across all projects. That is your over-allocation map.
What If the Data Is Not Quite Ready
Cross-project reporting surfaces data quality issues fast. SheetXAI handles them in the same prompt.
When you want the summary pivot built inline, not manually
You do not want to build the pivot yourself. You want a summary tab added automatically.
List all active ERPNext projects and their tasks with assignee, status, estimated hours, and actual hours — one row per task in the 'Task Detail' tab. Then create a second tab called 'By Assignee' with one row per assignee showing total estimated hours, total actual hours, and utilisation percentage (actual / estimated). Sort by actual hours descending.
When you only want tasks that are open or in progress
The review only covers active work, not completed or cancelled tasks.
List all active ERPNext projects and their tasks where task status is 'Open' or 'Working.' Include project name, task name, assignee, estimated hours, and actual hours — one row per task.
When you need to flag tasks where actual hours have already exceeded estimated
Overruns are a key signal for the resource conversation. You want those rows highlighted.
List all active ERPNext projects and tasks with assignee, estimated hours, and actual hours — one row per task. Add a column called 'Over Budget' and write "YES" for any task where actual hours exceed estimated hours. Leave it blank otherwise.
When you need the full picture in one prompt: raw task data, over-allocation flags, unassigned tasks flagged, and a summary paragraph
List all active ERPNext projects and their tasks with assignee, status, estimated hours, and actual hours — one row per task in the 'Task Detail' tab. Write "UNASSIGNED" in the assignee column for tasks with no assignee. Flag the 'Over Budget' column as "YES" where actual exceeds estimated. Then write a paragraph at the top of the 'Task Detail' tab summarizing: total active projects, total tasks, total employees with task assignments, number of unassigned tasks, and the three most over-budget tasks by hours variance.
The pattern: raw task pull, enrichment, flagging, and summary narrative are one instruction. The resource review starts from a complete picture, not a half-built spreadsheet.
Try It
Get the 7-day free trial of SheetXAI and run it before your next quarterly resource review. The ERPNext integration is included in every SheetXAI plan. For related workflows, see how to import timesheet entries into ERPNext from a project hours sheet or the ERPNext in Google Sheets overview.