Calculating Demand with the New Resource Engagements Feature

Looks like when I wrote that post on how to incorporate Resource Engagements into the portfolio analysis module of Project Online / Project Server 2016, I left out one critical step.

For resource engagements to show up in the calculated portfolio demand, you’ll need to open the Project Information dialog box within Microsoft Project Professional and toggle the calculation settings – then republish the project.



Note that the option only appears after the project has been saved to Project Server.

Calculating Demand with the New Resource Engagements Feature

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
            SelectRow Row:=T.ID, RowRelative:=False
            TaskOnTimeline Remove:=True
        End If
    Next T

End Sub

Adding Tasks to the Timeline with VBA

TFS and Project Online Integration

Pulling the development and the project management worlds together into a single view has long been regarded as the killer feature of the Microsoft IT work management world.  For evidence of this, check out this link to the 2007 Project Conference keynote where Steve Ballmer himself was on stage helping demo the new (at the time) connector with Project Server.

Needless to say, Microsoft has made great strides since 2007.  The version of the connector released for 2010, well, just worked.  It was a bit of an effort to install (usually due to security configurations), but once you got it working, it could plug along happily.  That is still the case with Project Server 2013 on-premises…..but once you start talking about the cloud based world, it becomes a bit murky.

At the time of this writing, Microsoft offers Project Online, a hosted Project Server service and  Visual Studio Online, a hosted Team Foundation service.  This means that we regularly run into customers that have Project Server, Visual Studio, or both online.  The question inevitably comes up about whether any of the online options can be integrated with each other – or with on-premises installations.

This post is intended to run through the various options and provide recommendations:

Out of the Box Server – Server Integration

Currently, the downloadable integration pack only works to link Project Server on-premises with TFS on-premises.  Note this pack is downloadable from MSDN Ultimate. I’m pretty sure it’s the same pack for both Project Server 2010 and 2013, but let me know if you find out otherwise.

This option is definitely the recommended option if both TFS and Project Server are on premises.


Project Desktop Integration with Team Explorer

This works reasonably well, although it assumes your project managers are scheduling within the Microsoft Project desktop application and not using the Web based scheduling feature.  Essentially, you download the free Team Explorer add in, which then adds a tab to your Project installation.


When updating tasks in Project, click the option to Publish, and the tasks appear in your TFS (online or on-premises) instance.


I was able to create a Visual Studio Online tenant and link it to my project in a couple minutes.  Admittedly, this is not a seamless integration:

  • It requires use of desktop scheduling (which – to me – is not a major issue.  I live in the desktop scheduling tool).
  • It requires a couple extra steps to push the data from the desktop to TFS – well actually, one button push.
  • It requires an extra step to download the data from TFS into the project schedule.

Also in the past, I’ve heard of issues where TFS will overwrite key data in the project schedule, like durations, etc.  In testing out the Team Explorer add in, I didn’t experience these issues, but feel free to report back in the comments below.

Until there is direct out of the box integration with cloud based implementations, I’d definitely recommend going this route for connecting Project Online with Visual Studio Online, Project on-premises with Visual Studio Online, and Visual Studio on-premises with Project Online.

Custom Development with CSOM to Project Online

For those of you running TFS on-premises and Project Online – which is the most common scenario we see these days, there is always the option of writing custom code to push or pull data from Project Online into TFS.  To assess whether or not the Project Online Client Server Object Model will support custom scenarios, I would encourage you to look at this link:

…then take a look at this link that Ionut wrote about remote event handlers for Project Online:

Visual Studio Online is also extensible.  See the link below for more information on integration with other services:

I would stress that the effort to develop your own integration package probably outweighs the incremental benefits that may be earned over using the desktop integration method.  Customization scenarios typically only work when there is a significant need for automation and your processes are very well defined.

3rd Party Tools

Last but not least, take a look at third party tools that may be out there to assist you in the integration.  More and more offerings are coming out each day, and stay tuned on the Project Server or Visual Studio application store to see if anything has popped up to support this scenario.

As more and more organizations move to the cloud, it will be refreshing to see Microsoft add extensibility options to allow improved integration and customization.  That’s definitely the direction I see happening with the cloud based offerings – and it will only drive the development of ever more applications that fill in the current gaps in functionality.

For some additional blog posts about connecting integrating Project Server and TFS….

TFS and Project Online Integration

Project Conference 2014 BI Linkfest

Thanks everyone for coming out to Mike McLean and my presentation on Business Intelligence in Project Server 2013.  Per some of the comments received, I wanted to provide a couple of links to blog posts supporting the demos we delivered:

  1. LINQPad and Office 365 (make sure to check the comments section)
  2. Major Milestones and OData
  3. Cumulative Milestone Report and OData
  4. Project Reporting in Power View Maps

…with additional blog posts forthcoming on Power View for SharePoint and SSIS against OData.

For those of you using on-premises versions of Project Server 2010 or 2013, I would encourage you to take a look at the following links for additional ideas, resources, and recordings of previous sessions with a different set of demos:

  1. SharePoint Conference 2012 BI Demo Links
  2. Project Conference 2012 BI Demo Links (Part 1)
  3. Project Conference 2012 BI Demo Links (Part 2)
  4. Project Conference 2012 BI Demo Links (Part 3)
Project Conference 2014 BI Linkfest

Your Schedule is Totally Mental

