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

image

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.

image

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.

image

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.

image

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.

image

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.

image

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 = https://servername.sharepoint.com/sites/pwa/_api/ProjectData/Projects(guid’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

image

Advertisements
Updating Milestone Reports One Project At a Time in Project Online

One thought on “Updating Milestone Reports One Project At a Time in Project Online

  1. Hi. Thanks for this article. I am using project server 2013 with SharePoint 2013 and wants to automatically populate milestones in project site list whenever a new project is created. In your oData where is the field TaskIsMilestone? How will you get Milestones in SharePoint Designer 2013 as there is no milestones field in task list. Your input in this will be highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s