Project Controls 360On my EPC projects, the monthly progress report used to take about two days. Export layouts from Primavera P6, paste them into Excel, fix the ranges, recalculate earned value, rebuild the S-curve, format it, and do it all again for the next project.
Today the same report is ready in seconds. I drop the new XER file in a folder and press refresh in Power BI. This article explains how that works, step by step, so you can build the same thing for your own projects.
Why manual monthly reporting breaks
The problem is not Excel. The problem is that every month starts from a new export, and every step in between is done by hand. That causes three things:
- Time. Two days a month, more when several projects are running.
- Errors. A wrong range, an old filter or a broken formula, and nobody notices until the meeting.
- No history. Each month is a separate file, so comparing this update with the last three is another manual job.
The fix is to stop copying data out of P6 and read P6 data directly.
Step 1: Understand what is inside an XER file
An XER file is a plain text export of the P6 database. Open one in Notepad and you will see it is made of tables. Each table starts with a line beginning %T (the table name), then a %F line with the field names, then one %R line for every record.
%T TASK %F task_id proj_id wbs_id task_code task_name ... %R 12345 678 901 A1040 Hall A mech. install ...
For monthly reporting you mainly need four tables: PROJECT (including the data date), PROJWBS (the WBS), TASK (activities, dates, float, progress) and TASKPRED (relationships). If the schedule is resource or cost loaded, TASKRSRC holds the budgets.
Step 2: Write one Power Query function that reads any table
In Power Query, load the XER as lines of text, keep the lines of the table you want, take the %F line as headers and the %R lines as rows. Turn those steps into a function that takes the file and the table name as inputs.
Now the same function reads TASK, PROJWBS or TASKPRED from any XER file. You write it once and reuse it everywhere.
Tip: read the field names from the %F line instead of relying on column positions. Different P6 versions can export columns in a different order, and header-based parsing does not break when that happens.
Step 3: Load every update from one folder
Create a folder for monthly XER files and point Power Query at the folder, not at a single file. For each file, run the function and add two columns: the project and the data date.
Take the data date from the PROJECT table (the last recalculation date) rather than from the file name. File names get typed wrong; the data date inside the XER does not.
Now all updates sit in one table, stacked by data date. That gives you history for free: trends, S-curves over time, and month-to-month comparisons.
Step 4: Clean the dates and bring in the baseline
Two details make or break the numbers:
- Current dates. For each activity, use the actual date when there is one, and the early (forecast) date when there is not. That gives one current start and one current finish for every activity.
- Baseline. Bring baseline start, finish and budget in from a baseline export, and join it to the current schedule on Activity ID. Now every activity carries both its baseline and current dates.
Step 5: Calculate everything once, with DAX
Write the calculations as measures in the model, not as formulas in a sheet. The core set is small:
- Planned Value, Earned Value and Actual Cost
- SPI and CPI
- Planned and actual percent complete
- Forecast finish and slippage against the baseline
Because every page uses the same measures, the S-curve, the KPI cards and the tables always agree with each other. If the schedule is not cost loaded, the same approach works on duration or labour units.
Step 6: Build the report pages once
Build the pages you show every month: an executive summary with SPI and forecast finish, the S-curve, a look-ahead, a variance report against the baseline. You build them once. Next month they update by themselves.
The monthly routine after this
- Export the new XER from P6.
- Save it in the folder.
- Press refresh in Power BI.
That is it. The two days of building the report are gone, and the time goes into what the report is for: explaining why progress is behind and what to do about it.
Where to go from here
Once the data is in one model, the next steps are much easier than they look: milestone delay drivers, schedule changes between updates, float erosion early warning and schedule health checks. All of them run on the same XER files.
If you would like to build this system step by step with guidance, I teach it in the Power BI for Project Controls course.