Archive

Posts Tagged ‘Project Server’

#ProjectOnline #Excel #PowerQuery Report #Office365 #PS2013 #BI #OData

January 20, 2015 Leave a comment
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)

Something I have been meaning to blog about for a while now is Power Query. There is nothing revolutionary in this post but hopefully it will give the readers some idea of how powerful Excel Power Query can be if they have not seen / used it before. We will only touch the surface here in this post but there is plenty of information available already.

So firstly, what is Power Query. In short, Power Query is an add in for Excel that enables you to work with many different data sources and transform that data as required. An intro can be seen here. Power Query is formula based but this is different formula syntax to what you use in Excel. The Power Query formulas are based on what was known a “M” language, an intro to the formulas can be found here.

Now we will look at creating a simple Excel Power Query report for Project Online using the OData API. The first report we will create will be a simple report that allows you to dynamically filter the data based on a text value in a cell. The dynamic filtering is useful when you are looking at large amounts of data such as time phased assignments in Project Online. The example we create below doesn’t really benefit from the dynamic filtering as the data set is small but this is only an example to show you how this works.

Get your Project ODATA url, for this example and am using the following:

https://<URL TENANT>/sites/pwa/_api/ProjectData/Projects()?$select=ProjectName,ProjectOwnerName,ProjectWork,ProjectCost,ProjectDuration,ProjectPercentCompleted,ProjectDescription,EnterpriseProjectTypeName

Launch Excel and click the Power Query tab then From Other Sources > From OData feed:

image

Paste in the URL:

image

Click OK. You will then be presented with an authentication window. Select “Organizational account” from the left hand side navigation and click Sign In. In the new window that appears enter the credentials for an account that has access to the ProjectData API.

Once authenticated you will see the Power Query Preview window load with the data:

image

You can see the formula used in the formula bar:

image

If you can’t see the formula bar, click the View tab and check the Formula bar check box:

image

We now have our Project Online data in Power Query ready to be used as a data source. In this example I have already selected the columns I wanted, but if you just used the Projects feed with no select (http://..pwa/_api/ProjectData/Projects()) you would see all of the columns displayed. At that point you can then easily chose which columns you would like in your data source by selecting the columns you don’t need then right click and chose Remove Columns.

image

Then you can see those columns have been removed. The formula bar has been updated with the latest steps:

image

You will also see another step appear in the applied steps, see it has the “Removed Columns” step:

image

At any point in time you can switch back to a previous step or even delete a step. If I click back on the source step I see the original data set with the two columns I deleted:

image

For the purpose of this post I will delete the “Removed Columns” step but before I do I want to show the Advanced Editor screen. This is available from the View tab:

image

This editor enables you to type the query yourself rather than using the UI menus. Some things you might want to do might not be possible from the UI and you are required to type / update the query. I will now delete the “Removed Columns” steps and you can see the query has been updated:

image

I can manually update the query update the data set, for example I can manually update the query to remove columns:

image

You will then see the data update and an applied step appear:

image

Now I am happy with my simple data set, but before I do anything I will update the query name to “Projects”:

image

Now chose “Close & Load To…” from the Home tab:

image

Then chose “Only create the connection” also check the check box to load the data to the data model:

image

You will then see the Excel sheet with the Workbook Queries window that allows you to preview the data:

image

Before we create the Excel report, I want be able to filter the data based on the Project Owner. Next insert a table like below:

image

Enter any Project Owner name for now, I used he MOD Administrator in this example as that user is the Project Owner for a few projects. Give the table a name, in this example I called it ProjectOwnerTable.

Now click the Power Query tab then “From Table”:

image

The Power Query editor will load with the following:

image

Switch to the advanced editor to see the query:

image

Update the query to change the type and select the record:

image

The record will then be displayed:

image

Now click the “Close & Load To…” from the Home tab and chose the “Only create connection” and check the check box to add the data to the data model.

Now load the first Projects query that was created in Power Query and switch to the advanced editor:

image

This needs to be modified to filter for the Project Owner in the new table that was created. See the updated query below:

image

You will need to enable the data in privacy option:

image

image

Click Close and load to save the changes in the Power Query editor and you will see the Excel worksheet:

image

Now you can create the Excel report, for this I will add a Pivot Table using an External Data source then selecting my data model:

image

Then update the pivot table with the fields:

image

Change the layout etc.

image

image

This is filtered to the projects where the owner is MOD Administrator, if I want to see other projects, update the Project Owner on row 2 and refresh the data:

image

image

A very quick and simple intro the Power Query for Excel with Project Online data using the ProjectData Odata API.

I hope to give more examples in the future.

Categories: Paul Mather, Work Tags:

#HappyNewYear everyone! 2014 #blog review / highlights #ProjectServer #ProjectOnline #Office365

January 1, 2015 Leave a comment
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)

Firstly Happy New Year to all, I think by now we are all in 2015! I hope that 2015 is a great year for all.

A quick review of my blog for 2014 can be seen below.

The blog was viewed around 90,000 times in 2014 with 618 views being the highest for 1 day with the most popular post that day being the Project Online Reporting Pack post.

