New features for Bulk Edit v1.2

A new version of Bulk Edit has been released and can be downloaded immediately from the app store! In this version I have implemented many of the most requested features and changed some of the old, so read ahead and try it out.

New Features

  • Save changes by row. Now by default all changes are saved one row at a time not and not one cell at a time.
  • Defer saving of all changes. Optionally you can apply changes only a button click, this allows for you to change many projects at once before applying any changes.
  • Improved localization. Dates and numbers now are shown in your locale format configured in PWA.
  • Force check-in button. A shortcut button has been added to open the force check-in page in PWA.
  • Per-user view changes saved. By default the last used field configuration will be displayed when you open Bulk Edit.
  • No more double-click to edit fields. Now clicking on any cell or tabbing to that cell will allow immediate editing.

Bug fixes

  • Sorting order of the custom field filter view is now alphabetical.
  • On initial load the app sometimes failed to load data and would be stuck “Loading…” indefinitely.

Installation

Open site contents in PWA to find the update link:

If you don’t see the update link, then open Details on the app tile to find it.

New Project Server CSOM API features released by MS

Microsoft posted an update to the CSOM API on Nuget last week, see the full announcement here: New SharePoint CSOM version released for SharePoint Online – January 2017

Significantly for those of us in the world of Project Server is the addition of a handful of new classes and methods for managing Enterprise Resource Cost Rates:

  • public class Microsoft.ProjectServer.Client.CostRateCreationInformation
  • public enum Microsoft.ProjectServer.Client.CostRateTableName
  • public property Microsoft.ProjectServer.Client.DraftAssignment.CostRateTable
  • public property Microsoft.ProjectServer.Client.EnterpriseResource.CostRateTables
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRate
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRateCollection
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRatePropertyNames
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRateTable
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRateTableCollection
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRateTableObjectPropertyNames
  • public class Microsoft.ProjectServer.Client.EnterpriseResourceCostRateTablePropertyNames
  • public class Microsoft.ProjectServer.Client.PageSizes
  • public class Microsoft.ProjectServer.Client.PageSizesPropertyNames
  • public property Microsoft.ProjectServer.Client.ProjectContext.PageSizes
  • public property Microsoft.ProjectServer.Client.PublishedAssignment.CostRateTable

(Copied from the Office Blog above)

Good news as that is one more step closer to CSOM / JSOM API parity with the now legacy PSI, also great news to see something new from MS for us poor neglected Project developers. ;)

Updating the PDP Default Page Spacing

One of those small annoyances that I find myself always fixing (or being asked to fix) is the default page layout of a Project Detail Page in PWA, for example:

examplepdp2

Specifically around and between each web part on the page there is a large margin that even (partially) exists when you insert hidden webparts. Additionally the content alignment is out compared to other pages; ie the page title “Kosten” in this case is not left aligned with the page content and sub headings.

Easily fixed with a little CSS

This can be easily fixed with (you guessed) a content editor webpart and the following html style sheet:

<style type="text/css">
.ms-webpartPage-root {
    border-spacing: 0px;
}
</style>

The value was previously “20px”, so if 0px padding is too little then you can try a different value. Additionally there are lots of other styles that you might like to play with, for example: “.ms-PartSpacingVertical” is by default empty but is useful if you need a very specific gap between web parts.

When inserted in a hidden content editor web part anywhere in the page results in:

examplepdp3

Now that page title is a little too close, but now that can be easily edited as needed.

Futhermore, while I’ve not included it in this example, if you want to remove absolutely all spacing then add the following CSS:

.ms-webpartzone-cell {
    margin: auto auto 0px;
}

That 0px value changed from the default of 20px removes all spacing including the margin that hidden web parts have so if like me you find yourself with lots of hidden content editor webparts with scripts and such, then this can be useful.

Hope someone else finds that useful.

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.

 

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!

Update to Bulk Edit Available (v1.0.4.9)

A new version of BulkEdit is available now on the App Store, check site contents in PWA for the upgrade link to install it.

Bug Fixes

  • Fixes to non-English language users who see “Loading data” that never ends.
  • Fixes to some cases where custom field updates error with “Sorry the update failed, ensure that the project is not checked out”

Note: Some cases still exist where Bulk Edit does not detect which projects are checked out, and so will fail to update those fields. This appears to affect non-English Project Online users only, but if you see this please report it in the Support forum.

Additionally I am working on a workaround to address an issue which appears to cause approximately 1 in 5 Project Online field updates (ie Office365 JSOM API calls) to fail due to too many requests which the Microsoft servers cannot handle. The only workaround available at the moment is to retry. Fortunately this problem does not affect on-prem 2013 or 2016 Project Server customers.

If you are affected by this like most of my European Project Online customers are, then do what I do and speak to your Microsoft account manager (or customer’s account manager) about the poor performance of Project Online next chance you get! (feel free to quote me: Martin Laukkanen)

Changes

  • Updates to fully support Microsoft changes in Project Online API to support Project Server 2016.
  • New Welcome Screen for first time users.

bulkwelcome

Installation

Open site contents in PWA to find the update link:

sitecontentslink