Ask Project Server implementers what the “killer app” for Project Server would be, and most would tell you it would be adding the ability to take timephased snapshots of project data. Think of it….each week, your organization takes a snapshot of key project metrics, and adds those to a database, which may then be used as the backend for a reporting function.
I’ve long been an advocate of keeping an Excel spreadsheet in parallel with my project schedule. Then, on each status update cycle, I add the key metrics to my spreadsheet. It’s not that difficult, and requires a custom view or two within Project to facilitate the process, This allows me to generate ad hoc reports as needed. The challenge however is that the information is locked in an Excel spreadsheet and not easily accessible to the organization.
The enterprise-wide solution for accomplishing this is typically to implement some mechanism within the SQL database to take a snapshot of each project on a specific timed interval. That requires some SQL wizardry to keep running, but is not overly complex.
One possible objection I might have to this approach however is that it assumes the project manager has updated the project schedule and published it prior to the deadline. If the project manager misses the Friday deadline, and comes in on Saturday to update the project, that data does not make it into the snapshot. I want a snapshot mechanism that is triggered by my own action.
My other concern would be that as a project manager, I often take that snapshot using a manual process, recognize potential discrepancies, go back into the schedule to update it, and then redo the snapshot. So I want a mechanism where I can preview the results, and then override them as required. SQL doesn’t quite provide that.
So what to do? Well, as I was playing with External Content Types (ECT) for a totally different purpose, I realized that they may also be used to develop timephased snapshots. Here’s how to do so:
Create the ECT
Follow the instructions I laid out in this post to create the ECT. The main difference is that when it comes to the Read List operation, you need to add a wildcard filter on the ProjectName field. Follow that post until you get to the section with text marked <<bookmark>> and insert the following instructions.
When creating the Read List operation, the first screen will ask for the Filter Configuration. Select the ProjectName field, and add the wildcard operation as depicted below.
Click OK, finish configuring the Read List operation and click Save to deploy the updated ECT back to SharePoint.
Create the Custom List
We’ll now create a custom list to capture the project snapshots. Navigate back to the PWA site and select the option to View All Site Content under the Site Actions menu.
Create a custom list. I’ll call this list the Project Data Snapshot.
Once the list has been created, we need to configure the columns for the snapshot data. Click on the List Settings under the List tab.
Select the option to create a new column.
In the screenshot below, I show how I configured the new column of the External Data type. Note that I can select any of the fields from the ECT to include in the list.
Next, configure the Title field so it defaults to Project Snapshot. Click on the Title column within the List Settings and set the default value to “Project Snapshot.” You could probably make it calculate today’s date, or even use InfoPath to add a complicated function to determine the title. Simple text will suffice for this example.
Let’s test to see if the list works. Navigate back to the list and select the option to Add a New Item.
Type the project name into the Project Data field. Click the first icon to resolve the name. Alternately, click the second icon and search for the project name in the pick list that is displayed. Click Save.
Confirm that the correct data is populated within the list.
Configure List Security
As discussed above, as a project manager, I would like to have the option of taking a snapshot, reviewing it, and then making edits to the project schedule before revising the snapshot. As an administrator, I also want to make sure that each item is timestamped and cannot be edited after creation. How to resolve these concerns? Let’s set security on the list so users may create and delete, but not edit. In essence, we lock down each line of data, but allow the PM to potentially replace one with a more recent snapshot.
As an administrator, I can create alerts on each of these activities to notify me of potential issues or users in need of supplemental training.
Go back into List Settings. Click on the Permissions option.
Select the option to Stop Inheriting Permissions from the Site.
…now go back to Project Web App. Select Site Settings under the Site Actions menu.
Within the Site Settings, select the option to set Site Permissions.
Click on the permission levels option.
Select the option to create a new permission level.
Give the permission set Add, Delete and View permissions, but not Edit.
Go back to the List permissions under List Settings and add the users to the list with the new permission set. Actually, from a best practices standpoint, add the users to a security group, and then add the group to the list with the appropriate permissions.
….you now have a list for taking timephased snapshots of project data. Each snapshot is time stamped, tagged with the project unique ID field and is 100% controllable by the project manager.