Display a PowerBI report in a SharePoint webpart

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.

Content Editor Web Part and a little JavaScript

Note: this solution only applies to Office 365 and Project Online, sorry on-prem people..

While it would be very easy to embed a ‘Shared’ report into an iFrame in a content editor web part (CEWP), that requires the report to be accessible anonymously which is out of the question in just about any Corporate instance. So the big challenge with displaying a PowerBI report is that you must first authenticate the current user to Microsoft’s servers, this is were ADAL.js comes in, Microsoft’s Azure AD JavaScript library makes this all possible.

Screenshot of the finished product:

finishedwebpart

Overview of the Script

The script while short (scroll down for a sneak peak), does a lot!

  1. It will authenticate the user with Microsoft – normally this will take the users current logged in session so you won’t see the prompt
  2. On first load it will request permission to access PowerBI – this step only happens once per user and covers all web parts / reports configured
  3. Once it has a login token it will cache it for future requests (it’s valid for a few hours/days I think?)
  4. 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.

 

Requirements

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.

Configuration Steps

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

Register an Application for Power BI – https://dev.powerbi.com/apps

Configure the following settings:

register-app-p1a

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.

register-app-p2

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

Unfortunately as nice as that MS web site in the previous step was, it configures only specific types of apps, one that relies on a secret key in addition to the client Id. This is not suitable for a JavaScript app as that would mean the secret key is in plain text in the script! What we need is to enable “implicit flow”, in short this means that only short lived specific access tokens can be issued by this app (ie the kind you get when you logon to O365) negating the need for the secret code.

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.

appreg

Now you can edit the Manifest from the top menu, click Manifest and you’ll need to just make one change:

manifest-edited

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:

powerbireport

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:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://secure.aadcdn.microsoftonline-p.com/lib/1.0.0/js/adal.min.js"></script>
<script type="text/javascript">
(function() {
    /// PowerBI SharePoint WebPart
    /// By Martin Laukkanen - TPG The Project Group
    /// Including some of http://paulryan.com.au/2015/unified-api-adal/
    /// v0.2.20161016
    "use strict";
    
    var subscriptionId = '[mycompany].onmicrosoft.com';
    // Copy the client ID of your AAD app here once you have registered one, configured the required permissions, and
    // allowed implicit flow https://msdn.microsoft.com/en-us/office/office365/howto/get-started-with-office-365-unified-api
    var clientId = '00000000-0000-0000-0000-000000000000';

    var config = {
        subscriptionId: subscriptionId,
        clientId: clientId,
        postLogoutRedirectUri: window.location.origin,
        resource: 'https://analysis.windows.net/powerbi/api',
        prompt: 'none',
        cacheLocation: 'localStorage', // enable this for IE, as sessionStorage does not work for localhost.
        embedUrlBase: 'https://app.powerbi.com/reportEmbed',
        reportUid: 'ae89385e-2896-4e46-9cab-3fb7e6dd16c4',
        oDataFilter: "ProjectData/ProjectId eq '" + window.PDP_projUid + "'"
    };
    
    var authContext = new AuthenticationContext(config);
    // Check For & Handle Redirect From AAD After Login
    var isCallback = authContext.isCallback(window.location.hash);
    authContext.handleWindowCallback();
    if (isCallback && !authContext.getLoginError()) {
        window.location = authContext._getItem(authContext.CONSTANTS.STORAGE.LOGIN_REQUEST);
    }

    // If not logged in force login
    var user = authContext.getCachedUser();
    if (!user) {
        authContext.login();
    } 

    // Acquire token for resource.
    authContext.acquireToken(config.resource, function(error, token) {
        // Handle ADAL Errors.
        if (error || !token) {
            console.log('ADAL error occurred: ' + error);
            return;
        }

        // Store our token 
        config.authToken = token;

        // Update the report once the page has loaded
        $(document).ready(function() {
            // Update the iFrame
            var iframe = $('#powerBiframe');
            var url = config.embedUrlBase + '?filterPaneEnabled=false&navContentPaneEnabled=false';
            iframe.on('load', postActionLoadReport);  
            iframe.attr('src', url);   
        
            // Post the access token to the IFrame
            function postActionLoadReport() {
                var iframe = document.getElementById('powerBiframe');
                var computedStyle = window.getComputedStyle(iframe);
                // Construct the push message structure
                // this structure also supports setting the reportId, groupId, height, and width.
                // when using a report in a group, you must provide the groupId on the iFrame SRC
                var m = {
                    action: "loadReport",
                    reportId: config.reportUid,
                    accessToken: config.authToken,
                    oDataFilter: config.oDataFilter,
                    width: computedStyle.width,
                    height: computedStyle.height
                };
                var message = JSON.stringify(m);

                // push the message.
                iframe.contentWindow.postMessage(message, "*");;
            }
        });
    }); 
})();

