cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterNagy
Frequent Visitor

Report Server publish to website using REST API

Hello and welcome,

I have many questions regarding Power BI Report Server and how to embed it with a website.

I have been working for a year with Power BI and now we have to switch to On-Premises and the full solution is to develop the Reports and embed them in a website developed with AngularJs and Bootstrap.

 

My Questions are:

1- How can i embed the report server reports in a website for external use?

2- One of the experts suggested that i should use REST API to get the data and visualize them in my website, if that is correct:
     - How can i use the REST API? is it already developed for the report server and i should reuse it or i have to develop the API      Calls manually?
      - If i used these REST API Solution, will the charts be integrated all together so i can filter them ? and will they be Respnsive?

3- If there is another way to embed the reports to the website and be responsive please inform me.

 

 

The last question regarding the license for Power BI Report Server, the solution of SQL Eneterprise License with Software Assurance, should the SQL Enterprise be 2017 ? or can i use 2014 version?

 

Thanks

1 ACCEPTED SOLUTION
stpnet
Impactful Individual
Impactful Individual

Yes. We discovered that we couldn't "embed" dashboards from the on premise version in the way that you can using the cloud version, all you can do is an iframe with some params passed through on the URL. We found this to be quite demanding in terms of infrastructure. You have no real control over the aspect ratio so using it to produce ribbons of charts above or to the side of other content proved impractical. rendering each micro chart as an embedded iFrame proved to be way too much demand on the server and indeed the client (each iFrame gets the entire javascript and angular app that renderss the PBI report)

 

If you are using the cloud version then there's a ton of API stuff for embedding using a REST API and stuff

 

https://docs.microsoft.com/en-gb/power-bi/developer/embed-sample-for-customers

 

But this only works for cloud deployed PBI, this fucntionality is not available on the on premise PBI-SSRS product.

 

So we resorted to using simple D3 charts for all of this. All these micro charts respond to slicers we have built into the app to control the time period you are looking at, the currency and the position in the business heirarchy to look at/start at etc. But the slicers change everything on the screen. each of the D3 charts issue DAX queries (that are parameterised) using an ADO.Net connection to a SSAS Tabular instance, the params get changed as the slicers get changed. So not really very interactive. Each micro chart does however provide a hyperlink to a PBI report that gives the same info on the same type of chart with drilldown and detail as well as other pages so you can explore the data properly and interactively in PowerBI. Some of the micro charts are more like KPIs and these link to conventional SSRS reports to show the detail behind a count of failed transactions for example. These reports open in a new browser TAB/Window but attach to the same tabular model so everthing uses the same data. Yes DAX into SSRS, its a joy!

 

 

We also have "shared" datasets in other PBI reports where the source isn't SSAS Tabular. We found we could create old fashioned SSRS datasets, the kind you would use in RDL reports. We can then deploy these DataSets and connect to them using the oData connector in PBI Desktop. This is explained in these blog posts

 

http://dataap.org/blog/2018/01/17/power-bi-report-using-ssrs-shared-datasets/

 

https://blog.crossjoin.co.uk/2017/10/15/exploring-the-new-ssrs-2017-api-in-power-bi/

 

This means we can have a common dataset used in different PBI reports. We could, in theory, query these oData sources with the same D3 charts we used in the dashboard instead of using DAX via ADO.Net.

 

Sorry if I just confused thingsin my original reply

 

 

 

 

 

View solution in original post

6 REPLIES 6
shiboli
Microsoft
Microsoft

Here's an example to embed the report in sharepoint: https://docs.microsoft.com/en-us/power-bi/report-server/quickstart-embed

 

You can follow the same to add ?rs:embed=true to the url to embed in an iframe. 

 

As for REST API, all the publicly supported API is documented in swaggerhub: https://app.swaggerhub.com/apis/microsoft-rs/pbirs/2.0

Thanks so much for this, it is really useful to know that the APIs are already implemented.

So to make sure i understand correctly, i just reuse these APIs in my website to get the data from PowerBi report Server to appear in the frontend GUI, correct?

 

One more thing, if you know anything about if we can use SQL Server 2014 Enterprise license that i already have for Report Server or should i buy a new one for 2017 version?

 

Thanks once more.

stpnet
Impactful Individual
Impactful Individual

I suspect you may need to get a new license for SQL Server 2016/2017. Make sure you get Enterprise Edition with Software Assurance to get you PBI-SSRS license for free.

 