Folks like me often get a lot of push back from project managers as we work with their PMO to ramp up the quality of their schedules.  Most often, I see complaints not about the schedule itself, but about the seemingly arbitrary list of arcane rules required to ensure that the schedule prediction is underpinned by established modeling best practices.  Examples of such rules might be that every task should have at least one predecessor and successor, tasks should not exceed a specific duration, or that the update methodology must be strictly adhered to.  You know, simple DCMA 14 Point Assessment stuff.

A schedule developed without following these rules may still be accurate, insofar as the dates may actually be realized as reported, and the data all supports the organizational reporting requirements.  The model however, the underlying logic, is invisible.  It’s all happening in the PM’s head and then being reported through the schedule mechanism.

What we have at this point is a reporting schedule.  It’s a schedule created to meet the minimal organizational requirements and to document the key dates of the project.  It does not, however, capture the underpinning logic of the schedule.  It is missing the schedule model.

A couple of years ago, PMI introduced these two concepts, the concept of the “schedule model,” or the logical predictive model of a schedule, and the concept of the schedule itself, which is a static snapshot of the schedule model at a specific point in time.  For example, I create the schedule model in my favorite scheduling application, with all of the dependencies and sparing use of constraints, etc.   Then, every week, after updating my model, I generate my prediction of what the future will look like.  That prediction is my schedule.  The schedule is refreshed each week with the output of my updated schedule model.

Are these predictions correct?  I don’t know that anyone can ever say a prediction of the future is correct.  The more accurate question is whether these predictions are valid.  Are they an accurate reflection of everything we know about the work to date?  In fact, that’s my litmus test for validity.  Can I look at your schedule, and ask you, point blank, “Is this the most accurate prediction of the future based on what you know today?”  If the answer is anything other than yes, I would consider the schedule to be invalid.

Let’s take that and apply it to a typical audit scenario.  In this scenario, you, the project manager, are telling me that the dates are all correct and valid per your latest understanding of the project.  This statement is something that I cannot challenge.  Well, I cannot challenge this statement – with the possible exception of calling out tasks completed in the future or incomplete work still scheduled in the past.  What I can do is ask how the model was developed, to which the response is invariably, “It’s all up here.” with a finger tapping the temple.

In essence, what you’re telling me is that your schedule model is all in your head.  It may be valid and it may be invalid, but I, as an external observer have no way of identifying that.  Your schedule model is hidden to me, and therefore, unless I trust you implicitly, I can’t trust your model.

This is why we have schedule audits.  This is why we have DCMA checkpoints.  Because while it would be nice if we all just had a little more trust of each other, given the high cost of projects in the world today, that’s a luxury that many organizations simply cannot afford.  And all of those audits and quality assurance processes come to nothing when the schedule model is hidden and we’re only allowed to see the schedule.

So in the end, while you can show me a schedule that’s resource loaded and has all of the key organizational milestones attached to it, you can’t show me your schedule model.  You see, it’s all in your head, it’s all mental.  And that’s why  I can’t make a judgment about whether or not you have a valid schedule.

Your Schedule is Totally Mental

SharePoint Conference Project Server BI Linkfest

Thanks everyone for coming out to #SPC171 just now, where Microsoft’s Mike McLean and I skimmed a rock over what’s new in Project Server 2013 BI.  Looking for more?  Well, you came to the right place.

As we mentioned, we’ve got a whole slew of demos that we couldn’t quite fit into this presentation, about 150 minutes worth.  Feel free to catch these recordings from the Project Conference last March.  As you’ll see, this was presented on Project Server 2010 – but they’re equally applicable to Project Server 2013 on-premises – and mostly applicable to Project Server 2013 in the cloud.

  1. Session #1: Intro to Project Server BI
  2. Session #2: Even More of the Same
  3. Special Bonus Track: the same presentation at TechEd in June

Additionally, here’s an inventory of reports, demos, and the step by step instructions to reproduce them all:

  1. Linkfest #1: General Stuff, ODC, Visio Services
  2. Linkfest #2: Excel Services & the REST API
  3. Linkfest #3: PerformancePoint and Fun with VBA

Looking for OData reporting against Project Server?   Stay tuned for more content, but for now, this should get you started…

  1. Reporting on OData with LINQPad
  2. Creating a Major Milestones Report
  3. Creating a Cumulative Milestone Report (Part 1, Part 2, Part 3)
  4. Maps, Maps, Maps

Maybe some general Project Server information is more to your liking?  Yep, we’ve got some of that too.

  1. Becoming a Project Server Administrator
  2. 10 Things SharePoint Admins Should Know About Project Server
  3. 10 Things Project Server Admins Should Know About SharePoint

…and last, but not least, here’s a white paper I wrote a while back talking about the options to report against Project Server 2010.  Stay tuned to this blog and others for more Project Server 2013 fun…

SharePoint Conference Project Server BI Linkfest

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.


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.


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.


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.


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

    '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…


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.


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.)


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.)


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.


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.


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:

'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

'Refer to this page for language codes:
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 sToken As String

   ID = ""    ' Bing appID deprecated in Dec 2011 in favour of Access Token. Use nothing for legacy appID parameter
   sURL = "" & 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
   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 = ""

Dim sRequest As String
sRequest = "grant_type=client_credentials" & _
      "&client_id=" & ClientID & _
      "&client_secret=" & URLEncode(ClientSecret) & _

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.


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.


Project VBA and Azure: Better Together