Conference Catch Up: Critical Path Drag

Catching up on some of the discussions from the last conference I attended, the Critical Path Management conference.  Had a good hallway conversation about the concept of Critical Path Drag.

Not familiar with the concept of drag?  If you’re at all interested in the world of scheduling, it’s well worth the time to check out this article in Wikipedia.

Read the article and curious how to calculate this in Microsoft Project desktop?  Here’s a helpful macro courtesy of Tom Boyle.

Conference Catch Up: Critical Path Drag

Setting Default Desktop Scheduling Options

One of the takeaways from the recent Construction CPM Scheduling Conference in New Orleans last week (other than a couple of hurricanes on Bourbon street) was an acknowledgement that while Microsoft Project does indeed enable best practice scheduling, by default many of the required features are turned off.  This causes some frustration on behalf of the scheduler who must hunt deep into the Options panel to identify and select the appropriate options for each new project.

To assist in this endeavor, I wrote this little macro.  It basically goes through the options and sets them to optimize the scheduling interface for detailed construction scheduling (and therefore may have to be tweaked to support project scheduling in other domains).  In the past, I’ve triggered this macro whenever I run any other scripts on the schedule, i.e. I’ve written scripts to facilitate the update process….which call this routine up before going into collecting the update data.

Did I forget a key setting?  Let me know and I’ll update it accordingly.

Sub ApplyDefaultSettings()

    Dim Continue As String
    
    Continue = MsgBox("This macro will now apply the standard PMO settings to the project schedule.", vbOKCancel, "Confirm")
    
    If Continue = vbOK Then
        
        
        '1 - Project Settings
        With Application.ActiveProject
            .AutoTrack = True 'Sets the task status updates resource status setting
            .MoveCompleted = True 'Move completed work prior to the status date.
            .MoveRemaining = True 'Move incomplete work after the status date
            .SpreadPercentCompleteToStatusDate = True 'spread %Complete to the status date
            .NewTasksCreatedAsManual = False 'Turn off manual tasks
            .DisplayProjectSummaryTask = True 'Display project summary task
            .AutoLinkTasks = False 'Do not automatically link tasks when added to the schedule
            .MultipleCriticalPaths = True 'Calculate multiple critical paths
        End With
        
        '2 - Display Settings
        Application.NewTasksStartOn (pjProjectDate) 'Sets new tasks to default to the Project Start Date
        Application.DisplayEntryBar = True 'Displays the Entry Bar
        Application.Use3DLook = False 'Turns off 3D which used to cause printing issues
        
        '3 - Gantt Chart Settings
        GridlinesEditEx Item:=12, NormalType:=3 'Set the Status Date line
        GridlinesEditEx Item:=12, NormalColor:=192
        GridlinesEditEx Item:=4, NormalType:=0 'Turn off the Current Date line
        GridlinesEditEx Item:=0, Interval:=3 'Set dotted lines on every third Gantt row
        GridlinesEditEx Item:=0, IntervalType:=3
        GridlinesEditEx Item:=0, IntervalColor:=8355711
        GridlinesEditEx Item:=13, NormalType:=3 'Set dotted lines on every vertical top tier column
        GridlinesEditEx Item:=13, NormalColor:=8355711
        
    End If

End Sub

 

Setting Default Desktop Scheduling Options

Adding Tasks to the Timeline with VBA

Toodling around in MPP 2013 today and came up with this quick code.  It runs through each task and adds the ones flagged in the Flag1 field to the timeline.  Note this is barely tested – and I would assume it needs to be run on a view where the timeline actually appears.

Sub AddTasktoTimeline()

    Dim T As Task
    For Each T In ActiveProject.Tasks
        If T.Flag1 = True Then
            SelectRow Row:=T.ID, RowRelative:=False
            TaskOnTimeline
        Else
            SelectRow Row:=T.ID, RowRelative:=False
            TaskOnTimeline Remove:=True
        End If
    Next T

End Sub

Adding Tasks to the Timeline with VBA

