Creating Status Reports with Project Online and SharePoint Designer (Part 5)

In the last couple of posts, I talked about how to leverage SharePoint workflow and OData to create a status reporting mechanism in Project Server and Project Online.  Let me recap.  On second thought, there is too much…let me sum up:

  1. Post 1: Creating the Required Lists and Columns
  2. Post 2: Creating the Workflow to Capture Status in a Status Lists
  3. Post 3: Creating a Workflow to Create the URL to Trigger the Status Reporting
  4. Post 4: Adding All of that to Project Detail Pages

In this post, I’d like to take that just a bit further and add a couple of custom actions to our status list.  These actions aren’t required, but they go a long way towards making the experience more intuitive (read: less training) for the end user.  We’re going to add those actions to the drop down list next to the status list item.


To do this, we’ll add a custom column to the list called Update Status.  I set it so it defaults to “Draft.”


Now we’ll go into SharePoint Designer and create a simple workflow on the list to change the field from Draft to Final.


Publish the workflow.  Now, within SPD, go to the option to modify the Status List.  You should see a screen like this:


Note the Custom Actions in the bottom left.  Create a new one, and set it to initiate a workflow.


Go back to the list and you’ll see that a new Approve Item step has been created in the drop down.


Click on it and the item will be updated.  Couple that with an e-mail alert task and you could create an entire status approval workflow easily triggered from the list item.

Posted in Admin, Project Online, SharePoint, Status Reports, Workflow | 1 Comment

Creating Status Reports with Project Online and SharePoint Designer (Part 4)

In the last several posts, we created a couple lists and the workflow logic to move data back and forth.  In this post, we’ll continue to work on the user interface to support our status reporting by creating two PDPs:

  • Custom Project Actions
  • Status Updates

Both PDPs will be pretty simple.  We’ll create a blank PDP.


…and add two webparts:

  • Query String Filter Webpart
  • List Webpart


Configure the Query String webpart to pull the ProjectUID from the URL.


…then connect it to the list to provide an automatic filter.


Repeat the same steps for the Status List PDP.  Add the PDPs to the appropriate workflow stage, and we can review what we have created…to do this, I create a new project – which triggers our new workflow to create a custom Project Action.


Click on the link to trigger our status update workflow….  We can then go to the Status List PDP to review the results:


But we’re not done.  In the next post, let’s add a couple of bells and whistles to update our status report.

Posted in Admin, Project Online, SharePoint, Status Reports, Workflow | Leave a comment

Creating Status Reports with Project Online and SharePoint Designer (Part 3)

In the last post, we created a workflow to trigger a status reporting process.  In this post, we’ll look at how to create a trigger for the workflow and embed it on a PDP for easy access and intuitive use.  To create this trigger, we’ll need to go to the Project Action list and start our new workflow.

Find the Initiation Form URL

To enable the testing of the workflow, I’ve manually create an item in our list with a functioning Project UID.


I fire off the workflow on the second item in the list.  This displays the Initiation Form we configured in the second post.


Note the URL for the form:{1c1a0038-b729-4b26-99d8-193d46c13ecd}&ID=2&ItemGuid={B4276416-A12E-4721-B2FC-40CE66666BAE}&TemplateID={1F39EC48-B553-45F6-911D-64DE745056A0}&WF4=1&Source=https%3A%2F%2Fprojectna%2Esharepoint%2Ecom%2Fsites%2Fpwa%2FLists%2FProject%2520Actions%2FAllItems%2Easpx

It looks like a long string of variables – which it is.  We are going to parse this and add it to the Project Server workflow so a new unique URL will be generated in the Project Action list each time a new project is created.

Parsing the URL

The first issue is that the URL is too long to store in a standard link list column.  If we try to use this URL, the workflow will fail.  Hence, we need to strip all of the nonessential components from the URL.


That yields a much shorter URL:{1c1a0038-b729-4b26-99d8-193d46c13ecd}&ID=1&TemplateID={1F39EC48-B553-45F6-911D-64DE745056A0}

Test this URL by dropping it into the browser to see if it activates our status update workflow.

Creating the Project Workflow

We’re now going to add a step to our project workflow to create the URL to trigger the form.  In this case, I have a simple workflow with a single stage.  In this stage, I’ve added three steps:


The first thing we’re going to do is create the initial item in the Project Action list.  We do this so that we can get the item ID, which is then added to the URL, and then updated back into our item.  Feel free to add any URL to the URL field – and populate the Project UID field with the correct lookup.


Note that this creates a workflow variable for the UID of the item in the Project Action list.  We’ll use this UID to come back and modify the item in step #3.


Here, we run into a limitation of SharePoint Designer.  If we try to create a URL that uses lookups as well as characters such as {,} & %, we will get an error.


