Roland Oldengarm - Independent IT Contractor

Living in the coolest little capital Wellington, New Zealand!

Part 3: How to embed a Power BI report in SharePoint with the SharePoint Framework

In this final post of the series “How to embed Power BI in SharePoint”, I’m going to show you how to embed a PowerBI report in SharePoint, using Power BI Embedded and the SharePoint Framework! All new technologies, so exciting times 🙂

Please refer to part 1 and part 2 of this series. Previously I used the approach where the PowerBI service was used to render the report. The big downside is authentication, as this approach requires the user to log in. I had tried to solve that by embedding the token, but looking at the comments that did not work for everyone.

PowerBI Embedded is a different approach, where Azure is added a layer to render and store the reports. It took me a while to get my head around PowerBI Embedded, but this image explains it perfectly:

In my case, “Your app” refers to a SharePoint Framework app, i.e. all client-side technologies.

To embed a PowerBI report in a SharePoint Framework app, I’ve followed these steps:

  1. Create a Workspace collection in Azure
  2. Create a Workspace in Azure
  3. Upload the PBIX to this Workspace
  4. Create a report access token
  5. Render the report in the client-side web part

Let me discuss each step in more details.

How to: Create a PowerBI Workspace collection in Azure

This was the easiest step, and can be achieved via the Azure Portal. It is discussed in detail on the PowerBI website, scroll down to Create a workspace collection. As a result of this step you need to copy/paste one of the access tokens, plus the name of your workspace collection.

How to: Create a PowerBI Workspace in Azure

You can’t create a workspace via the UI. So, we have to use the Power BI API. The examples on the PowerBI website use either C#, or manually craft HTTP POST’s to the PowerBI API. I did not want to do either, and luckily there is an alternative: The PowerBI CLI. This is a Node.js tool, so you need to have NPM + Node.js installed on your machine.

Another prerequisite is that you’ve saved the PBIX file from PowerBI to your computer.

  1. Open a command prompt
  2. Go to the folder containing the PBIX file, and stay there for the rest of this tutorial
  3. Run “npm install -g powerbi-cli”
  4. Configure the settings for workspace collection and access key, so you don’t have to type them every time:
    powerbi config -c <workspace_collection_name> -k <access_token>
    
  5. Create a workspace via:
    powerbi create-workspace
    
    [ powerbi ] Workspace created: <workspace_id>
    
    
  6. And configure this workspace ID as the default config setting using:
    powerbi config -w <workspace_id>
    

How to: Upload a PowerBI PBIX file to your workspace

The next step is to upload the PBIX file to your create workspace. From the command line, type the following:

 powerbi import -f <filename> -n <name>

e.g.

powerbi import -f .\hotels.pbix -n Hotels
[ powerbi ] Importing .\hotels.pbix to workspace: <workspace_id>
[ powerbi ] File uploaded successfully
[ powerbi ] Import ID: 24774420-4a32-4623-8cd5-4c3b7513e965
[ powerbi ] Checking import state: Publishing
[ powerbi ] Checking import state: Succeeded
[ powerbi ] Import succeeded

The next thing to do, is to grab the report ID, as you will need this later:

$ powerbi get-reports
[ powerbi ] =========================================
[ powerbi ] Gettings reports for Collection: <workspace_collection_id>
[ powerbi ] =========================================
[ powerbi ] ID: <report_id> | Name: Hotels

Save the report_id for later use.

 

How to: Create a report access token

Authentication happens via access tokens. As I’m building a client-side application, the access tokens are accessible to anyone. The access token from the Azure Portal gives a user full control on your workspace collection, you don’t want that it ends up in the wrong hands! Therefore you must create an access token that only has access permissions to a particular report.

To do this, run the command “create-embed-token” with as parameter the report id, saved in the previous step:

powerbi create-embed-token -r<report_id>

This will return something like this:

[ powerbi ] Embed Token: eyJ0eXAiO.....S75E9qdBwpRHEA

Save this embed token for the next step.

How to: Render the report in the client-side web part

The last and final step will create the client-side web part, and let it render the PowerBI report. First of all, install all the prerequisites for the SharePoint Framework. This is explained in detail here, so I won’t repeat that.

To communicate with PowerBI via JavaScript, I’m using the PowerBI Client plugin.

