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 inhttps://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.webAbsoluteUrlto 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.svcif 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?