Importing Files to Project Online with VBA

In yesterday’s post, I talked about how to run a script to export all of our Microsoft Project schedules into XML files.  In this post, I’ll provide the script to import these into a new instance of Project Server – either on-prem or online.  Note that this script will not import custom fields – but could be easily adapted to capture and transfer custom fields.

Note also that there are other third party tools that do much of the same thing, but better.

Caveats aside, if you followed yesterday’s post, you should now have a directory called C:\Exports that contains all of the exported project schedules in an XML format.

image

You should also have a Microsoft Project schedule containing a list of all of the projects you wish to import.

image

Add the following code…

Sub ImportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="C:\Exports\" & fName & ".mpp", ReadOnly:=False, FormatID:="MSProject.MPP"
        Application.FileSaveAs Name:="<>\" & fName, FormatID:=""
        Application.FileCloseEx pjSave, True, False
    Next T

End Sub

…and run to import the files.  You will probably need to click through the prompts as they appear.

Pair this with a utility to publish all of the projects like the one Alex Burton wrote here and you’ve now moved your projects from one environment to another.

Importing Files to Project Online with VBA

Exporting MPP Files with VBA

Well, I didn’t deliberately intend to get into a VBA rut, but periodically, I like to clean out some of the half written posts I have lying around, and it turns out that I apparently left a couple VBA posts started and not yet finished.  My resurgent interest in VBA of late has also been driven by my efforts to get up to speed in Project Online, and the realization that I can easily use my traditional VBA skills to interact with the Project client, and quickly upload data to my Project Online tenant.

Hence, I wrote this quick VBA script to export all of the MPP files in an existing Project Server instance, and then import the files into another Project Server instance.  And here’s the beauty of VBA and the Project client interface……the code is essentially version agnostic….meaning I could use it to do a dump of all of my existing project schedules (to prep for export or to perform a backup) from Project Server 2010 and then upload them using a different code into an online version of Project Server 2013.

I’ll also note that there are much more robust partner solutions that pretty much do the same thing – but better.  This code is crude, virtually untested, and has only been used in development environments to generate demos.

Identify the Target List

The first step  is to generate a list of the projects you would like to export.  The easiest way for me to do this was simply to grab the list of projects from the Project Center.

image

I simply exported the list to Excel, and then copied the results into a Microsoft Project file.  I could have simply run the macro from Excel I suppose, but its simpler for me to just use Project.

image

Note that I would include custom fields as custom fields in the MPP file if I were planning to transfer them over.  In this case, I just want the raw project data.

Add the macro…

Sub ExportProjects()

    Dim T As Task
    Dim fName As String

    For Each T In ActiveProject.Tasks
        fName = T.Name
        Application.FileOpenEx Name:="<>\" & fName, IgnoreReadOnlyRecommended:=True
        Application.FileSaveAs Name:="C:\Exports\" & fName, FormatID:="MSProject.XML"
        Application.FileCloseEx pjDoNotSave, CheckIn:=True
    Next T

End Sub

…and create a directory on the C drive called “Exports.”  We’ll export the project files as XML files into this directory.  Why XML?  It tends to clean up much of the junk that ends up getting pushed into these files over time.  It also removes user customizations to views – which I consider a good thing to do periodically.  Change the export format to “MPP” to export as an MPP file.  Note however that this increases the chance of the import not working properly.

Run the macro.  You may have to click through a couple times if a project opens and throws out a prompt to accept updates, etc.

You should end up with a directory full of XML files.

image

Next up….the code to take that directory and load it to a new Project Server instance.

Exporting MPP Files with VBA

Flagging Project Owner Changes with OData and VBA

This question comes up in the forums pretty frequently, and in fact, I’d actually spec’d out a similar procedure for on-prem Project Server installs which involved hitting the Reporting Database to figure out the Project Owner.  I am not sure how useful this code really is, but I was curious about how hard it would be to consume OData values with VBA and then manipulate Project desktop data with the results, and hence spent a couple cycles figuring it out.