First, create your web part:

  1. Open a new command line instance
  2. Go to a folder where you want to create the web part, e.g. “c:\projects\sp”
  3. Make a folder, e.g. “PowerBI_WebPart”, and navigate to it.
  4. Run the yeoman SharePoint generator with “yo @microsoft/sharepoint” and stick to the default values.
  5. Add the additional dependencies via:
    “npm install powerbi-client –save”
  6. Open a Visual Studio Code instance via “code .” (or use any other editor)

The next thing is the implementation of the actual web part. I was struggling with this, as the documentation on the PowerBI client did not work initially. I could not find the global variable powerbi; so after some digging I found an alternative by creating the service manually. Next, the example documentation uses jQuery, but it’s not required, so I’m using vanilla JavaScript.

Go to the webpart file in your editor (i.e. PowerBIEmbeddedWebPart.ts) and add the following import:

import * as pbi from ‘powerbi-client’;

Then, replace the render method with the following implementation:

this.domElement.innerHTML = `
<div id="reportContainer" style="height:500px;"></div>

<a id="fullscreen">FULL SCREEN</a>`;

// embed configuration.
// generate the access token with "powerbi create-embed-token -r<report_id>"
// get the report id via "powerbi get-reports"
var embedConfiguration = {
type: ‘report’,
accessToken: ‘eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ2ZXIiOiIwLjIuMCIsImF1ZCI6Imh0dHBzOi8vYW5hbHlzaXMud2luZG93cy5uZXQvcG93ZXJiaS9hcGkiLCJpc3MiOiJQb3dlciBCSSBOb2RlIFNESyIsIndjbiI6IlBvd2VyQkktUm9sYW5kIiwid2lkIjoiN2JmNmU0ZDMtNjJkZi00YmY1LWJhZDQtYjA0OTI3OWQ0NmQxIiwicmlkIjoiY2NhMGVjYjQtYjc4Yi00Njk4LWJlYzQtZjc3YmU4OGY0YTFmIiwibmJmIjoxNDczNzI2MTcyLCJleHAiOjE0NzM3Mjk3NzJ9.tI-yc_YGuw0krR0T-FNZ9e1ueyMcdQLlbP5L2o3K2I0’,
id: ‘cca0ecb4-b78b-4698-bec4-f77be88f4a1f’,
embedUrl: ‘https://embedded.powerbi.com/appTokenReportEmbed’
};

// grab a reference to the HTML element containing the report
var reportContainer = document.getElementById(‘reportContainer’);

// construct a PBI service; according to the documentation this should be already available as a global variable,
// but in my case that did not work.
let powerbi = new pbi.service.Service(pbi.factories.hpmFactory, pbi.factories.wpmpFactory, pbi.factories.routerFactory);
var report = powerbi.embed(reportContainer, embedConfiguration);

// attach an event handler for the
document.getElementById("fullscreen").addEventListener("click", () => {
var report = powerbi.get(reportContainer);

report.fullscreen();
});

Then, run “gulp serve”, and wait until the workbench loads. Add the web part:

After it has loaded, you should see your report, with the option to open it in full screen!

Embedding reports via PowerBI embedded in SharePoint

I’ve shown you how to embed your PowerBI reports in SharePoint via the SharePoint Framework. User won’t need to log in, but are still able to see data they normally would not be allowed to see. Be careful though, the data in your report is available to anyone with access to the page!

Another big advantage of using the SharePoint Framework is that it’s backwards compatible, i.e. you can use this approach to embed PowerBI reports in “old school” server-side page layouts!

The source code is available on Github.

7 Comments

  1. Nice! – I got this working pretty easily. The one issue I am seeing is how do I keep a scheduled refresh for this item?

  2. Hey Roland,

    If I was to set this is to use many different reports (report access tokens) would I have to create a new webpart for each report?

    • rolandoldengarm

      March 2, 2017 at 12:45 pm

      With the current code, yes, but nothing prohibits you from updating the code to accept a list of report access tokens of course 🙂

  3. Very nice blog post! Thank you.
    Your solution implements pre-generated access token. It is only good for a short period of time.
    Do you have any suggestions for generating fresh access token from within the webpart?
    Thanks,
    Pawel

  4. rishabh shukla

    June 7, 2017 at 9:16 am

    Hi Roland,

    Thanks for explaining it so well. Do you know how can I increase the token expiry time?

    Thanks,
    Rishabh

  5. Hi,
    Using this approach could we embedd a powerBI Dashboard?

Leave a Reply

Your email address will not be published.

*