#ProjectServer 2010 reports in #MSWord using the #Excel Services REST API #PS2010 #SP2010September 19, 2011 at 1:08 pm | Posted in Configuration, Customisation, Functionality, Reporting | 2 Comments
Tags: Project 2010, Project Server 2010, PS2010, SharePoint 2010, SP2010
I have put together a quick example to show how easily and quickly a dynamic report can be created in Microsoft Word. You could easily create monthly reports for Project Sponsors, directors etc. In this example I have just created a simple Project Cost report to show the concept. The report can be seen below:
The steps to create this example can be seen below.
Firstly create the required chart in Excel using the relevant ODC file, in this example the OLAPPortfolioAnalyzer ODC file was used to create the Pivot Table below:
A chart was then added based on the data from the Pivot table:
We now need to publish this report to the SharePoint document library, in this case it will be published to the Sample Reports library. Below is the report in Excel Services:
Now we need to get the URL for the Excel Services REST API URL for the Chart, notice in the Excel Screen the chart is called Chart 1, this is key. The URL we need in this example is:
Open Microsoft Word, add the text required in the report then click Insert > Quick Parts > Field. Select IncludePicture from the field names, paste the URL into the Fieldname or URL box and check the Data not stored with document check box
Click OK and you should see the chart appear as below:
This chart will update every time the Word document is opened, providing the URL is accessible and data refresh is allowed from REST on the trusted file location for the Excel Services report.
This is just a basic example of what can be done to demonstrate other possibilities for creating Project Server reports. For more information on the Excel Services REST API please see the following MSDN article: http://msdn.microsoft.com/en-us/library/ff640648.aspx#restapi