BI Cloud image

How to integrate Power BI reports to your website or SharePoint site

In Business Intelligence, Power BI, SharePoint, SharePoint On Premises, SharePoint Online by Bryant Avey0 Comments

Microsoft Power BI has a feature that allows users to publish PowerBI.com reports to any website or app that allows for iFrame embedding. The report above is a live, interactive Power BI report.  Take a few minutes to play with it.  This post will walk through the major features and then progress to a, step-by-step instruction on how to integrate a Power BI report into your SharePoint site. The same technique can be used with SharePoint onprem and online with SharePoint 2013 and 2016. It would likely work in SharePoint 2010 as well, although I haven’t tested it.  You are viewing this post on WordPress, so it obviously works with WordPress, and should work with most major web content provider systems.

The Data

The data in the report was integrated using the internuntius inCollatio™ business analytics architecture. The information is a combination of US Census data and NFL team data.  The data was combined based on the city where each NFL team is located. All the Power BI chart elements are linked together, so clicking on any element will dynamically filter the information displaying on the other related elements in the report.  Give it a try. Click on a city from the map and all the data in the other charts is filtered to represent the data and percent of total for that selected city. You can look at groups of teams by using the filters in the bottom right of the report to pick AFC or NFC (American Football Conference and National Football Conference). Then you can further slice the report data by division (north, south, east and west).  So for example if you choose NFC and North from the slicers, you’ll see the Vikings, Packers, and Bears, and Lions along with the various ethnicity, gender, crime and economic statistics related to the cities.  The data is averaged together to show the median value of the teams selected.

One thing you’ll notice if you look hard at the data is that some of the teams are represented in more than one city. One example is the Rams, who are listed both in St. Louis and also in Los Angeles.  This is because our dataset contains historical team data going back to the inception of the NFL and we have individual player member data going back over 10 years.

But wait…There’s more

There is one significant feature that’s worth mentioning. This feature is only available on the PowerBI.com website while looking at the dashboard.  Dashboards are not able to be exported for web viewing. Only Reports can be exported. Having said that, there’s a feature in Power BI Dashboards called Natural Language Query. How it works is that you just start typing questions in the query entry and Power BI will analyze your question and dynamically create a custom chart using the underlying datasets in the dashboard to attempt to answer your question through a data visualization.

By typing the question: “teams with more black than white” in the natural language query prompt, Power BI automatically provided the following scatter plot of the Census Ethnicity for Black vs. White by NFL team.

Take a look and you can quickly see that Green Bay, Wisconsin has a 73% white population with only 4% black. On the other end of the spectrum, Detroit, Michigan has an ethnic mix of 83% black and only 8% white. Minnesota and Chicago are in the middle. So the NFC North has the absolutely most diverse ethnic makeup of any conference division in the entire NFL:

Ethnicity mix by NFL city – Black and White Populations

Ethnicity mix by NFL cityBlack vs. White

Requirements

A few things are required to utilize Power BI. The following list is the minimum you’ll need in order to follow along this article.

  1. A PowerBI.com account. You have to register with PowerBI.com and get a free account. There’s no way around this.
  2. This shouldn’t be a problem because you can register for a free Microsoft account at live.com. A Microsoft Live account gives you free access to the full suite of Excel, PowerPoint, Word, and Outlook.
    Outlook, People, Calendar, OnDrive, Word, Excel, PowerPoint, OneNote, Sway, OfficeOnline
  3. Optionally, you may want to use Power Pivot or Power Query to pull in your own data and create data relationships between various tables and sources. (in Excel 2016 Power Query is called “get and transform” in the Data Ribbon)
  4. A website where you can add the report. The walk-through in this article uses Office 365 and SharePoint online, but you can use any WordPress, SharePoint, or other web site or web page you may have. The only restriction that the Power BI “export to web” feature generates an iFrame, similar to YouTube. If your system supports iFrames, and nearly all of them do, then you’ll be able to do this.

Navigating your way around

At the top-right of each chart, as you hover your mouse over it, you’ll see a “Focus mode” icon.
focus icon

Clicking the Focus mode icon will zoom that chart up to full screen so you can focus in on it.  While in focus mode, the top navigation of the chart changes to show you “Back to Report” option in the top left.  Click Back to Report when you’re finished focusing on that chart.
back to report

There’s also a new chart option that displays in focus mode, allowing you to see the data behind the chart or map. The top-right of the focused chart shows the “See Data” icon.
052216_2324_Howtointegr6.png

Clicking on the See Data icon brings up a data grid view of the data behind the chart.
052216_2324_Howtointegr7.png

