Using schedule data within a Nintex Workflow for Project Server workflow

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.

Project scheduled - Task Stage

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.

Nintex Execute SQL Action

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.

Nintex Workflow - Initial plumbing

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.

image

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.

Execute SQL Settings

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:

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'

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.

image

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.

Nintex Workflow - Loop Actions

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.

Initial workflow - Action History

Clicking on the submit button will cause the workflow to proceed and check the schedule.

Initial workflow - Action History - Wait for Submit

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.

Edit the schedule to be 100% complete

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.

1 stage milestone outstanding

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.Workflow completed

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

Advertisements

One thought on “Using schedule data within a Nintex Workflow for Project Server workflow

  1. Great post, mate! We’ve been working with this a bit as well.

    While I agree the “Insert SQL” feature of Nintex is pretty cool, I am a little diappointed by the dependence on it. The real draw of the Nintex tool is that it enables the PMO to build workflows. To me, that says “you don’t have to be a developer or a DBA to make it work”.

    You clearly have to be a developer or a DBA to make “Insert SQL” work well.

    I hope the next version of the Nintex tool will allow us to call task-level information using the GUI. I think it should.

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