One of the new features in Reporting Services 2017 is the new REST API. The announcement is here:
And the online documentation for the API is here:
https://app.swaggerhub.com/apis/microsoft-rs/SSRS/2.0
Interestingly, the new API seems to be OData compliant – which means you can browse it in Power BI/Get&Transform/Power Query and build your own reports from it. For example in Power BI Desktop I can browse the API of the SSRS instance installed on my local machine by entering the following URL:
[sourcecode language='text' padlinenumbers='true'] http://localhost/reports/api/v2.0 [/sourcecode]
…into a new OData feed connection:
This means you can build Power BI reports on all aspects of your SSRS reports (reports on reports – how meta is that?), datasets, data sources, subscriptions and so on. I guess this will be useful for any Power BI fans who also have to maintain and monitor a large number of SSRS reports.
However, the most interesting (to me) function isn’t exposed when you browse the API in this way – it’s the /DataSets({Id})/Model.GetData function. This function returns the data from an SSRS dataset. It isn’t possible to call this function direct from M code in Power BI or Excel because it involves making a POST request to a web service and that’s not something that Power BI or Excel support. However it is possible to call this function from a Power BI custom data extension – I built a quick PoC to prove that it works. This means that it would be possible to build a custom data extension that connects to SSRS and that allows a user to import data from any SSRS dataset. Why do this? Well, it would turn SSRS into a kind of centralised repository for data, with the same data being shared with SSRS reports and Power BI (and eventually Excel, when Excel supports custom data extensions). SSRS dataset caching would also come in handy here, allowing you to do things like run an expensive SQL query once, cache it in SSRS, then share the cached results with multiple reports both in SSRS and Power BI. Would this really be useful? Hmm, I’m not sure, but I thought I’d post the idea here to see what you all think…
The dataset caching is certainly an interesting route to improve the performance of the refresh of PBI models. The reports in the PBI model likely won’t be affected since they don’t go directly to the SSRS dataset.
Interesting idea Chris. Specifically the one about custom connectors.Might actually use it soon.
One thing about the POST request maybe a little bit deceiving, as I can make POST requests with Web.Contents for other web services. The only limitation I can think of is passing the Windows credentials on.Which MS considers a no-no for post requests.
Ah, but as I say in the post, you *can* make POST requests with Windows authentication from a custom data connector (I know you can’t in Power BI Desktop).
Looks interesting, however I’m curious why the old SSRS report data source was never included given it’s part of the excel powerpivot sources. We use SSRS 2012 and the folder level security would give us a nice way to provide an intermediate step between ssrs data source old report.
Looks interesting, however I’m curious why the old SSRS report data source was never included given it’s part of the excel powerpivot sources. We use SSRS 2012 and the folder level security would give us a nice way to provide an intermediate step between ssrs data source old report.
This is really interesting to me. We have had use cases where we would like to share data from an existing SSAS tabular model to an application through managed code. The pain point has been the need to have a developer write DAX in managed code. Can I assume we could create the SSRS data set based on the SSAS tabular model and expose the data to the application via the post request?
I think it’s funny that you can read from SSRS using ODATA, but SSRS itself still can’t use ODATA as a data source.
That being said, I wonder if a use for your example would be to accomplish some kind of incremental refresh with SSRS caching and use that as the Power BI data source.
Is it possible to get data into Power BI Desktop from SSRS 2014 using OData? Or this is a feature available only for SSRS 2014?
No, not easily I think because as far as I understand it the OData supported by older versions of SSRS isn’t actually proper OData
“turn SSRS into a kind of centralized repository for data”… sooo intriguing. I am on the cusp of overhauling our IT reporting strategy. We use SSRS liberally (this won’t go away, only grow). I have even had users access SSRS tablix object data with ODATA to Excel Native (the .ATOMSVC method was well received and appreciated). We are looking to move from *huge* DW ETL for everything and I *really* like the ssrs dataset feed to PBI. Can you tell me how to achieve this?? I do not know how to make a custom data connector. We have SSRS 2016, but we would certainly be open to upgrading to SSRS-PBIRS.
FYI: You CAN directly leverage the odata from an SSRS-SharedDataset, no POST request needed
https://yourReportServer/reports/api/v2.0/Datasets(00a6c2d1-c205-4c7f-b861-ad6afc6984e3)/data
–> Replace the GUID with “your” dataset guid (browse the “Datasets” entity to get the guid)
-> in PBI “get data” select “OData” and check “advanced” where you can set “include open type columns”
for detailed walkthrough see here: https://www.c-sharpcorner.com/article/rest-apis-for-power-bi-report-server/
Can we use Power BI report server REST API to embedd in a website, to give reader to interact with embedded report , which is hosted on-Premises PBI Report Server. I know that PBI services has this feature but not sure about PBI On Premises Report Server. If you have any information kindly share it