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

image

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

image

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

image

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

image

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.

REST API

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:

http://project.contoso.com/PWA/_vti_bin/ExcelRest.aspx/ProjectBICenter/Sample%20Reports/Resource%20Report.xlsx/Model/Charts(‘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.

image

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.

image

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

About these ads

About Andrew Lavinsky

I am a consultant with the UMT Consulting Group, one of the premiere project portfolio management consulting companies in the world, galaxy, or universe for all I know.
This entry was posted in BI, Reporting and tagged . Bookmark the permalink.

5 Responses to Project Server Business Intelligence Resources (Part 2)

  1. Pingback: Incorporating Project Server Data into Word Documents | Project Epistemology

  2. Pingback: TechEd 2012 Project Server BI Follow Up | Project Epistemology

  3. Pingback: SharePoint Conference Project Server BI Linkfest | Project Epistemology

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

  5. Pingback: Project Conference 2014 BI Linkfest | Project Epistemology

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