Microsoft PowerBI is one of the best new BI app’s from Microsoft since SSRS (which still doesnt work in the cloud?!), however there is no out-of-the-box web parts available for Office365 or Project Online users unless of course you’re happy to publish your reports unauthenticated to the whole world by using the PowerBI embed option!
There is an add-in available (Power BI Tiles for SharePoint) on the SharePoint app store but if you just want a quick PowerBI report displayed I couldn’t find anything out there, so after reading about a great example of creating your own Provider hosted app in the following blog; Part 1: How to embed PowerBI in a SharePoint site, I thought that there must be an easier way.
Note: this solution only applies to Office 365 and Project Online, sorry on-prem people..
Screenshot of the finished product:
Overview of the Script
The script while short (scroll down for a sneak peak), does a lot!
- It will authenticate the user with Microsoft – normally this will take the users current logged in session so you won’t see the prompt
- On first load it will request permission to access PowerBI – this step only happens once per user and covers all web parts / reports configured
- Once it has a login token it will cache it for future requests (it’s valid for a few hours/days I think?)
- Now finally using the security token it will load a specific report in a iFrame displayed in a CEWP on the page
Additionally using an oDataFilter you can pass parameters like ProjectUid or similar to the report.
This solution requires the following:
- Office 365 / Project Online tenant
- An Azure AD application- but we don’t need anything fancy like dirsync setup (And note that every O365 tenant today has an Azure AD directory automatically created so there is no additional cost here)
- All users who will view the web part require at least the PowerBI free license in O365
- To setup this you’ll need permissions to configure Azure AD, so probably global admin in Office 365.
First up you’ll need to create an Azure AD Application in your tenant to allow permissions to view PowerBI reports for your users, to do so go here (don’t panic this is not a full blow SharePoint app we’re creating, it’s just a simple xml manifest that defines which permissions this script will be allowed!):
1. Register the Azure AD Application
Configure the following settings:
The most important setting is the Redirect URL, that must match your Office365 tenant name, and it could be a subsite but ADAL will allow any site below this URL to use the script.
Then make sure to pick the appropriate permissions, I’m going to go with everything for this example.
Finally click Register App, and it will create a Client ID (a GUID), save this we’ll need it later.
2. Update the Application registration to include Implicit Flow
However we now need to edit this app to enable this functionality, if you skip this step then when you try to use the script, you’ll see the following error:
ADAL error occurred: AADSTS70005: response_type 'token' is not enabled for the application Trace ID: ebe80ce0-8365-4f89-b2ee-d10218ffa595 Correlation ID: 491b009c-907b-4c8d-8605-efa34347ff47
So before you go further you’ll need to update the application manifest in Azure AD.
Firstly open https://portal.azure.com and login with your Office365 admin user credentials (note that using the old Management Portal will require a subscription, but you don’t now using the new portal if all you’re doing is editing Azure AD).
Now from the left menu select Azure Active Directory, then the default directory details should load, if not you’ll need to select the appropriate directory configured by your Admin. Click on App Registrations, then click on the app with the name you created in the previous step.
Now you can edit the Manifest from the top menu, click Manifest and you’ll need to just make one change:
By default the option oauth2AllowImplicitFlow (line 15 above) is set to false, change that to true by editing the value, then save the changes.
Okay, that was the hard bit.
Update Oct 2017: You will need to update your ReplyUrl in the manifest to include a wildcard on the end, that will be required if this webpart is installed on a PDP which has a URL: https://siteurl/PWA/Project%20Detail%20Pages/pdpname.asp?ProjUid=1324132412124 or similar. Obviously the ProjUid changes for each project, so a reply url of something like: ‘https://contoso.sharepoint.com/*’ or more specifically (for better security) ‘https://contoso.sharepoint.com/sites/pwa/Project%20Detail%20Pages/*’.
3. Publish your PowerBI report and get the Guid
The script I’ve put together is pretty simple, it just authenticates the user then loads a report from PowerBI using the Guid of the report and passing a few other configuration options to suit the requirements (ie display a simple report with minimal UI). So for this you’ll need a report published to PowerBI under your workspace, I’ve just created a simple Project Status report that shows some details and takes a ProjectId parameter:
Once published to PowerBI you can see the Guid of the report in the browser URL bar, in this case: ae89385e-2896-4e46-9cab-3fb7e6dd16c4
Otherwise you can see the report level filter configured; “ProjectId“, we’re going to use that in the script as well as we’ll be adding the webpart to a Project Detail Page (web part page).
4. Configure the script
So here’s the script:
Note: The script is a .html file, this saves having to have two separate files. Save this file as “PowerBiWebPart.html”.
The script is based on a great ADAL.js example from: http://paulryan.com.au/2015/unified-api-adal/, I’ve just taken the authentication part and then added the PowerBI iFrame components.
What you’ll need to update are the following lines:
- Line 11: Replace [mycompany] with your O365 tenant name
- Line 14: Paste your client id for the PowerBi Azure app registered in step 1 here.
- Line 24: Update the reportUid with your report’s Guid from the last step.
- Line 25: Update the oData parameters as required for your report. You can see I’m using window.PDP_projUid which is a globally declared variable by Project Online that is available in all PDPs to provide the current project’s Guid.
- Line 86: Set the iFrame width and height desired to fit the report in the web part. For the height add 56 px to fit in the PowerBI bar at the bottom.
I won’t go through the whole script in detail, read the comments if you want to know what’s happening, however I will note that I have added the following query strings to the eventual URL of the report (line 57):
They will hide both the filter pane from the right and the report page view from the bottom. (if anyone knows the query string to hide the PowerBI bar at the bottom please post in the comments!)
5. Add the Script to a Content Editor Web Part
Last steps: upload this script somewhere into your SharePoint site collection, e.g. /sites/pwa/SiteAssets and keep a note of the URL.
Edit the web part page / project detail page that you want to add this to, and add the Content Editor Web Part:
Next edit the web part properties and update the URL to point to your script, for example; /sites/pwa/SiteAssets/PowerBiWebPart.html
You can leave the rest of the web part settings as defaults, now save the page and stop editing, on refresh you should see this:
The script has just taken over the browser to authenticate you and now the Application permissions need to be approved by the user. You should not have seen the normal logon prompt as the in the script on line 21 we have configured “prompt”: “none”, but every user will still see the above consent form.
Note: I haven’t tested “admin_consent” prompt setting which in theory should allow the admin to consent on behalf of all users, I’ll update this when I get a chance to try that out
Once you Accept then you should be immediately redirected back to the page you were in:
As you can see that PDP is open on a project called “Apparel ERP Upgrade” and the report filter has been applied successfully!
The user experience in these OAuth2 authentication with consent flows can be a bit weird, even more-so in this case where it is a web part in a page that is causing a full page redirect (and return). Not much can be done about that as OAuth2 requires this flow to be 100% in the control of the authentication provider, ie Azure AD here, therefore it is not possible to hide this in a frame or popup window or anything like that.
Fortunately once done the token is cached for subsequent requests and so only once per day or so (longer I think???) will the authentication redirect happen. Additionally after the initial user consent is granted the authentication will automatically return without further prompting, it just might look a bit weird on a slow connection though.
Finally, if you want to display multiple reports on one page, don’t add the webpart to the page more than once, I found that the authentication gets very confused when the token is not in the cache. Instead update the script to load multiple iFrames or just load a dashboard instead.
Enjoy! And please leave a comment below if you find this useful!