This is a topic I’ve briefly played with before – how to automatically incorporate Project Server data into Office documents. The REST API has long been the go to tool for doing such things, as the API allows us to consume Excel reports dynamically in Word or Powerpoint documents.
The general goal in adding this sort of document automation is to attempt to drive user behavior a bit. We want our users to move away from generating documents on their C drive and generating them within SharePoint. Adding document automation is on of the major incentives to do so, as it makes it easier to create a document within SharePoint than on the local drive.
In this post, I want to take that integration to the next level, i.e. I want to incorporate Project Server fields within the text itself of a Word document. I might do this to automate some of my status reporting, or to create a project charter template that automatically populates with key data.
Now, I’ll point out that this technique is not quite seamless, and probably could benefit from a little custom SharePoint development….but hopefully this may give you some ideas, and be referenced in other, more robust solutions. I also haven’t quite figured out how to deploy this across multiple sites without custom development. So for now, take the solution for what it is, a rough idea for automating documents at the workspace level. If someone smarter than I wants to take this idea and run with it….by all means, be my guest.
Building the External Content Type
The first step in accomplishing this is to build an external content type (instructions here). When building the external content type, make sure to add a filter on the project name (instructions here).
Building the Document Template
Now let’s go create our document template. To do this we need a Project Workspace. It doesn’t have to be a live project workspace as really all we need is a document library. We’re going to add our ECT to the document library as a custom field.
First, navigate to the Library Settings.
Scroll down and add a new column.
I then configure the new column as below – using the name of the ECT that I created in one of the previous posts.
Now let’s associate that field with the default content type. To do this, we need to enable content type management within the document library. Click on the advanced settings to do this.
Enable content type management for the library.
Confirm that the field has been associated with the default document content type. Once we’ve done that, lets create a new document from the workspace. (You could also take an existing document template and load it to the document library and then adapt it to our purposes.)
Note how the Document Information Panel now shows a number of blank fields.
These fields will be populated from the project header data in the document library. More importantly, we can consume those fields within the document using the Quick Part mechanism.
I’ll go ahead and create a quick status report leveraging the Quick Parts.
Now let’s save the document back to the workspace. For good measure, I’ll save another copy off to my desktop as a template file.
Once the file has been posted back to the SharePoint site, test it out by selecting the option to Edit Properties.
Type the project name and hit the search button….
…and the metadata is now populated. Open the document to see the changes.
Yes….I know it’s kind of annoying to have to open the document and then close it and edit the metadata column to get it to work properly. I am sure there’s a decent solution to that, but for now, I’ll leave it there.
Associating the Document Template
The next step here might be to associate our validated document template with the content type on the workspace. To do that, I go back into our document library and upload the template to the content type.
…and now I can create a document within the document library, save it, add the metadata and know that everything is properly updated.
As I said above, the entire process is perhaps a bit contrived, but hopefully this should serve as a launching point for ideas. Other options that might be a bit easier (depending on your particular skill set) would be to simply write VBA within Word to query Project Server data and populate fields correctly.
Also, consider combining this technique with the concept of document sets. In that case, you would only have to apply the fields once, and then have all of the changes trickle down to the document set components. Please see this post from Alex Burton on leveraging document sets with Project Server: http://epmsource.com/tag/document-sets/.
Note that this post was inspired by (yet another) highly informative and entertaining presentation from Mr. Tom Resing, now of Rackspace. His presentations have become a highlight of the annual Houston SharePoint Saturday event.
Update (5/11): Tom’s presentation was indeed informative and entertaining, but after looking at my notes, Mike Huguet, PFE Extraordinaire, was the one who actually presented on customizing Word documents at the SharePoint Saturday event. Just wanted to clear that confusion up. Now I can rest at night.