Embed Power BI report on SharePoint on-prem page, SP List as data source & repeatable deployment?

[DylanCristy]

I have asked a similar question about PowerView/PowerPivot, but I have not gotten an answer, and through further research I'm starting to think I can't get done what I want to get done using PowerView. Since it would make sense also to look forward and be ready for a move to O365, I'm now trying to figure out if it's possible to do using Power BI.

Here's the situation:

• SharePoint 2016 on-prem.
• I have a web part that makes some calculations and saves the results to a custom SharePoint list.
• The web part and list are easily deployable to multiple sites, and they need to be, because several different projects need to make these calculations on their own data, and save their results separate from anyone else's results. For instance, Project AAA gets their own site

  https://my-sp-on-prem/sites/site-aaa

  They add the web part to the home page and add the list. The web part will automatically look to save its results in

  https://my-sp-on-prem/sites/site-aaa/lists/my-results-list

  This is due to the fact that the web part is written in JavaScript, and I can therefore code the relative URL

  /_api/web/lists/getbytitle('my-results-list')

  combined with

  _spPageContextInfo.webAbsoluteUrl

  to save data using REST.

All of the various projects using this web part and results list need to generate the same charts/graphs based on the data in the results list. So what I'm trying to do is figure out how to create a report with the correct charts/graphs already set up, using a SharePoint list as a data source (the field names will all be the same since they are all using my custom list definition), but set it up in a way that if I embed the report somewhere on "/sites/site-aaa", it will automatically know to look for the "/sites/site-aaa/lists/my-results-list" as the data source.

And I say "embed the report somewhere" somewhat vaguely because I don't know how Power BI works exactly. I'd like my users to be able to click a link in the quicklaunch, and go to a page in their SharePoint site that has the report right there. I have read that with PowerView, you can store the PowerView report document in a document library, put a Page Viewer web part on a page, and point it at the report document.

Can something similar be done with Power BI? I've also read that there's something called the "Power BI report web part", but apparently that requires modern pages, and I don't have modern pages in SP2016 on-prem.

So, is it possible to somehow:

• Create a Power BI report that uses a SharePoint list as a data source. (In one way or another - I also understand that Power BI can use an OData feed as a data source, and I have no problems using the REST API or even the old ListData.svc if that would make this all possible.)
• Show that report embedded somehow in a SharePoint site (on-prem!).
• Have the report look for the data source list on the same site it is embedded on.
• Have the process to embed the report on the site be very easy, like activating a SharePoint feature, or maybe adding some web part to a page and pointing it at the report.

If all that is possible, then what Power BI product(s) will I need to get, and what will the licensing look like for that?

Do I need to get Power BI Server? (I've read that that's what you need to "publish" a Power BI report on-prem.) Is that a single license for the "server" product? Will there also be user CALs for all the users viewing the reports on their SharePoint sites?

[v-yuezhe-msft · Accepted solution]

@DylanCristy,

Firstly, you are able to use SharePoint list or OData feed connectors in Power BI Desktop to connect to your list.

Secondly, you can generate code by using publish to web feature, and add the embed code to page viewer part of your sharepoint site. Or you can create new page in your sharepoint site and add Embed Code to the page as discussed in this similar thread.


The above method doesn't require Power BI license. Power BI free account is enough. 

However, if you concern about the security issue when using publish to web feature, you can use Power BI embedding feature. Or you can use Power BI report server. And you can review the article to get more details about Power BI report server license issue.

Regards,
Lydia