Roland Oldengarm - Independent IT Contractor

Living in the coolest little capital Wellington, New Zealand!

Part 1: How to embed PowerBI in a SharePoint site

One of the most requested features for PowerBI was to be able to embed it in any web application. This has now been implemented with Power BI Embedded. An excellent demo created by Microsoft can be found here. While this is a very cool feature, it is aiming at developers. Another feature request that many of our customers have is to embed reports in a SharePoint site, e.g. to display it on an intranet.

Currently there is no out-of-the-box web part to display a PowerBI report in a SharePoint site. What I am going to show you, is how to do this with a provider hosted add-in!

Update September 2016: Part 3 is available.

While below instructions are working on my environments, some people have found some issues with this approach. In Part 3 I am using the Power BI Embedded service in the SharePoint Framework which solves those issues.


 

Essentially the app will work as follows:

  1. The App allows the user to sign in.
  2. After signing in, a list of Dashboards is shown.
  3. After selecting a Dashboard, a list of Tiles on that dashboard is shown.
  4. When selecting a Tile an pressing confirm, that particular tile will be displayed on the SharePoint site.

As a lot of steps are involved, I will focus on steps 1 – 3 in this part of the blog post series, and in part 2 I will show how to embed it into a SharePoint site. The source code can be found on GitHub

Register a Power BI App

The first step is to register a Power BI App. This works similar to registering an app for Azure AD. Under water it is the same, when you create a Power BI App, an Azure AD App is created for you.

Go to https://dev.powerbi.com/apps and fill out the form. I have done it as follows:

Regarding permissions, I’ve chosen to enable all permissions, just as I don’t want to waste time on 401 errors. In a production scenario, you should only pick what you need.

The last step is click Register App. Please keep in mind there is no Edit function. Every time you click Register App, a new app is created in Azure.

Create a Provider Hosted Add-in

Open Visual Studio (preferably 2015), and click File > New Project.  Select Office/SharePoint > App for SharePoint

Click OK and specify the following settings:

The URL must be a developer site on Office 365.  Click Next and log in. In the next screen, select “SharePoint Online”. In the final screen I’ve selected Web Forms. For Production purposes I would recommend to use MVC, but with Web Forms it is quicker to develop a basic app.

I prefer to use IIS (instead of IIS Express). Go to the project settings of the Web Project, go to Web, and change IIS Express to Local IIS. Also change Project URL to https instead of http. Finally, click on Create Virtual Directory.

Please note that this requires Visual Studio to be run as Administrator, and that IIS is configured on your machine.

The application

Please have a look at the source code on GitHub. It will take forever to explain every line of source code, so I will take you through the important bits:

  • NuGet Packages: Add the following NuGet Packages:
    • Newtonsoft.JSON
    • Microsoft.IdentityModel.Clients.ActiveDirectory
  • Settings: Double click Properties, go to Settings and create a default settings file. Add two settings: ClientId and ClientSecret, and copy the values from the PowerBI App.
  • Redirect.aspx: When the OAuth flow complete,
  • Default.aspx: This page is the main entry point of the application. It contains a Login button, which handles the authentication flow with Azure AD. After logging in, it will display the Dashboards in a drop down. When selecting an item, it will display all Tiles in that dashboard in another drop down. And finally, when a tile is selected, this report will be displayed.
    Some client-side JavaScript is required to handle the authentication for the iframe. All other authentication happens server-side.
    In Part 2 of this blog post series I will show how to inject scripts into the host web, to show the report in the SharePoint site.

The result!

 I have added the sample “Sales and Marketing” dashboard to my PowerBI instance:

Then, after

  • I deploy the app from Visual Studio via F5.
  • Trust the App
  • Click “Sign In”
  • Log into Power BI
  • Accept the permissions requested:

… the sign in button disappears and I see the dashboards. I select the only dashboard in my account:

… and the first Tile in the Dashboard:

After waiting a bit, the dashboard should show up like this:

We have embedded PowerBI in a SharePoint Add-in

Goal 1 is achieved: Embed a PowerBI report in a SharePoint Provider Hosted Add-In. The next step is to inject this into the host SharePoint web. I will follow the approach the PnP team is using in their AppScriptPart sample. I will show this in Part 2 of this series! Follow me on Twitter to be the first to know.