In this case, as usual, I owe credit to someone else who did the heavy lifting.  In doing my research, I came across Marcelo Ruiz’s blog where he pretty much did all of the work.  I’d encourage you to check them out:

  1. VBA and OData (Part I)
  2. VBA and OData (Part II)
  3. VBA and OData (Part III)

I basically took his work and repurposed it to work with Project.

The Scenario

Specific fields are available with Project Server but not available within the Project Client.  Most notably, these fields include the Enterprise Project Type field or the Project Owner.  There are a number of scenarios that could be enable through making these fields visible in the Project Client.  For example, I would like to flag the project when the Project Owner has changed, as many organizations forget to change the Status Manager field – and as a result the task updates all flow back to the original Project Owner.

The Solution

In Project Online, we can’t consume the database data, and must instead consume the OData feed from Project Server.

Hence the code below.  It pings the OData feed and returns the value of the Project Owner, then performs two actions:

  1. Flags tasks where the Status Manager does not equal the Project Owner.
  2. Pops up a message box to let the user know the Project Owner has changed.  (Note this pops up the first time the macro is run on a project, but that would be easy enough to fix.)
Option Explicit

'Copied from Marcelo Ruiz's blog:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx

' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime

Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2

'Given a URL, reads an OData feed or entry into an XML document.
'Copied from Marcelo Ruiz's blog:
'http://blogs.msdn.com/b/marcelolr/archive/2010/02/16/consuming-odata-with-office-vba-part-i.aspx
Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
    Dim objXmlHttp As MSXML2.XMLHTTP60
    Dim objResult As MSXML2.DOMDocument60
    Dim strText As String

    'Make a request for the URL.
    'AL - Note I tweaked the XMLHTTP TO XMLHTTP60
    Set objXmlHttp = New MSXML2.XMLHTTP60
    objXmlHttp.Open "GET", strUrl, False
    objXmlHttp.send

    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
    End If

    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing

    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
    End If

    Set ODataReadUrl = objResult
End Function

Public Sub CheckProjectOwner()

    Dim PWAURL As String
    Dim ODataURL As String
    Dim objDocument As MSXML2.DOMDocument60
    Dim GetParam As String
    Dim pName As String
    Dim StartTag As String
    Dim FinTag As String
    Dim txtStart As String
    Dim txtFinish As String
    Dim OValue As String
    Dim T As Task

    'Configure the following parameters
    PWAURL = "https://ProjectOnline.sharepoint.com/sites/pwa"
    GetParam = "ProjectOwnerName"

    'Calculate the appropriate strings
    ODataURL = PWAURL & "/_api/ProjectData/"
    pName = ActiveProject.ProjectSummaryTask.Name
    StartTag = "<d:" & GetParam & ">"
    FinTag = "</d:" & GetParam & ">"

    'Ping OData with the query
    Set objDocument = ODataReadUrl(ODataURL & "Projects()?$filter=ProjectName eq '" _
    & pName & "'&$select=" & GetParam)

    'Parse the result set to look for the value
    txtStart = InStr(1, objDocument.XML, StartTag, vbTextCompare) + Len(StartTag)
    txtFinish = InStr(1, objDocument.XML, FinTag, vbTextCompare)
    OValue = Mid(objDocument.XML, txtStart, txtFinish - txtStart)

    Debug.Print OValue

    'Do something with the returned value
    'This changes a flag field if the value doesn't match the status manager

    For Each T In ActiveProject.Tasks
        T.Text1 = OValue
        If T.StatusManagerName = OValue Then
            T.Flag1 = True
        Else
            T.Flag1 = False
        End If
    Next T

    'This compares it with another field to launch a dialog box if it's been changed

    If ActiveProject.ProjectSummaryTask.Text1 <> OValue Then
        MsgBox "The Project Owner has been changed.  You should change the status manager.", vbOKOnly, "Reminder"
    End If

    'Store the current status manager for the next iteration.
    ActiveProject.ProjectSummaryTask.Text1 = OValue

