SharePoint (and Project Server) Shenanigans

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:

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])

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:

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:

Work:

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.

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)

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:

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)

I have been meaning to publish this PowerShell script for a few months but finally got around to it today. The PowerShell script creates a project from a template then updates the Enterprise Project Type (EPT). I did write a post over a year ago on how to create a project using PowerShell but this didn’t update the EPT:

http://pwmather.wordpress.com/2012/01/17/create-and-publish-a-project-in-projectserver-via-powershell-ps2010-msproject-sp2010/

The issue is that even if your Project Plan Template is associated to an EPT in Project Server, the project created from the CreateProjectFromTemplate method is associated to the default EPT regardless. This script will prompt for the project name, the project plan template name and the EPT name. It will create the project then update the project to associate it to the desired EPT. The script can be downloaded from the script gallery below:

http://gallery.technet.microsoft.com/scriptcenter/Create-Server-2010-2013-19bd3cc7

Once downloaded, the script will need to be updated with the correct PWA URL, the lines that need updating are 9 and 23. These can bee seen below:

Update the http://vm753/pwa part of the URL for your PWA URL.

A walkthrough using the script is below.

Open Windows PowerShell and navigate to the PowerShell file, in this example it is on the desktop:

Press Enter and complete the details:

The new Project will be called “CPS Test Project by Paul Mather”, it will use the “PMTemp1” template and then associate the new project to the “PM TEST” EPT.

The project is created as you can see in the Project Center:

It is also associated to the specified EPT:

The default EPT on this PWA instance is “Enterprise Project”:

When using the CreateProjectFromTemplate method only, this new project would have been associated to the Enterprise Project EPT as this is the default EPT.

This is just an example of what can be done, many improvements could be made, certainly around error handling but it provides a working example for you to build on.

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)

I haven’t posted for a while due to work commitments and just generally being too busy – I am hoping this will change after the summer!

Anyway, just a quick post today to let you know that I have tested some of the key PowerShell scripts I wrote for Project Server 2010 on Project Server 2013. These also work for Project Server 2013. I have since updated the script gallery and associated blogs posts for these scripts listed below:

Publish All Projects:
http://gallery.technet.microsoft.com/scriptcenter/Server-2010-Publish-all-45ba385b

Publish Specified Projects:
http://gallery.technet.microsoft.com/scriptcenter/Server-2010-Publish-537857d8

High-level Audit example:
http://gallery.technet.microsoft.com/scriptcenter/Server-2010-High-level-e9c6ad09

Updating Project Server Lookup Tables:
http://gallery.technet.microsoft.com/scriptcenter/Update-Server-Lookup-table-bb1ae14f

My other scripts are available for download here:
http://gallery.technet.microsoft.com/scriptcenter/site/search?f%5B0%5D.Type=User&f%5B0%5D.Value=PWMather

Look out for more scripts coming soon.

Enjoy

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)

The Office 2013 August 2013 Cumulative Updates are now available, please see the links below:

http://support.microsoft.com/kb/2873346

Project Server 2013 Server Roll up package August 2013 CU (Recommended): 
http://support.microsoft.com/kb/2817615
Project Server 2013 August 2013 CU (Included in the Server Roll up package):
http://support.microsoft.com/kb/2817508
Project 2013 August 2013 CU:
http://support.microsoft.com/kb/2817502

Also worth noting, if you haven’t done so already, install the March 2013 Public update: http://support.microsoft.com/kb/2768001 if installing the August 2013 CU.

The Office 2010 August 2013 Cumulative Updates are now available, please see the links below:

http://support.microsoft.com/kb/2869757

Project Server 2010 Server Roll up package August 2013 CU (Recommended):
http://support.microsoft.com/kb/2817573
Project Server 2010 August 2013 CU (Included in the Server Roll up package): 
http://support.microsoft.com/kb/2817580 & http://support.microsoft.com/kb/2817587
Project 2010 August 2013 CU:
http://support.microsoft.com/kb/2817584
Remember SP1 or SP2 is a pre-requisite for the Office 2010 August 2013 CUs.

For more details please see:

http://blogs.technet.com/b/projectsupport/archive/2013/08/13/microsoft-project-server-2010-and-2013-august-2013-cu-announcement.aspx

As always, test these updates on a replica test environment before deploying to production

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)

A common requirement for clients is to have a report that highlights key information about each project. This usually includes any key project level fields, up and coming tasks / milestones and any active issues or risks. For previous versions of Project Server, in my opinion, the best reporting technology for this was SQL Server Reporting Services (SSRS). For Project Server 2013 on-premise installations SSRS is probably still the best option for most reports due to the flexibility around formatting, layout etc. For Project Online, unfortunately SSRS is not an option, a nice option for a project highlight type report is Power View. This blog post will walkthrough creating a project highlight report using OData and Power View for Project Online. This example report contains key project level data, key milestones due to complete this month, future key milestones, active issues and active risks. The OData feeds used are:

https://<serverurl>/pwa/_api/ProjectData/Projects()?$filter=ProjectType ne 7&$select=ProjectId,ProjectName,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectWork,ProjectCost,TotalCost,ProjectQuality,ProjectSchedule,ProjectCost,ProjectResource
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-08-01T00:00:00′ and TaskFinishDate lt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Issues()?$filter=Status eq ‘(1) Active’&$select=ProjectId,IssueId,Title,Discussion,Resolution,Owner,DueDate
https://<serverurl>/pwa/_api/ProjectData/Risks()?$filter=Status eq ‘(1) Active’&$select=ProjectId,RiskId,Title,MitigationPlan,ContingencyPlan,Owner,DueDate

These feeds need to be added to Excel 2013 using the “From OData Data Feed” option found on the DATA tab:

Paste the feed URL into the Link or File field:

Click Next

Select the Projects table:

Click Next

Click Finish

Select Only Create Connection and Click OK

Repeat these steps to add the other OData feeds using the same steps.

Once all of the feeds have been added, the table relationships need to be created. On the DATA tab in Excel click Relationships. This can be found in the Data Tools group.

Click New

Set up the relationships required, for this example the relationships are:

Click Close

On the INSERT tab click Power View

Design the report as required, in this example we are using Project Name as a slicer to filter the data on the other tables, this can be seen below:

Once the report layout meets your requirements, save this to Project Online. The report can then be accessed and refreshed in the browser:

Clicking a different project on the slicer will filter the data:

Enjoy

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)

Quite a nice personal touch for a PWA instance is to add the company logo as the background image. This is very simple to do for both 2010 and 2013, 2013 is even easier than 2010.

Firstly we will look at 2010, the quickest way to add an image to the PWA homepage is to use CSS, an example is below:

Update the image URL to the correct image, save the CSS in notepad or preferred editor, for this example I called the file backimage.inc. Then upload the file to PWA, in this example it was uploaded to the Shared Documents library. Edit the PWA homepage and add a content editor web part, then link to the backimage.inc file:

Apply the change and click OK, then save the page and you will see your image set as the background:

For 2013 you just click Change the Look from the settings cog, click current:

Click Change and browse to the image and add. Click Try it out, if you are happy with the image, click Yes keep it:

This will add the image to all pages in 2013.