Using schedule data within a Nintex Workflow for Project Server workflow
By blurg64
Recently I have been looking into how far you can integrate a Nintex Workflow for Project Server workflow into the day to day management of a project. One particular area I have been concentrating on is encoded logic within the workflow to check the status of tasks within the schedule. Imagine the scenario, a project can only move into the close phase if all the deliverables or milestones in the project have been met, or you may want the phase of the project to change following the completion of a specific task.
In this post I am going to explore using Nintex Workflow for Project Server to query a project schedule to determine if all the milestones within the current phase have been completed before allowing the workflow to move forward.
The Schedule
In order for the workflow to determine which milestones belong to which phase, it is necessary to record the phase information against each task. To do this I have added a new Task custom field called ‘Task Stage’ and entered the relevant stage into it for each task.
The Workflow
For the workflow I am going to follow the same basic pattern used for the Stage Gate approval in this post, a variable controlled loop that will continue to repeat until a certain condition is met. Unlike the previous post, instead of a task being assigned to security group for approval, we are going to leverage another Nintex action, called Execute SQL.
This action, in my opinion is one of the most underrated actions in the Nintex suite, usually because it’s only used to query non SharePoint data stores (remember direct SQL lookups against the ContentDB are not supported and shouldn’t be attempted). However as Project Server has it’s own dedicated Reporting database which contains a wealth of information about each project including the schedule that can be queried, we can leverage the Execute SQL action to our hearts content.
To start with, I am going to create the plumbing for the workflow, starting with setting up the initial stage (Execution) and logging some status information. Following this, the workflow variable that will control the loop is set to False (Phase Milestones Complete) and finally the loop action is added that is set to repeat whilst the Phase Milestones Complete variable is false.
Inside the loop we need to add a few more actions, starting with the Wait for Submit action that holds the workflow until the Submit button on the ribbon is pressed and the Execute SQL action which is going to do the clever stuff.
To configure the Execute SQL action, firstly the connection string needs to be configured, in this case I have configured the action to look at the PWA_Reporting database using the contoso\administrator windows logon.
The core of the action is the SQL itself, which does a count of all records the current project that are:
- Milestones (via the TaskIsMilestone flag);
- 100% complete, and
- in the current stage
and stores the result in the variable ‘Outstanding Milestone Count’.
The full SQL used is shown here:
[sourcecode lang=”SQL”]
select count(epmt.TaskUID) as [Outstanding Milestones] from MSP_EpmTask_UserView epmt
inner join MSP_EpmProject_UserView epmp on epmt.ProjectUID = epmp.ProjectUID
inner join MSP_EpmWorkflowStage epmws on epmt.[Task Stage] = epmws.StageName
where epmp.ProjectUID = ‘{Common:ProjectUID}’
and epmt.TaskIsMilestone = 1
and epmt.[TaskPercentCompleted] <> 100
and epmws.StageUID = ’43A1EB7B-562D-42E8-9A96-88817EF74295′
[/sourcecode]
You will notice that we leverage Nintex’s ‘Insert Reference’ capability to inject the ProjectUID directly into the query on line 4, also their is a GUID hardcoded into the query on line 7, in this case it’s the is the StageUID for the Execution phase, as determined by looking at the System Identification Data on the Execution stage page. Finally the query above joins the Task Stage to the Project Stages using the Stage name, so it’s imperative the stage names in the schedule match the stage names in the workflow.
When the SQL is run, if all the milestones for the current phase have been completed, the SQL Query will return 0, otherwise it will return the number outstanding.
The last part of the workflow uses a Set a Condition action that checks the number of outstanding milestones returned by the query and either breaks out of the loop if there are none, or loops again if there are one or more.
That’s really all there is to the workflow, hopefully the breakdown above was easy to follow, but in case it wasn’t I have published the full workflow image here for your viewing pleasure.
Running the workflow
To test the workflow, I have created a new EPT called ‘Milestone Test’ with two phases (Execution and Closure). To the workflow I uploaded the schedule from above as a template and associated it to the EPT so it would be created automatically.
Once the project has been created, the workflow will start and then wait until the submit button is pressed.
Clicking on the submit button will cause the workflow to proceed and check the schedule.
Notice the workflow has gone back to the Wait for Submit action again and noted that there are 2 milestones outstanding. Next open up the schedule via the Schedule PDP and set one of the milestones to 100% complete, then republish and submit once again.
You can see that the outstanding milestone count has gone down to 1, but the workflow is still in the Execution phase. This is because we still have one milestone left to complete before we can move to the next stage.
Lastly, set the final milestone to be 100% complete and republish and resubmit. The workflow will detect there are no outstanding milestones for the phase from the SQL query and therefore allow the workflow to proceed through to the next stage.
As you can see the Execute SQL action is a very powerful action within the Nintex toolset and when coupled with the Project Server Reporting Database allows you to incorporate nearly any aspect of your Project Server data into your governance workflow. Now get coding