3

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 quick launch, 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.
  • 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?

4

Create a Power BI report that uses a SharePoint list as a data source

Yes, you can get SharePoint List / Library data using

  • OData Feed.
  • SharePoint List as a direct data source connection

For more details, check POWER BI: READ DATA FROM SHAREPOINT LIST


Show that the report embedded somehow in a SharePoint site.

There is no a Power BI web part in SharePoint 2016 On-Prem, it only available in SharePoint Online, maybe will be available in SharePoint 2019 Modern sites.

So you should use

  • the traditional Page viewer web part to show it. or
  • show it in a new tab without a toolbar.

Have the report look for the data source list on the same site it is embedded on.

This is will be specified manually based on your requirements.

When adding SharePoint List connection, it will ask you to provide the List path.


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.

There is no any relation between the SharePoint OnPrem and Power BI, it does not work like SSRS integrated mode.

So no Application service to be configured or feature to be activated.


Do I need to get Power BI Server?

Yes, you should Install and Configure Power BI Report Server, you can do that on the WFE SharePoint Servers or an independent Power BI Report Server.

For more details, check Install and Configure Power BI Report Server


what will the licensing look like for that?

Unlike the SSRS (SQL Server Reporting Service), the Power BI Report Server license is not bundled with the SQL Server License.

The Power BI Report Server license has an independent license that is included in the Power BI Premium.

For more details, check Install and Configure Power BI Report Server

  • Thanks for your long answer! A couple of clarification points though - regarding looking for the data source on the same site, you are saying I will need to configure each report manually, but that is exactly what I do not want to do, I want to set up the report template in a way that will avoid me having to do an extra configuration step each time the report is deployed to a site. Are you saying that there is no possible way to do that with a Power BI report? Secondly, one of your answers says "So no Application service to be configured or feature to be activated", but I think...(cont.) – Dylan Cristy Aug 24 '18 at 14:27
  • ... you misunderstand what I mean there. I am asking about the process to put a report embedded on a site. For instance, let's say I create a new site. In order to put my web part on it, I click edit page, then add web part, then add the web part. Easy. To put my custom list on the site, I go to site features and activate the feature that has my list definition/instance, and the list is now there. One click, easy. So what is the process to get the report on the site? Is it a file that I can package in a feature module to deploy to a doc lib? Does the report template live on... (cont.) – Dylan Cristy Aug 24 '18 at 14:31
  • ... the Power BI Server, and the steps for getting the report on the site are that I add a Page Viewer to the site and point the Page Viewer to the Power BI Server? How can I make that process as easy and repeatable as possible? Is it something I can package as a custom feature that can be activated from Site Features, the same way my custom list is deployed? Does that make more sense? I am not asking if there is some Farm feature that needs to be activated to get Power BI working on the farm, I am asking how to get a new report on a new site, in a very easy and repeatable way. – Dylan Cristy Aug 24 '18 at 14:36
  • 1
    You are welcome! @DylanCristy As a short answer: Power BI doesn't help in your scenario! if you checked my article, you will build your report using Power BI desktop and publish it to Power BI report server, then you can use the report URL in page viewer web part! no packaging, no dynamic data source, no flexibility as you imagine! – Mohamed El-Qassas MVP Aug 24 '18 at 16:08
  • BTW, Although your question/comments are too broad, I tried to help and answered all of your questions based on your description in detail as well as I have replied to your comments because I just want to help !! so please Avoid asking multiple distinct questions at once at next time! also, I noted that your comments are not specifically related to your multiple questions? here you asked about the ability/license ..etc and I answered it. and your comments search for alternatives! so please add it to independent question if you need further calafarciations. and close this one if it helped you – Mohamed El-Qassas MVP Aug 24 '18 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.