For the search engines: Using the special characters ‘[%%]’ or ‘[%xxx%]’ in any string, or using the special character ‘{‘ in a string that also contains a workflow lookup, may corrupt the string and cause an unexpected result when the workflow runs.

Luckily, there’s a simple hack to get around this.  We need to capture the various elements with special characters as their own workflow variable – then compile that back into the URL.


Now we reassemble the URL, using the variables instead of special characters.


Note the addition of the user friendly label at the end of the URL.  Also note that we are looking up the ID for the item in the Project Actions list using the key of item GUID = workflow variable ActionUID.

From there, we go back and update the Project Action item with the correct URL.  The workflow should now look like this:


Add this to an EPT in Project Server…and we’re ready to move to the next step – creating the PDPs….

Posted in Admin, Project Online, SharePoint, Status Reports, Workflow | Leave a comment

Creating Status Reports with Project Online and SharePoint Designer (Part 2)

In the last post, we created two lists, one to hold the workflow trigger and one to capture the status of the project.  In this post, I’ll run through how to leverage workflow to capture project data and then store it in the status list.  To do this, we’ll need to fire up SharePoint Designer and point it at our PWA site.


Create a new workflow and assign it to the Project Actions list we created in the previous post.  Deselect the option to Automatically update the workflow status to the current stage name.

We also need to add a multiline parameter to the Initiation Form called status.  The Initiation Form is important because it can be called through the use of a simple URL.  We will capture this URL and embed it in a PDP to trigger the status reporting workflow.


The workflow will now be comprised of two stages:

  • Capture project data.
  • Create the status list item.
  • image

Consuming Project Data with SharePoint Designer

To consume project data, we basically need two actions within the stage….a Call action and a Get action.  For troubleshooting, we also want to log all transactions to the Workflow History list so we can trace when things fail.


We configure the Call action as follows (for my tenant, which is  You’ll need to plug in the name of your own tenant.):


See how the Call action is pointing to the OData repository and then plugging in the Project UID field from the Project Action list?  Feel free to test this out by entering the URL into your browser, but substituting an actual Project UID for the lookup.

The other parameters in the Call action can basically be left blank, although I did name the content of the response “ResponseContent.”  This will be the dictionary of results – or each of the project level fields for the designated project.

We then need to Get the appropriate field from the ResponseContent dictionary and push it into a workflow variable.  Just follow the OData naming convention to identify the correct field name.  In this case, I am using LINQPad to assist in navigating the data structure.


Finally, write the new variable to the Workflow History list.  This will support troubleshooting efforts.

Now repeat the Get and logging actions for each field we need.


From there, we just need to create the status list item to store the data.


That yields an entire workflow that looks something like this:


Publish that workflow, and we’ll come back to it.

Next up, we build the trigger for this workflow and add it to the Project Action list…..

Posted in Admin, SharePoint, Workflow, Project Online | 1 Comment

Creating Status Reports with Project Online and SharePoint Designer (Part 1)

This blog started way back when with a post on creating status reports.  Back then, my go to method was to leverage an InfoPath form embedded in a Project Server PDP.  The key was to pull the ProjectUID from the PDP URL, drop that into a status report entry in a custom list, and then surface the status reports on another PDP.  This concept worked reasonably well, and I managed to leverage it for a number of use cases throughout the last couple of years.

Of late, I’ve been playing with SharePoint Designer workflows, and I came across another method of creating status reports.  In fact, status reports are merely one use case.  The techniques that this and several future blog posts will show will allow administrators to create a whole slew of custom actions that could augment the traditional Project Server experience.

Note that nothing I am writing about is specific to the Project Online or Project Server on-premises.  This could be applied to either platform.

Creating the Status List

To support this scenario, we will need to create at least two workflows, and at least two custom lists.  The first list will be our status update list.  I’ll create that as a custom SharePoint list and add columns for the Status, Project UID, Project Name, Project Start Date, and Project Finish Date.  The reality is, of course, I can add any fields that I would like.


This list will be used to capture the status updates.

Creating the Project Action List

Next, we will create the list to store our custom project actions.  This list will be a custom Link list.  We will also use this list to host the status reporting workflow.  To this list, we add a column for the Project UID.  Ignore the two crossed out columns, as those were used for testing the workflow.


That’s pretty much it for the blocking and tackling.  In the next post, I’ll talk about how to read project data in a workflow, and write it to the status list.  We’ll follow that up in the third post with a discussion of how to create URL triggers for the workflow.

Posted in Status Reports, Admin, SharePoint, Workflow, Project Online | Leave a comment

Project Conference 2015 Announced – Well Sort Of

See this link for more details:

As has long been predicted, this will be the big one – where the worlds of collaboration collide for a unified Project/SharePoint/Exchange/Lync conference.

Posted in Events | Leave a comment

Project Scheduling and the World Cup

In my training sessions, I often use sports metaphors for project schedule modeling – this, despite my utter lack of any sort of athletic ability and pathological indifference to most sporting events.  The thing is, sports events are great examples of predictive models.

Take the World Cup for instance.  If I were to ask you who would have taken the Cup way back at the beginning of this thing, you would have selected from 32 teams.  At a basic level, we could say that each team has an equal chance of winning.  Of course, we can make the model more complex.  We can look at individual player performance, historical records, playing style, locations in which matches will be played…..  When we throw all of those together, we can create a model of the top 5-10 favored teams.

What is this doing?  It’s adding information to the model to predict the future – and providing a range of potential outcomes.  Like any schedule, as I learn additional information, I include that in my model and refactor the results.  From where I sit at the time of writing, we have now gotten down to the quarterfinals.  Of the 32 teams that started, 8 are left.  Clearly, I can remove 24 of the original candidates from the list of potential winners.

Simply by removing those 24 from the list, I have greatly increased my chances of being correct about who will win the championship.  Can I tell exactly who will win?  No, but I can apply my data modeling structure to the remaining teams and come up with a much better prediction than when the games started.

Extrapolate that forward, and as each game is played, the potential outcomes for the competition narrow even further.  Eventually, we’re left with two teams – which greatly simplifies the modeling.  Finally, at the end, we know who won.

Apply this model to a schedule.  We forecast a range of potential outcomes, with a more probably outcome and a less probable outcome.  As the project plays out, the range of potential outcomes diminishes.  The most probably path becomes more and more apparent.

Another metaphor I commonly use is a toothpaste tube.  If you assess the amount of risk, or uncertainty, associated with the schedule.  As events increasingly transform from the potential future to the recent past, the amount of uncertainty goes down.  It’s the same as squeezing toothpaste from a tube.  At the end of the day, there’s no uncertainty as the project is complete.  All of the risk has been squeezed out of the tube.

Posted in Scheduling | Leave a comment