</script>
<iframe id="powerBiframe" width="660" src="" height="296" frameborder="0"></iframe>

 

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):

‘?filterPaneEnabled=false&navContentPaneEnabled=false’

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:

addwebpart

Next edit the web part properties and update the URL to point to your script, for example; /sites/pwa/SiteAssets/PowerBiWebPart.html

webpartproperties1

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:

user-consent

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:

finishedwebpart

Neat.

As you can see that PDP is open on a project called “Apparel ERP Upgrade” and the report filter has been applied successfully!

Final Words

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!

Customising PDP Navigation

Despite the best efforts of Microsoft with the UI design of SharePoint / PWA, I frequently get requests from customers to make changes / additions to the standard UI elements such as the ribbon menu, which – if you’re reading Microsoft – people very often still just “don’t get”.

So a small thing I came across while digging around in the internals of the Project Server undocumented JavaScript was an easy way to re-use the standard functions of the ribbons in your own html / JavaScript.

(Note this is not a post about ribbon customisation, see Alex’s great post series here if that’s what you’re after.)

 

Example: ever wanted buttons at the bottom of a long form?

screenbtns

Simple yet effective.

 

Source html

<div id="myFooterBtns"><span>
    <input type="button" value="< Previous Page" onclick="PDPInfrastructure.GotoPreviousPDP();">
    <input type="button" value="Next Page >" onclick="PDPInfrastructure.GotoNextPDP();">
</span>
<span style="float: right;">
    <input type="button" value="Save Project" onclick="PDPButton.SaveData();">
</span></div>

Note the JavaScript function calls which are quite self explanatory: PDPInfrastructure.GotoNextPDP(); etc. As you might guess you can find a similar method for most options (although some are not so simple).

To use: simply save this as a html file somewhere in your site collection and then reference it in a Content Editor Web Part, see here for an example of how to do that.

 

Disclaimer

It may go without saying but I will anyway: This is using undocumented and unsupported internal functions that will undoubtedly change at some point in the future whenever Microsoft feels like it.

You have been warned. But in the meantime keep giving customers what they ask for. :)

 

Project Site Risk List in a PDP Webpart 2013 Version

Back in the days of 2010 there was a Project Site List Viewer WebPart that gave you the ability to show specific lists (Risks, Issues or anything) from a given project’s site in a PDP. While in 2013 on-prem you can still install that solution starter from Fluent-Pro (here), that doesn’t help you with Project Online and not to mention with the dozens of on-prem scenarios where the solution doesn’t work without code modifications. So recently I was asked to solve this problem again and I thought that I’d try to find another way.

Another (simpler) way

Let’s start with a screenshot of the end result:

screen0

Perfect it looks exactly like a typical SharePoint list only in the Project Detail Page for our project – and of course importantly it shows the Risk list from the currently open project.

Reason 9,999 why I love JavaScript

Basically using a small script we can show the actual SharePoint page for the risk list e.g. “/PWA/Project Name/Lists/Risks/AllItems.aspx?isdlg=1” in an iFrame on our PDP. To do that firstly you can see that I’ve added “&isdlg=1” to the URL which tells SharePoint to show the page without the rest of the SharePoint master page and quick launch menu in the frame (which just looks weird), then we need to update the address with the actual project’s name and finally by default the ribbon menu would be shown again in our frame so we need to hide that.