37 Comments

  1. Thank you for a nice post!
    Is there any way to implement whole Report or Dashboard to Sharepoint instead of Tiles?

  2. Hello, do you think it’s possible to implement report in hosted-sharepoint in SP Add-in without and let internal users to explore it without asking login credentials to PowerBi? Something similar is shown here — https://azure.microsoft.com/en-us/documentation/articles/power-bi-embedded-get-started-sample/
    Is it possible to implement it over Add-In to sharepoint?

    • rolandoldengarm

      April 29, 2016 at 11:27 am

      Thank you for your comment. Yes that’s certainly possible, and I’m planning to write about that in part 3 🙂 Part 2 will be to integrate what I have now into SharePoint. Even then, not every user will have to log in, the authentication token generated by the person configuring it could be reused. However, this has the big disadvantage that this may expire, so definitely not production ready.
      So, part 3 will be about using the Power BI Embedded service in Azure to overcome this issue.

  3. “An excellent demo created by Microsoft can be found here.”
    Don’t see any demo at that link

    • rolandoldengarm

      May 3, 2016 at 9:20 am

      The link is definitely there? Anyway, please try copy/pasting this link: http://pbipaasdemo.azurewebsites.net

      Please keep in mind that they are using the Power BI Embedded service, which is for anonymous access. I’m using Embeds; which are easier to set up, but does require a user to log in. In a next post I’ll discuss the differences.

  4. Thanks for the post. Is it possible to apply what you did for embedding a Power BI tile to an on premise SharePoint environment?

    • rolandoldengarm

      June 2, 2016 at 11:22 am

      With SQL Server 2016 it’s possible to use Power BI on premises, but I’m not sure my solution will work there.

      • Can you expand on this? Why would your solution not work, and how is it possible with SQL Server 2016? Does that mean it won’t work with older versions of SQL Server? Also, do you have any resources(links) that you can send me to that deals with embedding Power BI tiles on an on premise SharePoint environment?

        • I noticed that Power BI dashboards lose interactivity when embedded in SharePoint. Dashboards become static. Is there any way around this?

          • rolandoldengarm

            June 16, 2016 at 11:57 am

            Sorry for the late reply.

            Your first question:
            The authentication flow I’m using means you have to register an app for Power BI Online. This flow will most likely be different for Power BI On Premises.
            Power BI On Premises will only work with SQL Server 2016.

            Your second question:
            How do you mean they are static? What have you exactly done? In my demo, they are still interactive; i.e. the hovers and such still work.

          • Thanks for the reply. Is there a way to get at a users profile information in SharePoint and pass that as the credentials in Power BI? I’ve seen examples of getting at the username information but not at the password.

          • rolandoldengarm

            June 30, 2016 at 8:53 am

            No, you can’t retrieve a user’s password for quite obvious security reasons…. Otherwise any piece of server code could steal your password.

            You want to look at impersonation. The only option would be is to let the user log into the app via oauth.

  5. Thanks for the above post it . i have done full configuration and instead of localhost i am hosting the application in azure webapp it is getting hosted properly but when i click on sign-in and enter the user name and password it is getting authecate properly but while redirecting i am getting error it never goes back to the azure webapp site.

    Below error message i am getting.
    Timestamp: 2016-08-23 16:42:09Z
    AADSTS50011: The reply address ‘https://powerbidryrun.azurewebsites.net/Pages/Redirect.aspx’ does not match the reply addresses configured for the application: ‘89077752-d0b9-43ab-aca3-27e9da6a3e6a’. More details: not specified

    Please let me know is there any other configuration required to be done. Please share me your email id so that i can share you the error properly. Thanks!

    • rolandoldengarm

      August 25, 2016 at 2:31 pm

      The message is quite clear, right? The reply address configured in your Azure Application is not correct.
      You can open up Azure Active Directory, check you applications, and change the reply address. Alternatively you can create a new Power BI Application via https://dev.powerbi.com/apps.

      • Hi Roland,
        Thanks for the awesome post. I tried the steps that you mentioned above in the post. I am getting error:- “An error occurred while verifying your SharePoint Context. Probably the required query string parameters are gone because you’ve logged in. Please click back once or twice and refresh the page when the query string parameters are available.” , upon providing my credentials. I am not able to resolve it. Can you please help here.

  6. Hi Roland,

    Thanks for the article. i’d just like to make sure that the report imported to Sharepoint is not static, it will stay up to date with the refreshes done on the power bi reports, correct? Can you please confirm that.

    Thank you

  7. Hi Roland,
    Great blog, I was inspired by your method discussed here after separately getting familiar with the Azure ADAL.js library from Microsoft to solve this problem in a completely different way:

    Using ADAL.js and JavaScript to show a PowerBI report in SharePoint:
    https://nearbaseline.com/2016/10/display-a-powerbi-report-in-a-sharepoint-webpart/

    This JavaScript approach is much simpler, though far more limited that your solution, but it does remove the need for a Provider hosted app and all that entails.

    Hope you don’t mind me sharing here. 🙂

  8. Great topic you have covered here but for some of us newbies, if i try to do it in IIS with local host i have no https
    Can you explain how to do it in IIS express please

  9. There’s a great way to embed Power BI dashboards and tiles into SharePoint using a new feature in LiveTiles SharePoint which is worth checking out. https://powerbi.microsoft.com/en-us/blog/livetiles-presents-the-new-power-bi-tile/

    • rolandoldengarm

      February 20, 2017 at 10:21 am

      That looks great indeed. As with any new technology/feature, there may be limitations, or not?
      Plus, it’s not for free, it’s a 3rd party offering. I would have loved to see this being part of O365 / SharePoint.

      But great job Tom, I think you are filling a gap here.

  10. Hi rolandoldengarm,

    I am unable to see the powerBI reports in sharepoint online. I have tried the procedure given by you but i am facing issues like :This site can’t be reached

    localhost refused to connect.

    Can you please guide me with detailed steps how to publish it to online. I have a requirement to display it in online.

    Thanks in advance

    • rolandoldengarm

      February 24, 2017 at 10:46 am

      If you get errors about localhost, this means that you’ve published the app pointing to localhost. That’s of course only going to work on your dev machine.
      You need to publish your site to Azure. Then, everywhere it says “http://localhost” you need to change that to the Azure URL.

  11. My Requirement is almost similar, except the fact that I want a Dashboard to be embedded in my application and upon clicking the Tiles of the Dashboard, it should go to the particular Power BI report whose items is pinned in the dashboard on the first place.
    So basically I want the same behavior as we have in the power BI site, like clicking the Tiles of a dashboard it will go the particular report.
    As of now I have embedded the dashboard with the Tiles, but upon clicking the Tiles nothing is happening 🙁
    Can anyone please help me to achieve this.

  12. Hi there, I’m trying to get this going and I’m probably quite new at this compared to everyone else, but when I go to Create Virtual Directory I get an error “Unable to create the virtual directory. Could not find the server ‘https://localhost/PowerBI_Office365Web’ on the local machine. Make sure the local IIS server has been configured to support secure communications.’

    Do I have to run this on our Sharepoint server, or is there something I have to do on my local machine’s IIS to get it working?

Leave a Reply

Your email address will not be published.

*