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)

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:

The URL I used was: https://TenantURL/sites/PSP/Acquisition%20Target%20Analysis/_api/web/lists/GetByTitle(‘Issues’)/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:

Click Edit to load the query editor.

Rename the query to something meaningful;

Access the Advanced Editor:

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

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:

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:

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:

Click OK and click Choose Columns:

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

Click OK and rename the Query to something meaningful:

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

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:

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:

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

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

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

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

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.