Myujth

I would like to use an SSRS dataset as a datasource for PowerQuery, either with [Excel 2013] or [PowerBI Desktop]. The report server version is [SSRS 2016] [Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) - Enterprise Edition].

My goal is for end users to have access to the SSRS Datasets via
PowerQuery (either Excel or PBIDesktop). Users connect and build
reports against the data sources; and IT developers manage the SSRS
data sets.

Is upgrading to PBIRS the only way to connect PQ to SSRS Report Dataset? Will upgrading to PBIRS solve the requirement?

I know that I can export an ODATA feed from SSRS, and this will create an .ATOMSVC file that Excel native (Excel Data Tab) can connect to (steps at: "Generate Data Feeds from a Report (Report Builder and SSRS)": Docs-MSFT/SQL/SQL Server/Reporting Services/Report Builder/ and | steps at: "Reporting Services ATOM Data Feeds": mssqltips 2136).

PQ cannot connect to .ATOMSVC, it's ODATA connection requires a URL not a file.
When I google "powerquery connection to SSRS dataset", I found a few articles that indicate I should upgrade to PBIRS (PowerBI Report Server) from SSRS 2016 Server.

excel-pq-from-odata feed-url.png

Online sources

Users can build their own PowerBI reports based off of SSRS datasets

(FUTURE STATE): This would be a valid URL with PowerBI ReportServer.

• 
  http://myssrsname/Reports/api/v2.0


• 
  http://myssrsname/Reports ... works for me to access the report portal
  


• 
  http://myssrsname/Reports/api/v2.0 ... HTTP ERROR 404: No webpage was found for the web address
  

(HOW TO): Access SSRS data with PowerBI

• https://docs.microsoft.com/en-us/power-bi/report-server/access-dataset-odata

(HOW TO): Exploring The New SSRS 2017 API In Power BI

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

HOW TO UPGRADE, SSRS to PBIRS

(DOWNLOAD): PowerBI ReportServer, 180-day free trial:

• 
  https://powerbi.microsoft.com/en-us/report-server/

(HOW TO): Migrating from SSRS to Power BI Report Server

• https://deevita.com/2018/10/24/migrating-from-ssrs-to-power-bi-report-server/

(HOW TO): Install and configure Power BI Report Server and Power BI Desktop

• https://www.mssqltips.com/sqlservertip/5000/install-and-configure-power-bi-report-server-and-power-bi-desktop/

(PRICING-PBIRS): POWER BI REPORT SERVER: A modern on-premises BI solution

• 
  https://www.blue-granite.com/blog/power-bi-report-server-revisited


• SQL Server Enterprise edition with active Software Assurance (SA)

(PRICING- PBI Pro):

• 
  https://powerbi.microsoft.com/en-us/pricing/


• $10 per user/ mo