In short, you can’t ;-). MS Project is good at showing all sorts of info on your project for any given point in time. It does not, however, contain historic data to calculate trends. But there is a workaround. In each reporting cycle (say every week), save your project plan to a new file. Then use Excel and a macro to read all past project plans and chart the trend. Use pivot tables to drill down to deliverable or sub-projects. Here is how to chart work compared to baseline work over time.
The following picture shows you a trend of work vs. baseline work of two deliverables.
To get a trend like this, you need
- an Excel file that holds the configuration and trend data,
- a number of historic project files,
- a macro that pulls everything together and
- a baseline in the original plan.
The Excel file
To run the trend analysis, simply download the Excel file and the macro (see below). Configure the file with these steps.
In column A enter the names of your project files, one per line. In column B, choose to import this plan with “yes” or “no”. Initially, all lines should obviously be set to “yes”. In columns C thru E, tag each plan with something meaningful (these tags make the timeline in your pivot table). Lastly, enter unique IDs (UID) of tasks you would like to be able to select in the pivot charts in column F and corresponding names in column G. Using this subset of all available UIDs makes selection in the pivot table far easier (because the selection lists is not too crowded).
The macro will sift through your project files, extract all relevant data from summary tasks and dump it into the “Source” tab. Why only summary tasks? Because summary tasks are the only “constant” over time. Consider the following two snapshots of a project.
Taking the baseline at the beginning of the project rolled work up to the summary level (in this case for a deliverable). During the course of the project, things change – tasks disappear (e.g. Task4) and new tasks show up (e.g. Task6 and Task7). Hence measuring work for this deliverable makes only sense on the summary level.
Once the Excel file is configured, run the macro from Excel. The macro will sift through your project files and populate the tab “Source” in the Excel file. Previously imported data from the same project file is removed before any subsequent import.
Option Explicit Sub swaUpdateTrend() Dim swaWorkbook, swaFilename As String Dim i, j, runto, line As Integer Dim appProj As MSProject.Application Dim aProg As MSProject.Project Dim ts As Tasks Dim t As Task Dim StartTime As Double Dim rng1 As Range Dim swaSheet As Integer Dim swaPivot As PivotTable StartTime = Timer swaWorkbook = ActiveWorkbook.Name Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayStatusBar = True runto = Workbooks(swaWorkbook).Worksheets("Config").Range("A65536").End(xlUp).Row swaSheet = ActiveSheet.Index For i = 2 To runto Step 1 swaFilename = Workbooks(swaWorkbook).Worksheets("Config").Cells(i, 1).Value If Workbooks(swaWorkbook).Worksheets("Config").Cells(i, 2).Value = "yes" Then Debug.Print i & " Updating plan " & swaFilename ' remove all present entries in "Source" Set rng1 = Workbooks(swaWorkbook).Worksheets("Source").Range("H:H").Find(swaFilename, , xlValues, xlWhole) If Not rng1 Is Nothing Then ' the plan has been read before, remove all lines ' Change the activesheet for autofilter to work ActiveWorkbook.Sheets("Source").Activate ' ugly, but works With ActiveSheet .AutoFilterMode = False With Range("H1", Range("H" & Rows.Count).End(xlUp)) .AutoFilter 1, swaFilename On Error Resume Next .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete End With .AutoFilterMode = False End With Sheets(swaSheet).Activate End If ' add the plan to the end of tab "source" line = Workbooks(swaWorkbook).Worksheets("Source").Range("A65536").End(xlUp).Row Application.StatusBar = "Read plan " + swaFilename ' open Projektplan, add entries to "Source" Set appProj = CreateObject("Msproject.Application") appProj.FileOpen swaFilename, ReadOnly:=True ' crude, no error checking Set aProg = appProj.ActiveProject appProj.Visible = False ' was True ' dump work, baseline work and other stuff for each summary task Set ts = aProg.Tasks For Each t In ts 'these if statements step through all tasks in the project If Not (t Is Nothing) Then 'this handles blank line in the file If t.Summary = True Then 'this handles summary tasks only line = line + 1 With Workbooks(swaWorkbook).Worksheets("Source") .Cells(line, 1) = t.UniqueID .Cells(line, 2) = t.Name .Cells(line, 3) = t.Work / 60 / 8 .Cells(line, 4) = Format(t.Finish, "dd.mm.yyyy") .Cells(line, 5) = t.BaselineWork / 60 / 8 ' assuming 8 hrs per day .Cells(line, 6) = Format(t.BaselineFinish, "dd.mm.yyyy") .Cells(line, 7) = Workbooks(swaWorkbook).Worksheets("Config").Cells(i, 3).Value ' Status Week .Cells(line, 8) = swaFilename .Cells(line, 9) = Workbooks(swaWorkbook).Worksheets("Config").Cells(i, 4).Value ' Status Month .Cells(line, 10) = Workbooks(swaWorkbook).Worksheets("Config").Cells(i, 5).Value ' Status Year .Cells(line, 11) = "Yes" ' IsSummary .Cells(line, 12) = t.Flag10 ' swaImportant .Cells(line, 13) = t.Text27 ' AP or Deliverable .Cells(line, 14) = t.Text28 ' (Sub-)Project .Cells(line, 15).Formula = "=VLOOKUP(A" + Format(line) + ",uid2name,2,FALSE)" End With End If End If Next t Application.StatusBar = "" appProj.FileClose pjDoNotSave appProj.Quit Set appProj = Nothing End If Next i ActiveWorkbook.Sheets("Source").UsedRange.AutoFilter Field:=1 ' in case we have added early plans to the end of the list, sort Set swaPivot = Sheets("Trend by Month").PivotTables("PivotTable1") swaPivot.PivotFields("Status Month").AutoSort xlAscending, "Status Month" Set swaPivot = Sheets("Trend by Week").PivotTables("PivotTable1") swaPivot.PivotFields("Status Week").AutoSort xlAscending, "Status Week" ' for all charts in workbook and all series change line thickness to something that you can see ... For i = 1 To ActiveWorkbook.Charts.Count With ActiveWorkbook.Charts(i) For j = 1 To .SeriesCollection.Count .SeriesCollection(j).Border.Weight = xlThick Next j End With Next i Debug.Print Format(Timer - StartTime, "00.00") & " seconds" Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.StatusBar = "" End Sub
After the macro has been completed (check Excel’s status bar for the progress), simply go to the other tabs and refresh the pivot tables.
You can now see the development of work and baseline work over time. In a future post, I’ll describe how to perform milestone trend analysis using pretty much the same technique as outlined above.