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.
- A custom action list with the link to the workflow – as described here. (May need to be adapted to this purpose)
- A milestone reporting list.
- 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:
- Delete all existing milestones from the project on run time.
- Get the OData values
- 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.
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:
- Build a dictionary to capture the header data for the HTTP call (Accept=application/json;odata=verbose, Content-Type=application/json;odata=verbose)
- 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):
- Kicking of Milestone Refresh for Project UID = 70ce26da-4251-e411-be78-00155dc21d2f
- MilestoneGUID =
- No existing milestones found.
- Getting Milestones
- 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
- 3 Milestones Returned
- TaskID: 97ac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 3; TaskStartDate: 10/14/2014 10:00:00 AM
- Item Created
- TaskID: 98ac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 4; TaskStartDate: 10/14/2014 10:00:00 AM
- Item Created
- TaskID: 9aac721e-8651-e411-8b36-00155d80a23c; TaskName: Task 6; TaskStartDate: 10/15/2014 10:00:00 AM
- Item Created
- Workflow Completed