#ProjectServer 2010 reports in #MSWord using the #Excel Services REST API #PS2010 #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:

image

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:

image

A chart was then added based on the data from the Pivot table:

image

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:

image

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:

http://vm353/_vti_bin/ExcelRest.aspx/PWATEST/ProjectBICenter/Sample%20Reports/English%20(United%20States)/ProjectCostReport.xlsx/model/Charts(‘Chart 1’)

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

image

Click OK and you should see the chart appear as below:

image

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

Comments are closed.

Blog at WordPress.com.

Up ↑

%d bloggers like this: