Power BI's two best kept secrets: the PBIRS REST API and Power BI REST API

PBIRS REST API (public, completely customizable API)

If you use PBIRS to publish PBI reports to a centralized portal, or just want the ability to programmatically control your PBI development, deployments and system maintenance, there is a PBIRS API that you may not be aware of that can supercharge your PBI development environment.

It is not published but it is completely documented on SwaggerHub.com

It bears a close resemblance to the original SSRS v1 and v2 APIs, but in addition to all the SSRS functionality, it provides functionality for managing PBI reports, data sources, PBI data refreshes (data is refreshed on demand in SSRS reports), scheduling of PBI reports, managing uploaded ExcelWorkbooks and many other really useful features that developers can leverage to get the most out of your Power BI environment.

Publishing, updating PBI reports, data sources, posting ExecutionLog entries and more is made available via the PBIRS API

Power BI REST API (MSFT proprietary API)

And then there is the all-powerful Power BI REST API which serves as a successor to the ReportExecution2005.asmx and ReportService2010.asmx Reporting Services SOAP APIs that the original SSRS REST API was built upon. 

This API is not publicly documented (the SDKs are, however, which suffices for most usage scenarios) and not as extensible as original the SSRS REST API (ie. you need to authenticate with Entra and an online MSFT Power BI account; no custom authentication is possible), but it provides most all of the abundant paginated report and data source functionality in the original SSRS SOAP API along with a cornucopia of new API operations for managing Power BI users, workspaces and content.