End Sub
Flagging Project Owner Changes with OData and VBA

Task Paths in Project 2013

Here’s probably one of the most interesting new features (well, other than reporting) added to the new Microsoft Project desktop client – the Task Path feature.  This feature allows you to easily review a schedule to assess the impact of a single task.

image

First off, I point out that fellow MVP Nenad Trajkovski has already posted on this topic.  I wanted to add to his post, and perhaps provide some additional information on how to consume this new feature in the updated Project VBA object model.

In the screenshot above, you see that I have selected a single task.  Each of the driving predecessors of the task are highlighted in orange.  In turn, the driven successors of the task are highlighted in purple.

This is all controlled through the Task Path interface, now found on the Gantt Chart Format tab.

image

Opening that dropdown, you see the options for the task path display.  Note that these options are not mutually exclusive, i.e. I can actually select all of the options to add informational depth to my Gantt Chart display.

image

As always, just mouse over the bar in the Gantt Chart to see what the color represents – or doubleclick on the Gantt Chart to see the legend.

image

So that’s great, but what about if I want to filter on only those tasks that are predecessors and successors?  There’s no field affiliated with that setting, so how can I determine which tasks are relevant?

Enter VBA.  The new setting may be consumed via the VBA object model using:

  • Task.PathDrivenSuccessor
  • Task.PathDrivingPredecessor
  • Task.PathPredecessor
  • Task.PathSuccessor

For example, the following VBA code will label each task within the schedule as a Predecessor or Successor in the Text1 field, and then filter the view on only those tasks which have been flagged.

Sub DefinePath()

    Dim T As Task
    Dim I As Integer

    'Clear the existing filter
    Application.FilterClear

    'Capture the selected task
    I = Application.ActiveCell.Task.ID

    'Flag the Predecessors and Successors
    For Each T In ActiveProject.Tasks
        T.Text1 = ""
        If T.ID = I Then
            T.Text1 = "Selected Task"
        End If
        If T.PathPredecessor = True Then
            T.Text1 = "Predecessor"
        End If
        If T.PathSuccessor = True Then
            T.Text1 = "Successor"
        End If
    Next T

    'Apply a filter on the flagged tasks
    SetAutoFilter FieldName:="Text1", FilterType:=pjAutoFilterIn, _
    Criteria1:="Predecessor" & Chr$(9) & "Successor" & Chr$(9) & "Selected Task"

End Sub

Here’s what the results look like…

image

Task Paths in Project 2013

Project VBA and Azure: Better Together

I know what you’re all thinking.  Project 2010 is fantastic – and we can’t wait till we get to see the next version.  The only problem is that there’s this one critical function that Microsoft really needs to bake into the product.  You see, they really need to add a button that will automatically translate my schedule into Romanian.

image

Now, I don’t think we’ll see that quite baked into the product in the near future, but in this post, I’ll show you how to quickly add a bit of VBA that ties into the Microsoft Translator API to automatically translate your project schedule into Romanian – or 36 other supported languages ranging from Hmong Daw to Haitian Creole.  (Although, it still doesn’t support Mongol unfortunately.  I guess I’ll have to wait a bit for that.)

image

I know that I am a bit stuck in a VBA rut, but every now and then, I run into a trick that’s so cool, it’s worth blogging about.  In this case, I can’t really take much credit.  JimS, one of the users on the forums came up with this code to automatically translate task names from one language to another.  (Although as far as I can tell, the code originally derived from a post by Travis McGee back in 2010.)

image

The magic is done through the use of a bit of VBA code – that then calls the Microsoft Translation API, and drops the results into the Text1 field.

image

I had to update the code a bit for this blog as it appears the original code was using an older authentication form that has now been deprecated.  Hence, I would definitely not recommend using the older Bing AppID authentication method  that is floating around on the Web and instead implement the code below which authenticates against the Azure marketplace using an authorization token.