The top five most visited posts in 2014 were as follows:

  1. http://bit.ly/1wEOs1z
  2. http://bit.ly/1yaU4HI
  3. http://bit.ly/1wEOs1C
  4. http://bit.ly/1pw7ROI
  5. http://bit.ly/1yaU4HL

I look forward to creating more Project / Project Online / Project Server related posts in 2015!

Don’t forget the biggest event in 2015 – Microsoft Ignite, May 4th – 8th 2015 in Chicago – it will be an awesome event – I can’t wait to be there!

Categories: Paul Mather, Work Tags:

#ProjectServer #PS2013 update reference #PPM #MSProject

December 22, 2014 Leave a comment
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)

A quick post to highlight a couple of great links from Brian Smith for Project Server 2013 / Project Pro 2013 updates / patches. See the links below:

Project 2013 client update reference: http://bit.ly/1AxUsiP

Project Server 2013 update reference: http://bit.ly/1AXa7pV

Bookmark these links today! Smile

Categories: Paul Mather, Work Tags:

#ProjectServer and #SharePoint 2010 / 2013 December 2014 Cumulative Update #PS2010 #SP2010 #PS2013 #SP2013 #MSProject

December 11, 2014 Leave a comment
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)

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

http://bit.ly/1IEypL4

Project Server 2013 December 2014 Server Roll up package:
http://bit.ly/1IEyrT3

Project Server 2013 December 2014 CU:
http://bit.ly/1IEyrT8

Project 2013 December 2014 CU:
http://bit.ly/1IEypLd

Also worth noting, if you haven’t done so already, install the March 2013 Public update: http://bit.ly/1lR8IgK or Service Pack 1 http://bit.ly/1uorn2C if installing the December 2014 CU.

The Office 2010 December 2014 Cumulative Updates are now available, please see the links below:

http://bit.ly/1IEyrTd

Project Server 2010 December 2014 Server Roll up package:
http://bit.ly/1IEyqi0

Project Server 2010 December 2014 CU:
http://bit.ly/1IEyqi2

Project 2010 December 2014 CU:
http://bit.ly/1uornQ2

SP2 is a pre-requisite for the Office 2010 December 2014 CUs.

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

For more details see: http://bit.ly/1uorniV

Categories: Paul Mather, Work Tags:

#ProjectOnline / #ProjectServer #Project Site Finder #JavaScript #jQuery #Office365 #SharePoint

December 1, 2014 Leave a comment
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)

This script will enable the users to quick find and navigate to the Project Site for a particular project. This is useful if you have many projects in the instance. The script can be downloaded from the script gallery below:

http://bit.ly/1rMPi1b

To get the script to work you will need to download the following jQuery library: jquery-2.1.1.min.js – jQuery download Another version of this library may work but this was the one I used / tested with. Upload this library to your PWA site collection then update the script file with the correct location. I uploaded this file to the site collection document library as you can see in the code below:

image

This example script does use the ProjectData API, so users will need access to that for this to work.

Once the script is downloaded, upload this to the PWA site collection, in this example it was uploaded to the site collection documents library. Choose where you want to script to be accessed, in this example I have placed it on the PWA homepage using a content editor web part:

image

The content link in my example is: /sites/pwa/SiteCollectionDocuments/Project Site Finder.js

Once loaded, the page will look like this:

image

The user can then type in part of the project name and click “Find Project”:

image

The Project Site ULR column will contain the associated project site URL with a clickable hyperlink that will open the project site in a new window / new tab.

This only uses intrinsic fields so should work for any Project Online environment but do test it thoroughly first. You might want to improve the error handling etc. before deploying to a production environment. Also remember this does require the user to have access to the ProjectData API for this to work.

The script is provided "As is" with no warranties etc.

Categories: Paul Mather, Work Tags:

#ProjectServer / #ProjectOnline error deleting PDP #PS2013

November 21, 2014 Leave a comment
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)

Just a quick post, if you ever receive the following error when trying to delete a Project Detail Page, ensure the PDP has been removed from all Enterprise Project Types (EPT):

image

The error message is:

“An event receiver has canceled the request.”

Removing the unwanted PDP from all the EPT’s resolved it for me.

Categories: Paul Mather, Work Tags:

#Webinar: Establish a Strategic #PMO with #Project Online by CPS and UMT360 #Office365 #MSProject

November 17, 2014 Leave a comment
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)

Join a live webinar hosted by CPS and UMT360 to understand the core concepts of strategic Project Management Office (PMO). PMOs are being asked to master the important, yet tactical aspects of project management as well as take strategic responsibility for managing investment decisions to optimize spend and maximize return across the portfolio.

Now is the time to embrace the full spectrum of responsibility to ultimately maximize ROI.

Understand how integrating UMT360 investment planning and controls across the PPM lifecycle in Project Online gives you and the business, the financial and strategic insights needed to optimize spend and maximize from your project portfolios.

Start Date: 19/11/2014 12:30 GMT

End Date: 19/11/2014 13:30 GMT

Location: Online. Free to attend

To register, see the link below:

http://bit.ly/1sXQRUC

For more details see:

http://bit.ly/1xwK8Ym

Categories: Paul Mather, Work Tags:
Follow

Get every new post delivered to your Inbox.

Join 1,720 other followers

%d bloggers like this: