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 Baseline

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.

Beginning of project

During the 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.

The Macro

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

The Result

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.