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)

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.

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)

Service Pack 2 has been released for Office 2010, this includes Project Server 2010 and SharePoint 2010. For links to all Office 2010 SP2 updates see the office update site below:

http://technet.microsoft.com/en-us/office/ee748587.aspx

Project 2010 SP2: http://support.microsoft.com/kb/2687457

Project Server 2010 SP2: http://support.microsoft.com/kb/2687452

SharePoint 2010 SP2: http://support.microsoft.com/kb/2687453

For a full list of fixes, download the following Excel file:

http://download.microsoft.com/download/9/5/9/959F3A24-80B3-4930-8FF8-D3C631BB878F/Microsoft%20Office%20and%20SharePoint%202010%20Service%20Pack%202%20Changes.xlsx

For more information see the following blog post:

http://blogs.technet.com/b/projectsupport/archive/2013/07/23/project-2010-and-project-server-2010-service-pack-2-sp2-released.aspx

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 query that has been asked by several of our clients in the past is: “The last modified date field in PWA is not updating”. In most scenarios the Last Published and Last Modified fields will match in the Project Center as shown below:

This is because the Project Center views look at the published data and both these fields will get updated with a publish. If you modify (save but not publish) a project from Project Professional or PWA the Last Modified date will not update which may cause confusion. If you click the project to go to the project details you will see the status bar does show the correct Last Modified date:

Incorrect in the Project Center view:

There are scenarios where the Last Modified date will update and show a later date than the Last Published date, for example if you modify the project information then save the project:

To avoid any confusion to PWA users I would recommend not displaying the Last Modified date in Project Center views.

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 came across an issue a while back and meant to blog about it but forgot until a colleague of mine today mentioned the same issue. This jogged my memory of the fix so I thought it was a good time to write the post. The issue isn’t Project Server related but the reports and queries we were creating were for Project Server.

If your SQL query in an Excel file uses temporary tables Excel will throw an error like the one seen below:

For the search engines the error is below:

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator. Make sure the external database is available and hasn’t been moved or reorganized, then try the operation again.

The error will occur if you have the select statement in the connection file definition command text or even calling a SQL stored procedure from the command text. The same fix applied to both, at the start of the select statement add “SET NOCOUNT ON” as shown below:

Now Excel will execute the query and return the data 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)

A question I see and hear quite frequently is “Where has Manage My Alerts options gone in 2013?”. These have now moved the the PWA Settings page:

The two links for Manage My Alerts and Reminders and Manage My Resources’ Alerts and Reminders only appear once the SMPT server details have been enabled for the PWA instance. When the PWA notifications are removed from the PWA instance the two links are removed:

To enable notifications and enter the SMTP details, navigate to the Project Server Service Application in Central Admin, click Manage from the drop down menu next to the PWA site:

Enable the notifications and enter the SMTP server details on the Alerts and Reminders page. Once set, the two Manage Alerts links will appear on the PWA Sever Settings page under the Personal Settings heading. These links are permission controlled though.

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)

Just a quick post to highlight a limitation / issue with Excel Pivot Tables that I hadn’t seen before, details below.

This particular Pivot Table uses a Data Model that contains two OData feeds from Project Server 2013, one for Project details and one for Task details. The connections can been seen below:

The relationship has been set up as follows:

I have added Programme, Project Name, Task Name and Task Start to the Pivot Table. Programme and Project Name are from the Projects table and Task Name and Task Start are from the Tasks table. The Project Name is filtered to just one project:

Looking at the Pivot Table, it looks the CPS Test project has many tasks but in fact this project only has 4 tasks:

To resolve the issue you have to add a numerical field from the Tasks table to correct the aggregation:

Hope that helps