Registering with the Azure Marketplace

The first step though is to get two parameters: the ClientID and the ClientSecret from your free account on the Azure Marketplace.  Check here for guidance on getting those.  These parameters link the application to your user account – so for example, you could use the free version which is limited to 2 million characters/month, or pay a bit more to get more volume.

The VBA

Hat tip to this post for the guidance on adding the functionality to get the Authorization Token.

You’ll need to enter your personal Client ID and the Client Secret into the code to get it to function.  All the configuration parameters are grouped in the first function.

'Sources of info:
'http://blogs.msdn.com/b/translation/
'http://msdn.microsoft.com/en-us/library/hh454950.aspx
'https://datamarket.azure.com/dataset/1899a118-d202-492c-aa16-ba21c33c06cb
'http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/e4c149c4-fefb-48fd-8990-db6a8f0f9045

'Set the Following References :
'Microsoft XML, v6.0

Sub TranslateTaskNames()

'This macro will translate task names and drop the new values into the Text1 field.

'Set the authorization token parameters here. Macro won't work without the appropriate values.
'See here for instructions on getting set up with the appropriate codes.
Dim ClientID As String
Dim ClientSecret As String

ClientID = "ENTER YOUR CLIENT ID HERE"
ClientSecret = "ENTER YOUR CLIENT SECRET HERE"
'Refer to this page for language codes: http://msdn.microsoft.com/en-us/library/hh456380.aspx
Dim LanguageFrom As String
Dim LanguageTo As String
LanguageFrom = "" 'Set the source language. Leave blank to auto-detect.
LanguageTo = "FR"

'Give the user the option to change the target language
LanguageTo = InputBox("Enter the target language code:", "Target", LanguageTo)

'The next section performs the translation

Dim T As Task
Dim tName As String

