Project Server Business Intelligence Resources (Part 2)

Thanks again to all of the folks who attended the sessions I presented with Mike McLean at the Project Conference.  This post is a follow up to those presentations, and includes links to additional reference material as well as links to instructions on how to recreate the scenarios we demonstrated.

Feel free to add feedback at the bottom…  I’ll make sure that it all gets shared with Mike.

Note that the Excel reports listed below depend on the ODC files that I mentioned in the last post.

I also point out that if you’re deploying Excel Services, chances are that you’ll eventually end up on this post from Brian Smith listing common configuration errors.  I figured I’d save you all the trouble and just provide it here.

Excel Services


1. Developing a Portfolio Stage Gate Report with Excel Services. (Part 1 & Part 2).  Probably a required report for most organizations.


2. Developing a Baseline Execution Index Report (Part 1 & Part 2) – this is a great report for showing velocity over time – and work backlogs.


3. The Cumulative Milestone Report. (Part 1 & Part 2)


4. Cumulative Task Completion.  I’m not providing instructions on this one, but did throw the query into the ODC file that can be downloaded above.  Once you get the data into Excel, it’s just a matter of setting the PivotTable to show cumulative numbers and you’re good to go.


The 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 they’re posted in Excel Services, you can generate a URL that will go into the Excel report and generate a view of the chart or table.

In a report called Resource Report posted to the demo Contoso library, you would end up with a URL that looks like this:‘Chart%201’)

Note how the spaces are swapped for the %20 code.  Here’s a more technical overview with links to more information.  You can even pass parameters through the REST API – for instance, pushing a resource name into a calculated workbook and displaying the results.

The first two links in this section talk about how to embed a REST-generated chart in a Word document.  One of the tricks I learned for this presentation however, was how to enable a dynamically updated chart in PowerPoint:

Once you have the URL defined.  Test it out in the browser.  Confirm that the chart appears.


Open PowerPoint.  Select the option to Insert a Picture.  Paste the URL in the dialog box.

Here’s the trick.  Instead of clicking Insert, hit the drop down.


Link to a File will cause the image not to render if the deck is opened offline.  Insert and Link will actually insert an image, but will refresh it if the deck is opened while connected to the network.

Next up….PerformancePoint and VBA for Trend Analysis

Project Server Business Intelligence Resources (Part 2)

5 thoughts on “Project Server Business Intelligence Resources (Part 2)

  1. Ray says:

    I have always been impressed by materials by Mr. Lavinsky I have come across. He might well be one of the premiere project portfolio management consultants in the world, galaxy, or universe.

Leave a Reply

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

You are commenting using your 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