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

September 16, 2013 at 8:51 pm | Posted in Administration, Configuration, Functionality, Information, Reporting | 4 Comments
Tags: , , , ,

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:

https://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.

#ProjectServer #PS2013 / #SharePoint #SP2013 site template issue

September 4, 2013 at 10:02 am | Posted in Administration, Configuration, Customisation, Functionality, Information, Issue, Workarounds | 2 Comments
Tags: , , , , , ,

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:

image

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:

image

After creating a site from this new template, the “Change the look” functionality will work as expected:

image

Blog at WordPress.com.
Entries and comments feeds.