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 my last post where I published an option / workaround for the last connected feature that has been removed from Project Online, this post will look at generating a Power BI report for the usage data that is captured. For those of you that missed the last post, see link below before continuing:

http://bit.ly/2dj9PpV

Now that we have some data captured in the list we can generate a report, for this example report I have used Power BI and Power Query but you could do something similar in Excel Power View with Power Query or even JavaScript / HTML if you really wanted to. In this post I will walkthrough all the steps needed to start creating the example report seen below:

Page 1 (PWAUsage):

Page 2 (PWAUserLastConnect):

So firstly we need to two URLs for the data used to generate this report, the first one is from the PWAUsageList, this assumes the list is called PWAUsageList, update the URL as needed:

<PWA URL>/_api/Web/Lists/GetByTitle('PWAUsageList')/Items()?$Select=WhoString,LogonDate,Browser,Id

The next one is from the Project OData Reporting API to return the active users that have an account:

<PWA URL>/_api/ProjectData/Resources()?$Filter=ResourceIsActive eq true and ResourceNTAccount ne null

Now launch Power Bi Desktop and click Get Data > OData Feed:

Enter the URL for the PWAUsageList as seen in the example above and click OK then click the Edit button on the preview window and the Query Editor window will open. Change the Query name from Query1 to something meaningful such as PWAUsage. Now change the LogonDate Column to Date rather than Date/TIme by right clicking on the column heading > Change Type > Date:

Also rename any columns as needed and remove the duplicate ID column, I renamed LogonDate to Logon Date and WhoString to Username:

Now we will add a custom column to this query to get the “Last Refresh” date. To do this click the “Add Column” ribbon then “Add Custom Column” and complete the details:

Click OK and the dataset will update:

Now we need to add the Resources data source, in the query editor click Home > New Source > OData Feed and enter the Resources OData URL as seen in the example above and click OK then click OK on the data preview window. Now click Choose Columns, the only column needed is ResourceName:

Click OK. The ResourceName column was then renamed and the Query renamed too:

Now we need to create a third query table that contains data from the PWAUsage query and the ActivePWAUsers query to show the last logon time for each PWA User. We could keep just the two queries and merge the two without creating a third table but that is up to you, you would just chose the Merge Queries option if you wanted to have just two queries. For this post I will create a third dataset query. In the query editor click the Merge Queries > Merge Queries as New option:

ActivePWAUsers in the top table and PWAUsage is the bottom table, select Resource Name in the top section and Username in the bottom section. The default join kind is fine for this merge:

Click OK. Update the query name to something meaningful such as PWALastLogon.

Click the Expand button next to the NewColumn:

Change the radio button to Aggregate and then click the dropdown next to Logon Date and select Maximum, don’t select anything else:

Uncheck the option “Use original column name as prefix” then click OK:

Rename the new column to remove the “Max of” and call the column “Latest Logon Date”

We now have the three queries ready to load into the report:

Now click Close & Apply > Close & Apply from the Home ribbon menu and the report will show your queries available:

The first thing to do will be to ensure the relationships between the three datasets is correct. Click the relationships button from the left navigation and update as needed:

All I had to change was to join PWAUsage to ActivePWAUsers by dragging Username from PWAUsage to Resource Name in ActivePWAUsers.

Now click back to the report view and we are ready to design the report. Now we have the datasets ready, in part 2 we will look at creating and designing the report, for now ensure you save the Power BI report for later when we look at designing the report.