#ProjectServer and #SharePoint 2010 / 2013 / 2016 January 2016 Cumulative Update #PS2010 #SP2010 #PS2013 #SP2013 #MSProject

January 12, 2016 at 8:40 pm | Posted in Administration, CU, Fixes, Functionality, Information | Leave a comment
Tags: , , , , , , , , ,

The Office 2016 January 2016 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/3131245

Project 2016 January 2016 update:
https://support.microsoft.com/en-us/kb/3114538

The Office 2013 January 2016 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/3131245

Project Server 2013 January 2016 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/3114493

Project Server 2013 January 2016 update:
https://support.microsoft.com/en-us/kb/3114507

Project 2013 January 2016 update:
https://support.microsoft.com/en-us/kb/3114510

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the January 2016 CU.

The Office 2010 January 2016 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/3131245

Project Server 2010 January 2016 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/3114556

Project Server 2010 January 2016 update:
https://support.microsoft.com/en-us/kb/3085610

Project 2010 January 2016 update:
<no update this month>

SP2 is a pre-requisite for the Office 2010 January 2016 updates.

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

Want to query cross #project site #SharePoint lists in #ProjectOnline / #ProjectServer ? #PowerBI #PowerQuery #BI #Office365 #Excel #PPM

January 5, 2016 at 3:00 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , , ,

As you may know or not know, creating a report that contains data from a custom SharePoint list from all Project sites, for example a Lesson Learned list or Change list is not that simple. The default lists for Issues and Risks are simple as the data is synchronised to the reporting schema and available in the Project OData reporting API. In this blog post I will show you a simple example using Power Query to access the list data and create a simple cross project report consuming data from the SharePoint list APIs on the Project Sites.

In this example I use the Issues list but the same principle will apply to any SharePoint lists on the project sites. I have used Power BI Desktop to author this report but the same would work in Excel using Power Query. First load Power BI Desktop and click Get Data >  ODATA Feed and enter the REST API for the list using an example Project Site:

image

The URL I used was: https://TenantURL/sites/PSP/Acquisition%20Target%20Analysis/_api/web/lists/GetByTitle(‘Issues&#8217;)/Items()?$Select=Title,Priority

In this example I am only getting the item title and priority from the Issues list. To use a different list update GetByTitle(‘Issues’) with the name of your list and then update the Select to include the correct columns.

Click OK and you will see the data from this list:

image

Click Edit to load the query editor.

image

Rename the query to something meaningful;

image

Access the Advanced Editor:

image

Change the Power Query code to add in the following code to turn this into a parameterised function:

image

The top and bottom lines were added and the OData.Feed URL updated the remove the reference to the example site we used to load the initial data, this was replaced with the site parameter. Click Done and you will see the following:

image

Don’t invoke the function as this will edit the code. Now we need to add in the query to get the list of projects. So within the query editor on the Home tab click New Source > ODATA Feed and enter the URL to get you list of Projects and Project Site URLS:

image

The URL I used was: https://TenantURL/sites/PSP/_api/ProjectData/Projects()?$Filter=ProjectType ne 7

This filters out the Timesheet row, the properties will be selected in the next step. Click OK to load the data:

image

Click OK and click Choose Columns:

image

I only selected ProjectName and ProjectWorkspaceInternalUrl, other columns can be added but only these were required for this example:

image

Click OK and rename the Query to something meaningful:

image

We now need to add in a custom column to call the function. Click Add Column > Add Custom Column and enter the following:

image

So above we are calling the LoadIssueData function and passing in the ProjectWorkspaceInternalUrl. This will be used in the site parameter value. Click OK and the data will load:

image

The column needs to be expanded to show the data, click the expand button next to the column name and chose the columns to expand:

image

Click OK to load the data and expand the columns to see Title and Priority columns from the Issues list:

image

Now click Close & Apply from the Home tab to load the data model:

image

We can now create a report that displays the data, a simple table example can be seen below:

image

The expanded column can be renamed to something more meaningful, for example:

image

Depending on the number of projects / project sites the data load might take a while as it has to traverse all of the project sites and get the SharePoint data using the list REST APIs. Each Project requires a separate REST call to get the list data from the associated Project Site.

Hopefully that will open up your reporting options for getting data from those custom SharePoint lists or the Project sites or even if you want to use the default Issues and Risks list but have custom columns on those lists.

Create a free website or blog at WordPress.com.
Entries and comments feeds.