For Each T In ActiveProject.Tasks
    tName = MicrosoftTranslate(T.Name, LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate the Task Name
    tName = Mid(tName, 2, Len(tName) - 2) 'Truncate quotation marks from result set
    T.Text1 = tName
Next T

'The next section displays a confirmation box

Dim tCompleted As String
tCompleted = MicrosoftTranslate("Translation Completed", LanguageFrom, LanguageTo, ClientID, ClientSecret) 'Translate confirmation message
tCompleted = Mid(tCompleted, 2, Len(tCompleted) - 2) 'Truncate quotation marks from the result set

MsgBox "Translation Completed / " & tCompleted, vbOKOnly, "Done"

End Sub

Function MicrosoftTranslate(sText As String, LanguageFrom As String, LanguageTo As String, ClientID As String, ClientSecret As String) As String

'This function calls the Microsoft Translate API

Dim ID As String
Dim sURL As String
Dim oH As MSXML2.XMLHTTP
Dim sToken As String

   ID = ""    ' Bing appID deprecated in Dec 2011 in favour of Access Token. Use nothing for legacy appID parameter
   sURL = "http://api.microsofttranslator.com/V2/Ajax.svc/Translate?oncomplete=&appId=" & ID _
            & "&from=" & LanguageFrom & "&to=" & LanguageTo & "&text=" & sText
   sToken = GetAccessToken(ClientID, ClientSecret)
   Set oH = CreateObject("MSXML2.XMLHTTP")
   oH.Open "POST", sURL, False
   oH.setRequestHeader "Authorization", "Bearer " & sToken
   oH.send
   MicrosoftTranslate = oH.responseText
   Set oH = Nothing
End Function

Function GetAccessToken(ClientID As String, ClientSecret As String) As String

'This function authenticates against the Azure marketplace to ensure the user is authorized to use the app.

Dim webRequest As MSXML2.ServerXMLHTTP
Set webRequest = CreateObject("MSXML2.ServerXMLHTTP")
Dim URI As String
URI = "https://datamarket.accesscontrol.windows.net/v2/OAuth2-13"

Dim sRequest As String
sRequest = "grant_type=client_credentials" & _
      "&client_id=" & ClientID & _
      "&client_secret=" & URLEncode(ClientSecret) & _
      "&scope=http://api.microsofttranslator.com"

Dim mtToken As String
Set webRequest = New ServerXMLHTTP
webRequest.Open "POST", URI, False
webRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
webRequest.send sRequest
mtToken = webRequest.responseText
Set webRequest = Nothing

'Debug.Print mtToken

Dim arr As Variant, header As String
header = "{""access_token"":"""
arr = Split(mtToken, ",")
GetAccessToken = Mid$(arr(0), Len(header) + 1, Len(arr(0)) - Len(header) - 1)
End Function

Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String

'This function modifies the text to properly work in a URL.

Dim StringLen As Long
 StringLen = Len(StringVal)
 If StringLen > 0 Then
   ReDim result(StringLen) As String
   Dim i As Long, CharCode As Integer
   Dim Char As String, Space As String
   If SpaceAsPlus Then Space = "+" Else Space = "%20"
   For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
      result(i) = Char
      Case 32
      result(i) = Space
      Case 0 To 15
      result(i) = "%0" & Hex(CharCode)
      Case Else
      result(i) = "%" & Hex(CharCode)
      End Select
   Next i
   URLEncode = Join(result, "")
End If

End Function

Set the source and/or target language per the options here.

Here’s the same exercise from English to Spanish.  I don’t make any claims about the quality of the translation, but overall, it doesn’t seem like a bad start.

image

Kind of wish I’d had something like this back in the late 90s when I had to figure out a system to translate Fluke testing reports into English.

image

Project VBA and Azure: Better Together

Revisiting Project Trend Analysis and Project Change Logs

The question always arises as to how to best take a snapshot of project data, and use that in timephased reports or change logs.  In fact, by my count, this is the third time I’ve addressed this topic in this blog:

  1. Using External Content Types and SharePoint lists.
  2. Using VBA to store data in spare baseline fields.
  3. Using VBA to store data in a custom SQL table (keep reading).

In this blog post, I’d like to take a different approach than in the past and show how to use VBA to post snapshots of project data to a SQL database.  The value of storing the data in a custom SQL table of course is that the data is available on the enterprise level, subject to enterprise retention policies, and available for use with enterprise reporting solutions.

Now, I could always use automatically generated queries.  In fact, it would be quite simple to set up a timed job that runs within SQL every Friday evening and takes a snapshot of project data.  As I’ve mentioned before, I am not a big fan of such automated queries – generally because I know a lot of project managers who like to catch up on work over the weekend – or who often will submit the numbers only to immediately realize that they forgot to totally update their schedule, make adjustments and then resubmit.  Running a timed snapshot process may not work well for folks like this.

My preference is for the project manager to manually trigger that snapshot in time as part of the routine status reporting cycle.  You could even incorporate some workflow or alerts around the data when it’s submitted as part of the report.

Creating the Table

In this case, I’ll just use some code that Calin, one of our developers created for me to support some EVM reporting.  This query will generate a table within a SQL database.   Generally, you would add this table to a custom database.

CREATE TABLE [dbo].[ProjectStatusSnapshots](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NOT NULL,
      [ProjectName] [nvarchar](500) NOT NULL,
      [ProjectUID] [uniqueidentifier] NULL,
      [ProjectStatusDate] [datetime] NULL,
      [ProjectACWP] [money] NULL,
      [ProjectBCWP] [money] NULL,
      [ProjectBCWS] [money] NULL,
      [ProjectEAC] [money] NULL,
      [ProjectCPI] [money] NULL,
      [ProjectSPI] [money] NULL,
      [ProjectTCPI] [money] NULL,
      [ProjectCost] [money] NULL,
    [ProjectActualCost]  [money] NULL,
      [ProjectBaseline0Cost] [money] NULL 
CONSTRAINT [PK_ProjectStatusSnapshots] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Modify the fields to fit your needs.  You might also consider creating two tables, one for project header information, and one for task information.

Confirm that we have a new table in SQL.

image

Adding the VBA

The VBA then is relatively simple.  We just need to create a bit of code to generate the database connections and then run a SQL update query to insert the right data into the database table.

Note that I am grabbing the ProjectUID as well.  This will facilitate reports that will join the trend analysis data with the default Project Server reporting database.

As I mentioned above, you may want to consider adding code to capture task level metrics for trend analysis.  That would pretty much be the same concept.  Just make sure to grab a unique identifier for the tasks to use in generating reports.  Using that technique and a field to capture the current user, you could effectively implement a change log – especially if you embed the VBA code in the Project OnSave activity.

Sub UpdateSQL()

    'This macro will log specific field data into a customized SQL database.

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Dim SQLInstance As String
    Dim SQLDatabase As String

    'Set the connection parameters here

    SQLInstance = "Demo\Demo" 'Enter the name of the SQL instance
    SQLDatabase = "zzz_TrendAnalysis" 'Enter the name of the SQL database

    Conn.ConnectionString = "Provider=sqloledb;" _
    & "Data Source=" & SQLInstance & ";" _
    & "Initial Catalog=" & SQLDatabase & ";" _
    & "Integrated Security=SSPI;"

    If Conn.State = 1 Then 'To avoid errors where the connection was opened and not closed
        Conn.Close
    End If

    'Open the connection and assign the data to the table.

    Conn.Open

    With ActiveProject.ProjectSummaryTask

    Conn.Execute ("INSERT INTO " & SQLDatabase & ".dbo.ProjectStatusSnapshots([date], ProjectUID, ProjectName, " _
    & "ProjectStatusDate, ProjectACWP, ProjectBCWP, ProjectBCWS, ProjectEAC, ProjectCPI, " _
    & "ProjectSPI, ProjectTCPI, ProjectCost, ProjectActualCost, ProjectBaseline0Cost) " _
    & "VALUES(GetDate(), " _
    & Chr(39) & ActiveProject.GetServerProjectGuid & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.Name & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.StatusDate & Chr(39) _
    & Chr(44) & .ACWP _
    & Chr(44) & .BCWP _
    & Chr(44) & .BCWS _
    & Chr(44) & .EAC _
    & Chr(44) & .CPI _
    & Chr(44) & .SPI _
    & Chr(44) & .TCPI _
    & Chr(44) & .Cost _
    & Chr(44) & .ActualCost _
    & Chr(44) & .BaselineCost & ")")

    End With

    Conn.Close

    MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation"

End Sub

Run the VBA a couple of times to confirm it’s generating data.

image

Once you’ve validated the VBA, consider adding it as a button to a customized ribbon toolbar.  This will give your users the ability to trigger the macro directly from the main Microsoft Project interface.

Retrieving the Data

From there, it’s just a simple question of retrieving the data.  As I mentioned above, I fully expect the PM to potentially create multiple snapshots at each status period, I may only want to grab the “final” one that was posted each day.  To do that, we just need to modify a basic SQL query.

Here’s an example:

SELECT dbo.ProjectStatusSnapshots.Id, dbo.ProjectStatusSnapshots.Date, dbo.ProjectStatusSnapshots.ProjectName, dbo.ProjectStatusSnapshots.ProjectUID, dbo.ProjectStatusSnapshots.ProjectStatusDate, dbo.ProjectStatusSnapshots.ProjectACWP, dbo.ProjectStatusSnapshots.ProjectBCWP, dbo.ProjectStatusSnapshots.ProjectBCWS, dbo.ProjectStatusSnapshots.ProjectEAC, dbo.ProjectStatusSnapshots.ProjectCPI, dbo.ProjectStatusSnapshots.ProjectSPI, dbo.ProjectStatusSnapshots.ProjectTCPI, dbo.ProjectStatusSnapshots.ProjectCost, dbo.ProjectStatusSnapshots.ProjectActualCost, dbo.ProjectStatusSnapshots.ProjectBaseline0Cost, DailyRecord.LastRecord FROM dbo.ProjectStatusSnapshots RIGHT OUTER JOIN (SELECT MAX(Id) AS LastRecord FROM dbo.ProjectStatusSnapshots AS ProjectStatusSnapshots_1 GROUP BY ProjectUID, CAST(Date AS Date)) AS DailyRecord ON dbo.ProjectStatusSnapshots.Id = DailyRecord.LastRecord

Run this query to generate a table of the last entry posted per project on any given day.  You could then use the ProjectUID field to join this date to live data in the Project Server reporting database.

Revisiting Project Trend Analysis and Project Change Logs

Storing Custom Timescaled Data with VBA

Regular readers of this blog may recall a series of posts from January where I discussed how to use VBA to store timescaled BCWP values in the Baseline10Cost field.  At the time, I’d mentioned that the same technique could be used to store other custom timescaled data, for instance, taking a snapshot of milestone variance at each status date.

image

As I had to develop this solution for my presentation with Mike McLean at the recent Project Conference, I figured I’d go ahead and share the solution.

The Challenge

So the challenge I set myself was to figure out a way to manually trigger the storage of key timescaled data.  In this example, I wish to store the variance for each of the project milestones as of the status date.  The way I envision this would be that the project manager would update the schedule each week, then trigger the macro that takes a snapshot of the data.

The Macro

The VBA actually proved to be quite simple.  While there may be a performance hit, as I am only copying data into a single timescaled cell, it didn’t seem noticeable.

Sub MSVariance()

    'This macro will copy timescaled variance data into the Baseline10Cost field.
    
    Dim TSVBaselineCost As TimeScaleValue 'Capture the dataset for the Baseline10Cost
    Dim TSVSBaselineCost As TimeScaleValues
    Dim T As Task
    
    ActiveProject.StatusDate = InputBox("Enter the Status Date.", "Status Date", ActiveProject.StatusDate)
    
    Application.OpenUndoTransaction "UpdateVariance" 'Create an undo group to remove the changes

    For Each T In ActiveProject.Tasks
        If T.Milestone = True Then
            If T.PercentComplete < 100 Then
                Set TSVSBaselineCost = T.TimeScaleData((ActiveProject.StatusDate), ActiveProject.StatusDate, pjTaskTimescaledBaseline10Cost, pjTimescaleDays, 1)
                For Each TSVBaselineCost In TSVSBaselineCost
                    TSVBaselineCost = T.FinishVariance / 480
                Next TSVBaselineCost
                T.Baseline10Cost = T.Baseline10Cost + 1
            End If
        End If

    Next T

    Application.CloseUndoTransaction  'Close the undo group for the changes.
    
    MsgBox "Milestone variance has been stored in the Baseline10Cost field.", vbOKOnly, "Confirmation"

End Sub

What this does is copy the variance into the Baseline10Cost field.  Now that’s a bit of a hack, as we’re copying a number field into a cost field.  As there are no timescaled number fields, I figured it would still work – and it does.

image

To use, simply set the status date, then trigger the macro.  Navigate to the Task Usage view and add the Baseline10Cost field to confirm.

The Query

Once the project has been published, create an Excel report from an ODC configured with the following SQL query:

SELECT     TOP (100) PERCENT dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTaskByDay_UserView.TimeByDay, 
                      dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost
FROM         dbo.MSP_EpmProject_UserView INNER JOIN
                      dbo.MSP_EpmTaskByDay_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTaskByDay_UserView.ProjectUID INNER JOIN
                      dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmTaskByDay_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID
WHERE     (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost IS NOT NULL) AND (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost <> 0)
ORDER BY dbo.MSP_EpmTaskByDay_UserView.TimeByDay

The Report

Once the data’s in Excel, it’s a pretty easy report to generate.

Configure the Pivot Table so it appears as follows:

image

Add a line chart, and you now have a custom timescaled data report.

image

Storing Custom Timescaled Data with VBA