Know Microsoft Project? Move to Colorado

From Governor Hickenlooper’s State of the State speech:

We have launched an initiative that pulls together state resources from several departments, with the single-minded objective to assist long-term unemployed workers find work.

People like Wendy Stedman.

Last year, Wendy walked into a Colorado Workforce Center in Centennial and enrolled in the Dislocated Worker Program. A veteran who served this country honorably, she had lost her job working in I.T. and communications systems.

Wendy’s workforce specialist provided her with resume and interview skills coaching. Together, they determined she would be more marketable with some specialized training.

Wendy was approved to attend Microsoft Project certification classes, and her previous employer re-hired her as a Technical Project Manager.

Please join me in asking Wendy to stand so that we can congratulate her on her new job and thank her for strengthening Colorado’s economy.

The investments we make in our workforce development programs benefit not only the long-term unemployed but also employers and the entire state.

Know Microsoft Project? Move to Colorado

Updating Milestone Reports One Project At a Time in Project Online

One of the main concerns about reporting with OData is that it’s tough to control the specific parameters of the data query on the fly.  The default solution is generally to run one massive query, dump the data into a single repository, and then report on that.  When that massive query exceeds a certain size, performance concerns kick in and organizations need to start looking at the SSIS option that’s been well publicized of late.

The question came up the other day as to whether or not there could be a mechanism to automatically generate queries for each project individually – and thus mitigate potential performance issues.  This post is an attempt to address that using SharePoint Workflow to pull data from OData and deposit it into a centralized SharePoint milestone list.  These milestones may then be reported on within the context of a PDP.

To create this solution, we’ll need a couple of components.

  1. A custom action list with the link to the workflow – as described here. (May need to be adapted to this purpose)
  2. A milestone reporting list.
  3. A workflow to capture the OData milestones and populate the milestone reporting list.

Custom Action List

For this post, I created a simple list that had one required field, the ProjectUID field, and a workflow attached.  This would be the custom action list mentioned above.  (See that blog post for more information).


Milestone Reporting List

This is also a simple list with a couple custom fields to capture the OData values.  I would envision this being added to a PDP and filtered using the QueryString filter on the ProjectUID field.


This list will be populated through the workflow we’ll be deploying to the Milestone Trigger list.

Populate Milestone Workflow

To populate the milestone data, we’ll create a new workflow and apply it to the Milestone Trigger list.  This workflow will consist of 2-3 parts:

  1. Delete all existing milestones from the project on run time.
  2. Get the OData values
  3. Populate the milestone list with the OData values

Note this is all conceptual stuff – i.e. I’m not designing for performance or error handling or anything like that.  I would expect this approach to require some modification before being deployed in a production environment.

Remove Existing Milestones

This part is pretty straightforward.  I’ll just throw a screenshot in here.  You’ll see I loop through and look for any milestone in the master list with the Project UID.  If found, it will be deleted.  If not found, the workflow will progress to the next stage.


Get Milestones

At this point, there are plenty of blog posts on how to consume OData through SharePoint Workflow so I won’t rehash it all here.


In a nutshell:

  1. Build a dictionary to capture the header data for the HTTP call (Accept=application/json;odata=verbose, Content-Type=application/json;odata=verbose)
  2. Set the query string to get the right OData values.


3. Count the number of results using a couple of variables.  (Don’t ask – you just need to)

Populate Master Milestone List

This part is a bit easier.  Iterate through each of the results in the MilestoneList dictionary and create an item in the Master Milestone list for each result.


Put it all together, and execute it, and I ended up with the following log (on a project with three milestones):

  1. Kicking of Milestone Refresh for Project UID = 70ce26da-4251-e411-be78-00155dc21d2f
  2. MilestoneGUID =
  3. No existing milestones found.
  4. Getting Milestones
  5. QueryString =’70ce26da-4251-e411-be78-00155dc21d2f’)/Tasks()?$filter=TaskDuration eq 0M and TaskStartDate ne null&$select=ProjectId,TaskId,TaskName,TaskStartDate
  6. OK
  7. 3 Milestones Returned
  8. TaskID: 97ac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 3; TaskStartDate: 10/14/2014 10:00:00 AM
  9. Item Created
  10. TaskID: 98ac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 4; TaskStartDate: 10/14/2014 10:00:00 AM
  11. Item Created
  12. TaskID: 9aac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 6; TaskStartDate: 10/15/2014 10:00:00 AM
  13. Item Created
  14. Workflow Completed


Updating Milestone Reports One Project At a Time in Project Online

Happy New Year!

…Mongolian New Year that is.  May your animals fatten nicely…

In honor of the holiday, I figured that I’d include this useful vocabulary tip from a Project Manager’s Guide to the Mongolian Language:

Buda – dry rice.  Not cohesive.  No matter how hard you squeeze a handful of dry rice, it will never stick together.  Example: “Call a wrecker, this car is buda.” or “Stick a fork in it and call it done.  This project team is buda.”

Yag Shuu Dee – (pronounced “Yagshday”).  “Yes,” but actually meaning “No.”  The ironic equivalent of “Yeah, right.”  As in “Project management is great!  Yag Shuu Dee.”

Link below for further reading….

Tsagaansar 2012

…and pictures from our last trip to the Land of Blue Sky – although it was more grey sky at the time.

Happy New Year!