EPM Team
The Catapult EPM team is responsible for delivering Microsoft project and portfolio management solutions. Their blog reflects team insights and real-world experiences developing world-class EPM solutions.

Document Automation with the REST.API   

Tags: Project Server 2010, SharePoint
Technorati Tags: ,

One of the top client requests I often see is document automation.  Many clients have bits and pieces of reusable content that they wish to automatically populate into PowerPoint or Word documents.  Ira Brown of Project Widgets did an impressive webinar the other day on using macros to automatically move data from MPP files to Office files.  Continuing on the BI thread this week, here’s how you can use the REST.API in SharePoint 2010 to easily implement a good portion of this functionality.

The REST.API has been well documented, and good instructions exist in a number of posts (here and here).  In fact, the folks over at Bamboo have even created some free code to enable REST in SharePoint 2007.  Let’s look at how it can be applied to some of the out of the box reports in Project Server 2010 with SharePoint 2010.

How To

First, let’s take a look at the out of the box reports.  In this case, I have blurred out two custom reports that have been developed for demonstration purposes.


Opening one up in Excel we can see that it already has data, but that the data ranges have not been named and chart is simply labeled “Chart 1.” 


Let’s rectify that.  Highlight the data range, select Ctrl-F3, and name the range as appropriate.  Note by the way that a range definition by default is not dynamic.  Should the range change size, as would happen as the number of projects increase, the range will have to be manually adjusted – or redefined dynamically (instructions here).


…and now we will rename the chart.


So now we have descriptive names on the worksheet items, we save it back to our document library.  I am prefixing “Demo” to the file name, so that the new file is called DemoTopProjects.xlsx.

Following the instructions here, we will develop the REST URLs for the worksheet elements.  The URLs will look something like this:

Document URL: http://servername/PWA/ProjectBICenter/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/DemoTopProjects.xlsx

Chart URL - http://servername/PWA/_vti_bin/ExcelRest.aspx/ProjectBICenter/Sample%20Reports/English%20(United%20States)/DemoTopProjects.xlsx/Model/Charts('ProjCost')

Range URL: http://servername/PWA/_vti_bin/ExcelRest.aspx/ProjectBICenter/Sample%20Reports/English%20(United%20States)/DemoTopProjects.xlsx/Model/Ranges('ProjCost')


Test to ensure that the URLs work.  To do this, paste them into your browser and see what comes up.


With the magic URL's, we can now do some interesting things:

We can embed the picture in a Content Editor Webpart.


We can paste the link as a picture in a PowerPoint document:


And for the grand finale, let’s see what we can do in Word….  Here we paste the link as a Picture Reference using the Insert Quick Part option from the Ribbon.  Note that you can select to have the chart dynamically refresh on document open or not…  After inserting the chart, we can insert the range that we named below it, also using the Insert Quick Part option.


Yep, dynamically linked content in a Word document….  For the detailed instructions, please refer to the Excel Product Team blog here.

Posted by  Andrew Lavinsky  on  1/12/2010
1  Comment  |  Trackback Url  | 0  Links to this post | Bookmark this post with:        

Links to this post

Pingback from  Defining an Update Methodology (Part III) | Project Epistemology  on  12/22/2011  5:02 AM

Pingback from  IT Ramblings | What-If Analysis with Microsoft Project  on  11/5/2013  2:31 PM

Pingback from  Conditionally formatting bar styles in Project 2010 | CL-UAT  on  12/28/2014  3:00 AM

Pingback from  Conditionally formatting bar styles in Project 2010 | DL-UAT  on  3/9/2015  12:48 PM


Andrew  commented on  Wednesday, October 06, 2010  7:34 AM 
Messing with REST.API last night and saw this post on handling Excel name exceptions here. Figured I'd add it to the comments for posterity: http://blogs.msdn.com/b/cumgranosalis/archive/2010/10/05/how-to-treat-spaces-single-quotes-in-sheet-names-in-rest.aspx

Name *:

CAPTCHA Image Validation