#ProjectServer 2010 reports in #MSWord using the #Excel Services REST API #PS2010 #SP2010

September 19, 2011 at 1:08 pm | Posted in Configuration, Customisation, Functionality, Reporting | 2 Comments
Tags: , , , ,

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

Advertisements

2 Comments »

RSS feed for comments on this post. TrackBack URI

  1. […] on from my previous post that demonstrated embedding Excel Services objects in Word using the Excel Services REST API, this […]

  2. […] REST API and Project Server has been covered both here and here.  The general gist is that if you make note of the specific chart elements when […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: