Home > Paul Mather, Work > #ProjectOnline #PowerBI report with hyperlinks #Office365 #PPM #SharePoint #PowerQuery #BI

#ProjectOnline #PowerBI report with hyperlinks #Office365 #PPM #SharePoint #PowerQuery #BI

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)

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:

image

Enter the Projects URL and click OK:

image

On the next screen click Edit:

image

In the Query Editor window click Choose Columns:

image

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

image

Now select the following columns: ProjectId, ProjectName, ProjectWorkspaceInternalUrl

Click OK and the query will only contain those columns:

image

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

image

I have also changed the query name to Projects:

image

Now click Add Column > Add Custom Column:

image

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.

image

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:

image

Enter the Issues URL and click OK:

image

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

image

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:

image

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

image

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

image

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:

image

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:

image

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

image

Click OK and you will see a new column appear:

image

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

image

Select just the Project Site URL:

image

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

image

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

image

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)

image

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

image

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)

image

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

image

Then click Close & Apply:

image

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

image

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

image

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:

image

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:

image

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.

Advertisements
Categories: Paul Mather, Work Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: