Merging or Splitting WBS Codes with Existing Resource Plan and Timesheet Data
Overview
Sometimes you are asked to merge two or more WBS codes (UniPhi projects) into one, or to split one WBS code into several. If a resource plan or timesheet hours already exist against those codes, the hours must move too.
This article sets out the order of operations for doing that without losing or double-counting hours. It covers:
Baseline (Budget) hours
Forecast hours
Timesheet actual hours
You will need UniPhi administrator access and Microsoft Excel.
Why the Order Matters
UniPhi's timesheet import updates rather than aggregates. If you import two rows for the same person, project, timesheet task and date, only the second row is kept.
You must therefore import one total per person, per week, per project, per timesheet task. The steps below make sure of that.
Summary of Steps
Export the data for every affected code.
Calculate control totals to check against at the end.
Consolidate with a pivot table (one row per person, per week, per project, per task).
Apply the new project ID and timesheet tasks.
Reconcile the totals to confirm no hours were lost or duplicated.
Import into UniPhi.
Step 1 – Export All Time for the Affected Codes
Timesheet actuals: use the Timesheet Export report. It holds all the raw timesheet data and usually has no weekly billable-hours cap applied to the output.
Go to Menu > Reporting > Reports > Timesheets and open Timesheet Export.
Filter the Activity parameter to the affected WBS codes and set a date range that covers all booked time.
Click Run, then export the output to Excel.
Baseline and forecast hours: use the Resource Plan Export report in the same way.
Go to Menu > Reporting > Reports > Resource Planning and open Resource Plan Export.
Set View to Budget for baseline hours, then run it again with Act/Fcst for forecast hours.
Export each run to Excel.
Step 2 – Calculate Control Totals in Excel
Before changing anything, calculate totals and sub-totals on the raw export. These are your control totals for Step 5.
Total hours per project (WBS code)
Hours per timesheet task (if there is more than one task)
Hours per person, per project
The more control totals you calculate, the easier it is to check your work at the end and to find any errors.
Step 3 – Consolidate the Data with a Pivot Table
In Excel, build a pivot table to combine the data. For example, to merge two WBS codes into one, use the pivot table to bring all their rows together. A pivot table reliably groups hours per week and per person.
Pivot rows: Person, Date (or Week Ending), Timesheet Task
Values: Sum of Hours
Example: Joe books 4 hrs on Project A and 4 hrs on Project B, both on 15/09. Both are being merged into Project C.
Joe – Project A – 15/09 – 4.00 hrs
Joe – Project B – 15/09 – 4.00 hrs
Joe – Project C (import row) – 15/09 – 8.00 hrs
The pivot table returns a single row for Joe of 8 hrs on 15/09. That is the row you import into Project C. If you imported the two 4-hour rows instead, UniPhi would keep only the second row and Joe would show 4 hrs, not 8.
Splitting a code? Add a column to the export that assigns each row to its new target WBS code. Then include that column in the pivot rows, so you get one total per person, per week, per new project, per task.
Step 4 – Apply the New Project ID and Timesheet Tasks
Create the new project in UniPhi (Activity Portfolio > +) and note its Activity ID.
In the consolidated Excel data, replace the old project IDs with the new Activity ID.
Update the timesheet task values so they match the task list assigned to the new project. You can check or set the task list for each activity under Menu > Configuration > Timesheets > Admin.
Step 5 – Reconcile Against the Control Totals
Recalculate the same totals on the consolidated data and compare them to Step 2. Every figure should match, with zero variance, before you import anything:
Total hours (all affected codes)
Hours per timesheet task
Hours per person
Baseline (Budget) hours
Forecast hours
If there is a variance, go back to the pivot table and look for missing filters, blank task values or rows mapped to the wrong project ID.
Step 6 – Import into UniPhi
When everything reconciles, import the data.
Resource Plan (Baseline and Forecast)
Go to Resources > Resource Planning and click the Import Resource Budget or Forecast Hours icon.
Set Import to Budget or Forecast, set Unit to Hours, and paste the Excel data.
Timesheet Actuals
Go to Timesheets > Week View and click Upload.
Paste the consolidated Excel data and import it.
Afterwards, check Menu > Configuration > Timesheets > Import for any import errors. Then run Timesheet Export and Resource Plan Export on the new code and confirm the totals match Step 2.
Quick Checklist
Timesheet Export and Resource Plan Export (Budget and Act/Fcst) exported for all affected codes
Control totals calculated per project, per task and per person
Pivot table gives one row per person, per week, per project, per task
New project created; project IDs and timesheet tasks updated
Totals reconciled with zero variance
Data imported; no import errors; post-import reports match
Comments