You can only embed SSRS-PBI reports using an iframe. You can pass some parameters in as part of the URL/request but its pretty limited currently, hopefully the new version (due soon) will improve the functionality in this area. I'm not entirely sure how this will work security wise. I assume the service account for the service rendering your AngularJS website is passed as the auth user to PowerBI (AD integrated security is what you get out of the box) so that account would need some permissions to browse the reports.

 

The REST API is for managing the server and content not for rendering the reports. To quote from the API reference

 

https://app.swaggerhub.com/apis/edugonz/PBIRS/2.0

 

The Power BI Report Server REST API provides programmatic access to the report server catalog.
For example, basic CRUD operations can be done on folders, reports, KPIs, data sources, datasets, refresh plans, subscriptions, etc.

The REST API can also be used to provide more advanced functionality, such as:

  • Navigate the folder hierarchy
  • Discover the contents of a folder
  • Download a report definition
  • Modify default report parameters
  • Change or execute a refresh plan
  • A whole lot more

What it doesn't do is enable reports to be rendered. If you are looking for the datasets that are used by the reports then these aren't directly available. You can query old fashioned SSRS datasets via oData as the REST API provides oData endpoints for these via the REST API. But i suspect that's not what you are after.

 

We have implemented a set of internal dashboads here in an AngularJS web app and had intended to use PBI reports for various aspects. We've ended up doing most of them using a D3 graph library with queries to the Tabular model that sits behind all our PowerBI. This renders much smaller simpler charts way faster. The charts are more like KPIs. If the users click through on one of these micro charts they get bounced to a PowerBI report that gives them drilldown/slicer capability. 

PeterNagy
Frequent Visitor

I cannot thank you enough for this outstanding detailed answer.

 

But through my investigations i heared that we can build the Website with embedded dashboards from Power BI using REST API Calls, but i guess that according to your experience you discovered that this is not applicable, am i right ?

Yet i am a bit confused regarding the oDATA part using the D3 Graph Library you used, is this a way to embed the powerbi dashobard in an Angular website that can be a responsive design with responsive dashboards ?

I am a bit new to embedding a dashboard or a report to a website, that's why i am bit overwhelming.

 

Thanks so much once more for your time.

stpnet
Impactful Individual
Impactful Individual

Yes. We discovered that we couldn't "embed" dashboards from the on premise version in the way that you can using the cloud version, all you can do is an iframe with some params passed through on the URL. We found this to be quite demanding in terms of infrastructure. You have no real control over the aspect ratio so using it to produce ribbons of charts above or to the side of other content proved impractical. rendering each micro chart as an embedded iFrame proved to be way too much demand on the server and indeed the client (each iFrame gets the entire javascript and angular app that renderss the PBI report)

 

If you are using the cloud version then there's a ton of API stuff for embedding using a REST API and stuff

 

https://docs.microsoft.com/en-gb/power-bi/developer/embed-sample-for-customers

 

But this only works for cloud deployed PBI, this fucntionality is not available on the on premise PBI-SSRS product.

 

So we resorted to using simple D3 charts for all of this. All these micro charts respond to slicers we have built into the app to control the time period you are looking at, the currency and the position in the business heirarchy to look at/start at etc. But the slicers change everything on the screen. each of the D3 charts issue DAX queries (that are parameterised) using an ADO.Net connection to a SSAS Tabular instance, the params get changed as the slicers get changed. So not really very interactive. Each micro chart does however provide a hyperlink to a PBI report that gives the same info on the same type of chart with drilldown and detail as well as other pages so you can explore the data properly and interactively in PowerBI. Some of the micro charts are more like KPIs and these link to conventional SSRS reports to show the detail behind a count of failed transactions for example. These reports open in a new browser TAB/Window but attach to the same tabular model so everthing uses the same data. Yes DAX into SSRS, its a joy!

 

 

We also have "shared" datasets in other PBI reports where the source isn't SSAS Tabular. We found we could create old fashioned SSRS datasets, the kind you would use in RDL reports. We can then deploy these DataSets and connect to them using the oData connector in PBI Desktop. This is explained in these blog posts

 

http://dataap.org/blog/2018/01/17/power-bi-report-using-ssrs-shared-datasets/

 

https://blog.crossjoin.co.uk/2017/10/15/exploring-the-new-ssrs-2017-api-in-power-bi/

 

This means we can have a common dataset used in different PBI reports. We could, in theory, query these oData sources with the same D3 charts we used in the dashboard instead of using DAX via ADO.Net.

 

Sorry if I just confused thingsin my original reply

 

 

 

 

 

PeterNagy
Frequent Visitor

Thanks so much for your outstanding help, it is much appreciated.

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.