JQuery script

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script type="text/javascript">
function RenderiFrame (c) {
	document.write('<iframe id="nbDataGridFrame" src="' + c + '" width="100%" height="1400" onload="hideRibbonMnu();">');
	document.write('<p>Your browser does not support iframes.</p>');
	document.write('</iframe>');
}
function runSummaryReport() {
	var projectName = $("[id$=RootAspMenu]").find("[href*=projuid]").first().find(".menu-item-text").text();
		
	var URLString = '/PWA/' + encodeURIComponent(projectName) + '/Lists/Risks/AllItems.aspx?isdlg=1';
	RenderiFrame(URLString);
}
function hideRibbonMnu() {
	var $isFound = $("#nbDataGridFrame").contents().find("#s4-ribbonrow");
	if ($isFound.length > 0 && $isFound.is(":visible")) {	
		$isFound.hide();
	}	
	else {
		setTimeout(hideRibbonMnu, 100);
	}
}
runSummaryReport();
</script>

Okay so I won’t go through it line by line, but in summary what the script needs to do is the following:

  1. Construct the correct URL to the list (Risks in this case) by obtaining the Project Name from the quick launch menu of the current page- Yes I’m assuming the default site naming based on project name!
  2. Passes the encoded URL into a function which renders an iFrame of the specified size.
  3. Finally as a part of the iFrame itself there is an ‘onload’ parameter which calls a function to hide the ribbon menu once the frame is loaded.

Installing the script

If you’ve not used the CEWP much before here are a few reminders on how to create a PDP with this kind of script:

Firstly save the script above to a file named “showListonPDP.html” (note it is .html not .js), then upload that to your PWA site contents where everyone can access it /PWA/SiteAssets/ or /PWA/Site Collection Documents/ etc.

Then create a new PDP as normal and add the Media and Content -> Content Editor web part:

screen1a

 

Now edit the web part properties and paste in the URL of the script:

screen2

Note that the URL must be just the file name, not the full URL, e.g.: “/PWA/SiteAssets/showListonPDP.html”

Now add the PDP to your EPT and go ahead and open a project.

screen0

 

Just one more little thing

Now if you click the new or edit item menus it opens the form in the frame which could be better, fortunately SharePoint lists can be configured to open forms in a dialog, the option can be found from the list settings on the project site itself (obviously you want to do this on your project site template as well), so open your site project, go to the Risks list, and select site settings -> Advanced settings, at the bottom you will find:

screen3

 

Select yes and Ok, now when you go back to the PDP and click New Item this is what you should see:

screen4

 

Neat huh?

 

CICONotCheckedOut queue errors when updating projects via JSOM

I’ve written many times before about working with projects using the JSOM and CSOM API’s, and this is another issue in the API that I’ve had to resolve for one of my apps (in this case Bulk Edit).

Issue

When updating built in fields (I haven’t observed this for custom fields) using the client side library (JSOM or CSOM or REST) in the normal way the following error can be reported in the queue unexpectedly:

CICONotCheckedOut: CICONotCheckedOut (10102). Details: id=’10102′ name=’CICONotCheckedOut’ uid=’a2da51ea-792b-e411-9af1-00155d908811′.

Queue: GeneralQueueJobFailed (26000) – ProjectUpdate.FailIfNotCheckedOutMessage. …

For example the following code from the MSDN article on this topic will intermittently get the above error:

// Get the target project and then check it out. The checkOut function
// returns the draft version of the project.
var project = projects.getById(targetGuid);
var draftProject = project.checkOut();

// Set the new property value and then publish the project.
// Specify "true" to also check the project in.

draftProject.set_startDate("2013-12-31 09:00:00.000");
var publishJob = draftProject.publish(true);

