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)

In this post we will look at creating a very basic Power BI report that includes hyperlinks to various entities in Microsoft’s Office 365 PPM Project Online PWA site. The report created here will be purely to show you how to create links to the Project Site, Project Detail Page, Issues and Risks items.

Firstly I will create my OData URLs I need for my datasets, these can be seen below:

• Projects: <PWA site URL>/_api/ProjectData/Projects()?$filter=ProjectType ne 7
• Issues: <PWA site URL>/_api/ProjectData/Issues
• Risks: <PWA site URL>/_api/ProjectData/Risks

Now launch the Power BI desktop and click Get Data > OData Feed as seen below:

Enter the Projects URL and click OK:

On the next screen click Edit:

In the Query Editor window click Choose Columns:

In the Choose Columns window, uncheck all of the columns – quickest way is the uncheck Select All Columns:

Now select the following columns: ProjectId, ProjectName, ProjectWorkspaceInternalUrl

Click OK and the query will only contain those columns:

Rename ProjectName to “Project Name” and ProjectWorkspaceInternalUrl to “Project Site URL”, this is done by right clicking on the column heading and selecting Rename:

I have also changed the query name to Projects:

Now click Add Column > Add Custom Column:

Enter the name “Project Link” and the formula:

"<PWA site URL>/project%20detail%20pages/schedule.aspx?projuid=" & [ProjectId]

Update the PWA site URL to your Project Online PWA site URL and update the PDP if required, this one uses the Schedule PDP.

Click OK. The Projects dataset table is now ready, we now need to add in the Issues data. From within the Query Editor window, click New Source > OData Feed:

Enter the Issues URL and click OK:

Click OK on the preview window then you will see the 2nd query added:

Now we need to Choose Columns, remove them all and select the following: ProjectId, IssueId, ItemRelativeUrlPath, Title

Click OK. Rename the query to Issues then that dataset table is complete:

We now need to add in the Risks data. From within the Query Editor window, click New Source > OData Feed:

Enter the Risks URL and click OK, click OK on the preview window then you will see the 3rd query added:

Now we need to Choose Columns, remove them all and select the following: ProjectId, RiskId, ItemRelativeUrlPath, Title

Click OK. Rename the query to Risks then that dataset table is complete:

Now we need to merge the Issues table with the Projects table so that we can get the Project Site URL in the the Issues table. Select the Issues dataset table then click the Merge Queries option:

In the Merge window select the Projects table and ProjectId from both as seen below:

Click OK and you will see a new column appear:

Click the button on the new column to choose the columns to expand from the joined table:

Select just the Project Site URL:

Click OK and you will see the Project Site URL appear:

Rename the column to remove the NewColumn. prefix. Now click Add Column > Add Custom Column:

Enter a name “Issue Link” then enter the formula for the new column:

[Project Site URL] & "/" & Text.Range([ItemRelativeUrlPath],0,13) & "DispForm.aspx?ID=" & Text.Range([ItemRelativeUrlPath],13,1)

Click OK. Click on a row and ensure the URL is correct – copy in the browser to test:

Now select the Risks dataset table to repeat the steps. Click Merge Queries with the same steps as before and click OK. In the new column expand the Project Site URL then rename the column to remove the NewColumn. prefix. Add the custom column called “Risk Link” with the following formula:

[Project Site URL] & "/" & Text.Range([ItemRelativeUrlPath],0,12) & "DispForm.aspx?ID=" & Text.Range([ItemRelativeUrlPath],12,1)

Click OK then click on a row and ensure the URL is correct – copy in the browser to test:

Then click Close & Apply:

Then the Apply Query Changes window will load whilst the data model loads:

Once loaded, check the table relationships are ok and change if needed:

Now you are ready to build the Project links report as you would normally build a Power BI report. One additional step that you will need to do it make the URL columns into Web Urls. For example, select the “Project Site URL” from the Projects dataset then click the Modelling ribbon and under the Data Category option chose Web URL:

Repeat this for the three custom columns created: Issue Link, Risk Link and Project Link.

A very simple report has been created below to show the hyperlinks:

This is a very plain report as you can see, but this demonstrates how to create hyperlinks to keys areas of the project from a Power BI report.