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.
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.