// Register the job that you want to run on the server and specify the
// timeout duration and callback function.
projContext.waitForQueueAsync(publishJob, 10, QueueJobSent);

This is despite the fact that we are obviously doing the check-out of the project in line 4.

Worse still:

  1. The update will still work for most fields (like Status Date but NOT for Project Owner), despite the error indicating otherwise!
  2. The error is not consistent, some updates work without an error.

Cause and Solution

Turns out the issue is one due to the asynchronous nature of the client side libraries, specifically it looks like when performing the “waitForQueueAsync”  we are actually requesting four things:

  1. Check out Project
  2. Update project value (start date above)
  3. Publish Project
  4. Check project back in

However it seems that steps 2 and 4 don’t quite run in the correct order!  Changing line 10 as follows to NOT check-in after publishing results in a successful update and no error:

var publishJob = draftProject.publish(false);

Then we need to add a separate checkIn() call AFTER the completion of the publish (IE in the callback function ‘QueueJobSent‘ above) and then call waitForQueueAsync again.

Looks like a bug although perhaps not as it is important to keep in mind that the queued async jobs are not guaranteed to be done in the correct order although clearly it usually happens in the order expected.

 

BTW, Yes expect an update for Bulk Edit supporting more built-in fields soon!

Correcting the alignment of PDP Web Parts

Recently I spoke at a Microsoft Project Server event here in Switzerland on the topic of Extending Project Server and using small pieces of JavaScript with jQuery to make little changes for big effects. One of my demos was to correct the following annoyance that many of us have probably come across but have no out of the box way to fix.

Project Detail Pages column alignment issues

Example

pdp screen 1

 

In yellow I have highlighted the issue that I’m talking about in case it is not obvious in the screenshot. As you see the alignment is actually based on the length of the longest custom field displayed in the web part, so as in the above example where we have used separate webparts to break up the webpage with headings the width of each column is unpredictable.

jQuery to the rescue

This is a great example of using jQuery as it shows how ridiculously simple some things can be to change! So let’s walk through the solution here if you’ve not done this before, as you will see the possible usage of this kind of “fix” is vast.

“Debugging” in Chrome or IE

The first thing you need to do to here is to identify the html element(s) in question, so the easiest way to do that is to use the “Inspect Element” feature available in both IE and Chrome browsers (and probably all others too), you’ll find it on the right click menu

pdp screen 2

When selected the inspect element will open up the developer console of your browser and focus on the specific element under the mouse, in this case the “Description” field text label.

pdp screen 3

Now you can browse the page source and as you see each element is highlighted, neat!

Now we can start using jQuery to select our columns to modify, start by dynamically loading the jQuery library using the following script:

var jq = document.createElement('script');
jq.src = "//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js";
document.getElementsByTagName('head')[0].appendChild(jq);

Now that we have jQuery available you can again look at the source and think about what we need to ‘select’, in this case we can see that our Description label is in a h3 inside of a table (tbody > tr > td) and specifically it has a class of “ms-formlabel”. Cool so in jQuery we can now select all such elements like this:

$("tr td.ms-formlabel")

Best thing is that being JavaScript all elements are selected and can be used in an array, but even better we can directly update all items like this:

$("tr td.ms-formlabel").width("300px");

Neat hey? If you run that command in the console immediately all the columns will update to be 300px wide!

Permanently applying fix to the page

So now we have our script let’s add it to a Content Editor Web Part (CEWP) on the page itself, to do that we need to wrap our line in some html which loads the jQuery library and runs the script when the page is ready, like this:

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script type="text/javascript">

$( document ).ready(function() {
	$("tr td.ms-formlabel").width("300px");
})

</script>

Now edit the PDP web part page, and add a Content Editor web part to it, then simply add the above HTML content to the web part HTML source like so:

First add the Content Editor web part and use the Edit Source ribbon option

pdp screen 4

Then paste in our html script

pdp screen 5

Now each time the page is loaded the script is run and all columns are aligned to 300px looking something like this:

pdp screen 6

Enjoy!