You can toggle between a vertical and horizontal view of the data and chart.  Also notice that in the very bottom right corner of the Power BI chart there’s an icon allowing you to view the report in full-screen mode, rather than having to look at it within the web page.

Click the Hide Data icon in the top-right and then click the Back to Report option in the top left of the chart to return back to the main report view.  Next, try clicking on either a team from the Team slicer or on a city from map.  As you click, you’ll see all the charts on the page change to show the data for that team or city. Clicking on a City from the map, displays the portion of the data for that city as compared against all cities. Clicking on a team from the team slicer displays only the data for that team.

Power BI changes the way we interact with data on the web!
Static images, reports, and infographics are dead.Bryant Avey, Chief Geek, internuntius

Publish to Web Walk-through

This article assumes you already have a report built in Power BI that contains public data that you with to publish to your website. If you need help producing reports and dashboards in Power BI you may find further information in our Learning Center videos or in our Leverage Center articles. (click on the “learn” or “leverage” menu at the top our website) We’re also happy to help you jump-start through our Adaptive Consulting services where we provide just-in-time assistance for you, no matter where you are in your process or learning or development cycle. (click on the “link” menu at the top of our website to learn more)

To publish your Power BI report, you’ll first need to open the report in Power BI and select the “Publish to web” option from the menu.
Publish to web
This will bring up a dialogue panel showing your embed settings, allowing you to make some adjustments to the size of the iFrame it produces.
publish to web dialogue box
If you just want to e-mail your report to someone you can copy/paste the e-mail link. But we want to embed the report into a web page, so we’ll need to use the second option. Copy the Html code highlighted in yellow in the image above.  We’ll use this in one of the next steps to embed the Power BI report into our SharePoint web page.

Creating your SharePoint Page

Now we need to create a SharePoint page to host our Power BI report.  This example uses a Web Part Page to insert a code snippet containing the Html code we copied from the previous step.
On your SharePoint site, click on the settings menu (the gearbox) on the top-right of your SharePoint site page. Choose “Add a page” and give your page a name when it prompts you.
Add a page
If you don’t see the Add a page option, you may need to add a page manually through the pages library. To add a page through the pages library, go to your “Site Contents” and find a library called “pages” or “site pages”.
Note that there are some slight differences between Publishing Pages and Standard Pages in SharePoint. This example uses Publishing Pages which are located in the Site Pages library. If you choose to use a standard page, the options are slightly different and the page may look different, but the actions that follow are the same.
site pages
 After clicking on the Site Pages library, from the Ribbon Menu, select New Document and from the drop-down menu select Web Part Page.new web part page
Give your page a name and when finished, you’ll see a new page in the Site Pages library.
new web part page
Click on the new page and choose a layout. Once finished,open the page and then edit the page so you can add the Power BI report to the page.
052216_2324_Howtointegr11.png
In order to add the Power BI report, we need to insert a web part called “Script Editor”. In edit mode of the web part page click on the Add a Web Part link. If you’re using a different type of SharePoint page, click on the spot where you want to insert your web part to get a text insertion bar and then from the Ribbon Menu, choose the Insert Menu and choose Web Part.
insert web part
Navigate to the Media and Content category and select the Script Editor web part and click the Add button.
snippet web part
Note: you could also choose the “Embed Code” icon from the Insert Menu to do the same thing, however, script editor web parts behave in a more stable manner within many SharePoint pages. Technically either method will work, we just prefer to use script editors.
Once you have the Script Editor on your page, click the Edit Snippet button in the bottom left corner of the script editor web part.
script editor - edit snippet
Editing the Snippet will bring up a dialogue box allowing you to pate in the Html you copied from your Power BI Save to Web process. Unless you’ve copied something else to your clipboard, the Html should still be loaded, so all you have to do is to paste in your Html code in the snippet editor.
insert power bi snippet
You should see a representation preview of the results below the snippet.  When finished, click the Insert button and then save and publish your page.
Power BI embedded SharePoint page

Other Considerations

You’ll notice in the last screen shot above, that there’s a gray border around the Power BI Report.  This can be controlled by the height and width setting in the Html snippet code.  We’ve found that an aspect size ratio of 60% works best for eliminating the extra gray boarder.  Aspect ratio refers to the result of dividing the width by the height. On this webpage, where you see no gray space, we used a setting of 1500 x 900. If you do the math, 900/1500 = .6 which is 60%.

Build one and let us know about it

If you find this post interesting and helpful, please let us know by taking a minute to comment. Also if you build something cool, please comment and provide a link to your work. We’ll review it and approve the comment to help evangelize the technology.