Home > Paul Mather, Work > #ProjectServer #ProjectOnline example report #PS2013 #SP2013 #Office365 #BI #Excel #PowerPivot

#ProjectServer #ProjectOnline example report #PS2013 #SP2013 #Office365 #BI #Excel #PowerPivot

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

Following on from a post I wrote last month where I detailed creating a highlight type report for Project Online using ODATA and Excel 2013 / Power View, below details another simple example for a project cost / work dashboard with cumulative totals. The highlight report post can be seen below:

http://pwmather.wordpress.com/2013/08/01/projectserver-projectonline-highlight-report-example-ps2013-sp2013-excel-bi-office/

Firstly add the required ODATA feeds into the Excel workbook as described in the previous post (link above), you can see my connections below:

image

The two Project feeds need to contain the correct fields – the fields you wants to see on the report. As a minimum the project baseline feed should contain ProjectId, Project Name, Project Baseline Finish Date, Project Baseline Cost, Project Baseline Work and the project feed ProjectId, Project Name, Project Finish Date, Project Cost, Project Work. The TimeSet feed should look like this:

ProjectData/TimeSet()?$filter=day(TimeByDay) eq 1 and TimeByDay gt datetime’2013-01-01T00:00:00′ and TimeByDay lt datetime’2018-01-01T00:00:00′&$select=TimeByDay

The date range can be changed to suit.

The next section uses PowerPivot to create the calculated field for the TimeSet feed join – this is required for the cumulative totals. Credit goes to Andrew Lavinsky for this method. Click the PowerPivot tab then Manage. On the Projects table, add a calculated field with the formula below and rename it to Total Date:

=[ProjectFinishDate]-Day([ProjectFinishDate])+1-TimeValue([ProjectFinishDate])

image

Do the same on the ProjectBaselines table but using the following formula:

=[ProjectBaselineFinishDate]-Day([ProjectBaselineFinishDate])+1-TimeValue([ProjectBaselineFinishDate])

Now Click the Design Tab > Manage Relationships and set up the following relationships:

image

Close PowerPivot and rename the first Excel sheet to Pivottables and add another sheet, call this Charts. On the Pivottables sheet insert 2 Pivot tables as shown below:

Cost:

image

Work:

image

Notice the cost, baseline cost, work and baseline work values have been added twice. Rename the duplicate values to Cumulative Work, Cost etc. as seen below:

image

Currently both values will show the same data as the running totals have not been set up, this is completed next using the Cumulative Cost as an example. Load the field settings and click the “Show Value As” tab:

image

Select “Running Total In” and choose the Total Date Field:

image

Click OK. Repeat this for the 3 other cumulative fields.

image

Now create charts from both Pivot Tables and copy the charts to the Charts sheet:

image

Due to the lack of data in my test instance, the charts don’t look that great but you get the idea!

With a bit of time and patience you can get the charts to look presentable (better than mine do anyway!):

image

As you can see above, I have also added a slicer to enable filtering, in this example we can filter by Programme.

Advertisements
Categories: Paul Mather, Work Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: