A few months ago, I was working with a Project Manager, who was very upset. Work on his project was delayed, because one of the critical resources assigned to it went on vacation without informing the PM.

I immediately checked if there was a Timesheet Entry for the “Planned” vacation, and sure enough there was a timesheet entry. Granted, that the PM should have caught the shift in project/task dates due this ‘unavailability’, his point was that it would have been nice if there was an email/central report that alerted him on the planned vacation of the resource. Out of the box, Project Server Timesheet only send the vacation approval to the Timesheet Manager, and not the project managers.

There are several ways to handle this request:

1) A lot of organizations use a Shared Calendar for recording their vacation, and out of office time, just so that other people know that it is coming up. Also if you use Outlook, then you can connect your Outlook to this calendar and have sort of heads-up on this.

2) we could build a report in SSRS, and set up alerts/subscriptions.

Another way, other than the above, and the one we will cover in this post is to use the Event-Driven Workflows available in Nintex.

The idea is simple:

We will use Nintex, to post an entry in to a SharePoint Calendar, every time somebody “plans” vacation using Timesheets.

Using SharePoint calendar, not only will allow for an easy to view “upcoming Vacations, it also will allow each Project Manager to setup alerts for their project resources and so on.

Step 1: Create a SharePoint Calendar

From the PWA Homepage, click on the ‘Gear” icon on the top right corner, and select Add an App

Click on the Calendar App to create a Calendar, give it a name, and Click Create.

Step 2: Building the Event Driven Workflow

Navigate to the Event Driven Workflows screen as shown below

Click New

This will open up the Workflow Designer.

To begin with, add the following workflow variables:

Now, add the following workflow actions:

I am attaching the configuration for each of these actions; reference the number codes.

The SQL Query used in both 2 and 3 are (for Vacation end Data, just change the order by to Descending)

Select Top 1
Convert(VarChar, TimeByDay,101)
from
dbo.MSP_TimesheetActual_Olapview a
inner join
MSP_TimesheetLine_Userview b
on a.TimesheetLineUID=b.TimesheetLineUID
where
(b.timesheetlineclass=’vacation’
AND
a.plannedwork>0
AND
b.TimesheetUID='{WorkflowVariable:TSUID}’)
order by TimeByDay

And finally, Create an item in the Vacation Calendar.

Save and Publish the Workflow. Now it is time to attach this to the actual event of the Timesheet Submission. Navigate to the “Manage Event Driven Workflows” page as above, and click on Create.

Set the options as shown and Save.

Now, you are all set. When you go into the timesheet and enter some “Planned Vacation”,  and save the update, it will create an item in the Departmental Calendar.

That’s pretty much it.  From here on, each PM can set their own alert for their critical resources, days etc.,  And the resources do not have to use ANOTHER timesheet system/Calendar, if they are already using Project Server Timesheets.

And finally I do not handle the scenario where vacation is not continuous, but I am sure the query could be written to account for that.