The following is an example of using the Power BI REST API with PowerShell (using the MicrosoftPowerBIMgmt PowerShell module, if connecting via .NET you'd use the Microsoft.PowerBI.Api .NET library/Nuget package; an example of using the Power BI REST API in .NET can be found here):

This demonstrates getting a list of PBI workspaces, reports and getting an individual PBI report by name

With these two APIs organizations can continue to manage their SSRS/PBIRS environments and start managing their cloud-based Power BI environments- programmatically.

References: 

https://app.swaggerhub.com/apis/microsoft-rs/SSRS/PBI3.0

https://learn.microsoft.com/en-us/rest/api/power-bi/reports 

https://github.com/Microsoft/PowerBI-CSharp

https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/Export-PowerBIReport.ps1

The slow death of SSRS and future of "PBIRS"

Microsoft has announced the inevitable- SSRS is not long for the world.

SSRS is now PBIRS and sadly, after 2033, PBIRS is likely going away in favor of costly, subscription-based MSFT reporting

There will apparently be no new features for RDL-based paginated reports moving forward unless MSFT changes course. All MSFT investments in data visualization and business intelligence are going to Power BI and other "Power Automate" tools.

For over 20 years, SSRS and RDL-based paginated reports have served a vital role supporting business intelligence and data visualization

This is not entirely a bad thing but I do have some thoughts on the sad reality that is the current state of Power BI, namely- its massive feature and extensibility limitations with respect to its predecessor, SSRS.

The first big one is report access. SSRS reports were available to anyone in the enterprise. And with custom authentication like extRSAuth, reports could be made available to any logged-in SaaS customer or otherwise authenticated client- for free. Most everything in Power BI requires a monthly subscription.

Note that the "Free" pricing tier has significant limitations (not very useful to develop reports in isolation...)

The second big one is scheduled reports and report archiving for auditing and comparison purposes via report snapshot functionality. Scheduled reports and snapshots are made possible via the PBI Report Server on-prem engine, but as the PBIRS on-prem engine is only supported through January of 2033 the question for organizations considering continuing with RDL-based schedulable and snapshottable reports is- "will we be cut off (unsupported) after January 2033?".

Sadly, everything is moving to a subscription-based model and this has everything to do with short-term "value demonstration" and the prioritization of short-term profits over long-term product excellence and long-term customer satisfaction. (more on this in a future post...).

Not an ideal way to present an external customer-facing report...

If I had to make a bet on the future, based on MSFT's current avaricious and short-sighted, "Subscription-and-AI-everything-customer-opinion-and-developer's-be-damned*" business direction- SSRS and its successor PBIRS will be completely phased out after 2033 and the only option will be Power BI reports, with Power BI offering some expensive, newfangled version of schedulable .pbix reports. Probably called something ridiculous like "Fabric Delivery Service". I doubt they will continue to support snapshot auditing capabilities, but they should.

Lastly, a sad consequence of the universal move away from code sharing, "actually free/unlimited use" software and software extensibility in general, is that Power BI binary files (.pbix) cannot be utilized outside of being decompiled by a Power BI editor. In the heydays of SSRS, you could view any .rdl, .rsd or .rds file and immediately inspect a report, dataset or data source (XML) file and make edits on the fly through a text editor or an API. This is no longer possible.

There have been innumerable SSRS scripts implemented at organizations to update large batches of data sources, datasets and reports to point to new data sources, data fields or to make mass updates to column text, report labeling or table definitions. So the removal of this functionality is kind of a big (raw) deal.

Paginated RS Reports (.rdl)  

RDL report files are XML-based and report definitions are clearly defined in the files; fortunately PBIRS is supporting paginated reports and the PBI Report Server database and engine for several more years.

Interactive PBI Visualizations (.pbix)

PBI report files are illegible compiled binary code; report definitions can only be decompiled, viewed and modified through tools like the online Power BI editor or downloadable Power BI Desktop programs. PBI interactive reports are the MSFT-recommended approach for all data reporting (except apparently, their own as is demonstrated below in the example of a SSMS 21/SQL Server 2025 standard, RDL-based database report).

There is a long way to go before SSRS reports disappear from the MSBI landscape completely however; SSRS will continue to be supported until January 11, 2033 according to Microsoft's recently posted Reporting Services consolidation FAQ.

In fact, if you look at Microsoft products like SSMS 21, you'll see regular old SSRS/RDL reports being rendered through the Report Viewer control when running reports for SSIS execution history, Job execution history, Database Standard Reports and many other SSMS reports.

Right-click any SQL Server database and you can view a suite of useful (if outdated looking) Standard Reports 

Ngl these reports look very 2008ish (3D is not recommended for serious dataviz)- but they are indeed SSRS/RDL

Comparing search terms via Google Trends it is clear that the push to Power BI accelerated in 2017 and has ramped up to the point where general interest in SSRS is negligent compared to interest in Power BI, despite SSRS offering robust, extensible on-prem report solutions for free and Power BI be buggy, considerably "locked down" and charging a relatively expensive monthly per-user subscription.

Google search interest in Power BI eclipsed SSRS in 2017 and has only risen since

But this is merely how marketing-sales-product singularity and product obsolescence works. If Galactus (MSFT) deems it so, it'll be so.

😕

😞

*we have come a long, long way from then-Microsoft CEO Steve Ballmer's infamous but earnest, "Developers!!, Developers!!, Developers!!" rant at a Windows conference in 2006. 😐

References:

https://www.mssqltips.com/sqlservertip/6867/ssrs-vs-power-bi

https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-consolidation-faq?view=sql-server-ver16

Embedding RS reports, HighCharts, and PBI interactives in ASP.NET Core MVC views

SSRS embed

 @model ReportView  
 <script>  
 </script>  
 <div class="container" style="background-color:#ffffff">  
   <section>  
     <div style="background-color:#ffffff; box-shadow: 5px 10px 8px #888888;">  
       <iframe height="800" width="900" src="@Model.SelectedReport.Uri" class="body-box-shadow"></iframe>  
     </div>  
   </section>  
 </div>  

This is the entire view of _Report.cshtml which renders the RS report; see the extRSAuth project for how to enable non-Windows AD SSRS client authentication

The <iframe> is rendered with the URL of the report on your report server which renders a ReportViewer control view of the report

HighCharts embed

 <script src="https://code.highcharts.com/highcharts.js"></script>  
 <script src="https://code.highcharts.com/themes/adaptive.js"></script>  
 <head>  
   <script>  
     $(document).ready(function () {  
       Highcharts.seriesTypes.line.prototype.getPointSpline = Highcharts.seriesTypes.spline.prototype.getPointSpline;  
       Highcharts.chart('potusApproval', {  
       title: {  
         text: 'Trump Job Approval',  
         align: 'left'  
       },  
       subtitle: {  
           text: 'Source: <a href="https://api.votehub.com/polls?poll_type=approval&subject=Trump" target="_blank">VoteHub</a>.',  
         align: 'left'  
       },  
       yAxis: {  
         title: {  
           text: 'Approval'  
         }  
       },  
       legend: {  
         layout: 'vertical',  
         align: 'right',  
         verticalAlign: 'middle'  
       },  
       plotOptions: {  
             area: {  
               pointStart: '1/1/2025',  
               relativeXValue: false,  
               marker: {  
                 enabled: true,  
                 symbol: 'circle',  
                 radius: 2,  
                 states: {  
                   hover: {  
                     enabled: true  
                   }  
                 }  
               }  
             }  
           },  
       series: [{  
         name: 'Approve',  
         data: [ @string.Join(", ", Model.HighChartsModel.Approves) ]  
       }, {  
         name: 'Disapprove',  
         data: [ @string.Join(", ", Model.HighChartsModel.Disapproves) ]  
       }],  
     });  
   });  
   </script>  
 </head>  
 <table style="width: 100%; height:50%">  
   <tr><td><div id="potusApproval"></div></td></tr>  
 </table>  

The same data can easily be embedded as a HighCharts visual using some JS and HTML; this example uses the simple HighCharts LineChart

The above HighCharts embed code renders this line chart

Power BI embed

The easiest Power BI embedded method is to use the Developer Playground "demo code" script; select File >> Developer Playground

Next, click the "Set up now" button on the upper right side of the following screen

Here you are presented with the embed <iframe> script that you put into your view... 

 <iframe title="PBI_Report" style="width:100%; height:73%" src="https://app.powerbi.com/reportEmbed?reportId=695fe0f1-5c9e-497d-8913-e59f0b939ac4&autoAuth=true&embeddedDemo=true" frameborder="0" allowFullScreen="true"></iframe>  

And, voila! You have a Power BI visual embedded inside your web app now

Reference: https://learn.microsoft.com/en-us/power-bi/developer/

Source: https://github.com/sonrai-LLC/extRS/tree/main/ExtRS.Portal | https://extrs.net

SQL Server 2025's sp_invoke_external_rest_endpoint with OPENJSON CTE for quickly and easily getting data from REST APIs

SQL Server 2025 introduces a convenient way to get data from a REST API endpoint directly through T-SQL and SQL Server utilities.

Outlined in lime green are the two items SQL Server 2025 handles well, discussed in this post

Prior ways of doing this usually involved using MSXML2.XMLHTTP (a COM object provided by Microsoft XML Core Services) through extended stored procedures, but with MSSQL 2025, there is a new SP, sp_invoke_external_rest_endpoint that is very readable and easy to use to get JSON (or XML) from an API response.

This brief article describes what an SP to get this data may look like, as well as the code to parse the JSON in the response to format the result as a table (vs. sending back all the JSON for the client to parse).

Here is an SP which fetches polling data for the Approval Polling data on current U.S. president Donald Trump:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	colin fitzgerald
-- Create date: 20250625
-- Description: SP to fetch data from VoteHub API
-- =============================================
CREATE OR ALTER PROCEDURE dbo.sp_GetVoteHubPollingData
	@LongView BIT = 0
AS
BEGIN
SET NOCOUNT ON;

EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;

DECLARE @ret AS INT, @response AS NVARCHAR (MAX);

EXECUTE
    @ret = sp_invoke_external_rest_endpoint
    @url = N'https://api.votehub.com/polls?poll_type=approval&subject=Trump',
    @headers = N'{"Accept":"application/json"}',
    @method = 'GET',
    @response = @response OUTPUT;

;WITH ResponseContent AS
(SELECT [key], [value] FROM OPENJSON(@response)),
 ResponseJson AS
(SELECT [value] FROM OPENJSON((SELECT [value] FROM ResponseContent WHERE [key]='result')))

SELECT --value,
--id,
pollster,
[subject],
poll_type,
sample_size,
created_at, 
approve,
disapprove
FROM ResponseJson
OUTER APPLY OPENJSON(value) WITH(
  id nvarchar(255), 
  pollster nvarchar(255),
  [subject] nvarchar(255),
  poll_type nvarchar(255), 
  sample_size int, 
  created_at datetime,
  approve decimal '$.answers[0].pct',
  disapprove decimal '$.answers[1].pct'
)
WHERE created_at >= CASE WHEN @LongView = 0 THEN dateadd(mm, -3, getDate()) ELSE created_at END 
ORDER BY created_at DESC

EXECUTE sp_configure 'external rest endpoint enabled', 0;
RECONFIGURE WITH OVERRIDE;

END
GO

And here is the design view of the data source for a report using this polling table data:

And here is the design view of the report that will use this data:

If we CREATE dbo.sp_GetVoteHubPollingData as stored procedure on a database (in this case, I created it in 'master') that our data source connects to, then we can deploy the report to a Report Server or Power BI Report Server and run it:

This is the report as rendered within Power BI Report Server's Portal using extRSAuth for authentication and authorization

This is the report as rendered within extRSAuth custom PBIRS reporting portal on extrs.net 

Lots of neat stuff you can do with the new features of SQL Server 2025- check 'em out.

Next up: embedding a PBI version of this report in extrs.net, and embedding a HighCharts JS version of this report in extrs.net- all using this dbo.sp_GetVoteHubPollingData SP that uses sp_invoke_external_rest_endpoint.

References:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql

https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server