Tags: Office 2013, Office365, Project 2013, Project Online, Project Server 2013
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:
Firstly add the required ODATA feeds into the Excel workbook as described in the previous post (link above), you can see my connections below:
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:
Do the same on the ProjectBaselines table but using the following formula:
Now Click the Design Tab > Manage Relationships and set up the following relationships:
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:
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:
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:
Select “Running Total In” and choose the Total Date Field:
Click OK. Repeat this for the 3 other cumulative fields.
Now create charts from both Pivot Tables and copy the charts to the Charts sheet:
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!):
As you can see above, I have also added a slicer to enable filtering, in this example we can filter by Programme.
Tags: Office365, Project 2013, Project Online, Project Server 2013, PS2013, SharePoint 2013, SP2013
When creating new site templates in SharePoint 2013, ensure you check the box to include content. If you don’t do this, any sites created from your new template will have the “Change the look” functionality missing, it will load a blank page as shown below:
To resolve this, include the content when saving the new site template. Ensure that there is no test data on the lists / libraries etc.
Include content enabled:
After creating a site from this new template, the “Change the look” functionality will work as expected: