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.
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, 2033according 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. 😐
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
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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: colin fitzgerald-- Create date: 20250625-- Description: SP to fetch data from VoteHub API-- =============================================CREATEORALTERPROCEDURE dbo.sp_GetVoteHubPollingData
@LongView BIT=0ASBEGINSET NOCOUNT ON;
EXECUTE sp_configure 'external rest endpoint enabled', 1;
RECONFIGURE WITH OVERRIDE;
DECLARE@ret ASINT, @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 >=CASEWHEN@LongView =0THEN dateadd(mm, -3, getDate()) ELSE created_at ENDORDERBY created_at DESCEXECUTE sp_configure 'external rest endpoint enabled', 0;
RECONFIGURE WITH OVERRIDE;
ENDGO
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.
extRS is a .NET assembly, available as a Nuget package here, which is a utility for getting data out of SSRS, FedEx, UPS, USPS, financial markets, among several other useful functionalities.
extRS was initially designed as a set of helper functions to access things in RS, but it evolved to contain a lot more..
Here are a couple examples of some of the useful things you can do with extRS:
Create, Get and Delete an SSRS Report Subscription
"A meaningful and useful abstraction that is able to withstand the test of time is very difficult to design. The main difficulty is getting the right set of members—no more and no fewer. If an abstraction has too many members, it becomes difficult or even impossible to implement. If it has too few members for the promised functionality, it becomes useless in many interesting scenarios." -Designing for Extensibility in .NET (Cwalina, Abrams, Barton)
ExtRS Portal provides a blueprint for extending the functionality of Reporting Services
extRS (pronounced, "extras") is a modern SSRS client for distributing and reading reports; with some extras. A demo of the app is linked here: https://extrs.net
The audience is SSRS report users (you know, the people you need to justify having enterprise reporting in the first place). So things like applying item-level RS security, managing users, and adding, editing and deleting SSRS catalog items and other system-level properties are not part of this client- at least not yet.
The aim here is to make SSRS at least slightly more interesting, accessible and useable for information consumers. This particular deployment of the extRS.Portal web client is connected to a report server with custom authentication (extRSAuth) which gets past the normally required "Windows authentication" hamstring of the default SSRS installation.
This wrapper and extension UI not only improve the user authentication experience and dynamism of SSRS parameter behaviors in the UI but also provide SSRS admins and other users with rich enterprise reporting usage and delivery data.
Enabled are most of the features contained in Reporting Service's built-in Report Portal at /reports.
I have disabled some things like deleting and uploading items for the sake of keeping my demo of the app small and simple.
The redirect loop looks like this and, in Edge, will display the error message: "[domain] redirected you too many times"
If you are trying to render the SSRS ReportViewer control within an <iframe>, you may run into a CORS issue that manifests in a series of 302 (Found) responses and an infinite redirect loop between ReportViewer control (ReportViewer.aspx) and Logon.aspx.
As of SSRS 2022, without an explicit instruction to allow CORS, ReportViewer cannot be rendered within an <iframe> on an origin different than the origin of the report server.
If you are using custom authentication, the solution is easy enough. Just add cookieSameSite="None"andenableCrossAppRedirects="true" to the authentication <forms> tag in the report server's web.config.
"Generally, Power BI paginated reports (SSRS reports) are optimized for printing, or PDF generation. Power BI reports are optimized for exploration and interactivity."
So, what really IS up with MSBI these days? Is SSRS getting shuttered? PBI has paginated RS-like reports, but not a lot of the other features SSRS provides. Microsoft marketing will continue to hate people like me who would go to great lengths to help keep alive an aging reporting technology. But the thing is- SSRS simply does the job for the vast majority of reporting use cases. In the last 18-20 years, there have been no major advancements in scheduling and snapshotting and otherwise caching and managing and distributing electronic information. And SSRS has all of that built in.
Similar BI products- but they'll both be around for a while, with PBI eventually subsuming all the most useful SSRS tech
Nearly all of the advancements in reporting technology have come on the presentation and client-side. We can now create beautiful ad-hoc analysis and brilliantly composed interactive charts and other data presentations. But this all comes with a not insignificant price ($10 per user/month). And beyond the price, much like Azure SQL (vs. a genuine "Microsoft SQL Server" VM) and the extremely limited Azure SQL Workbench (vs. SSMS), there is a lot that Power BI cannot do well or at all.
You may have noticed the built-in SSRS reports in SSMS19's new QueryStore feature. These are very useful reports that can give DBAs an idea of how queries and being processed and what processes are consuming the most CPU. And it is a good example of a company "eating its own dog food".
I've seen SSRS installations that contained thousands of reports representing trillions of dollars of value, categorized and summarized with realtime security ownership, counterparty, price and other core trade data. Several of these business-ctitical reports had scheduled delivery and were cached and snapshotted programmatically.
Little old SSRS is a quiet but reliable business data spartan. To my surprise it is actually quite popular in the investment banking industries where stock valuations and company summaries on reports are a big part of the lifeblood that drives investment banking decision making.
A professionally developed PBI report looks and behaves more like an interactive BI dashboard- this is a good example of a good PBI report
And with a little bit of customization magic via things like ExtRSAuth, ExtRSNET48, ExtRS and other RS extension tools, SSRS and Power BI can be tailored-made to serve as a uniquely effective symbioses of print formatted, scheduled and data-driven management reports (SSRS) and ad-hoc or OLAP-based interactive data analysis charts with data visualizations (Power BI).
To answer the question of "when will SSRS be end of life?", I would say that SSRS isn't going away anytime soon. Microsoft has decided to combine SSRS and PBI (RS, .rdl reports are the "Paginated Reports" in PBI) in a way that serves both platforms. The PBI3.0 REST API indicates as much as the combined SSRS/PBI API offers a plethora of functionality that .NET developers can use to get the best of both worlds (SSRS and Power BI) and customize RS and/or PBI dashboards to support unique business processes.
A print-formatted SSRS report- great for standardized, templated data reporting
The choice in what tool or tools you will enrich your printed reports and data visualizations with, is yours. Keep in mind that many organizations make use of both- with SSRS getting equal to more attention than PBI even to this day- not only because of the huge, global SSRS install base with all of these currently running SSRS reports- including many which support critical business and governmental processes across the globe. But also because Power BI requires a monthly subscription fee :( . Freeware seems to be slowly dying. Let's hope things change with the next version of SQL Server and maybe we'll get free* PBI.
SQL Server 2025? Happy reporting and data analyzing. Always remember that PBI and SSRS serve different organizational needs- ad-hoc analyzation of data and pixel-perfect, professional, print-ready reports, respectively.
*(at least a free "tier"? I mean c'mon MSFT..... developers want to CREATE, and MSBI data visualization creativity is dying behind that paywall)- SSRS and PBI should be free and work hand in glove. Anything less is a mistake and a gigantic missed opportunity, imho.
Fortunately, ExtRSAuth code doesn't require any updates to work with MSSQL SSRS 2022
ExtRSAuth for custom SSRS security
This assembly, forked from the Microsoft Custom Security Sample extends and improves custom authentication to allow for mechanisms other than user/pwd credential check and to offer a seamless pass-thru of the Login page if something present in the HttpRequest verifies that user is already authenticated. For instance, the user already has an app token from an app that communicates with the report server, and you require the communications with the report server to not involve any intermediate screen or login UI. The user just wants to auth as fast as possible and get to their report, right?
What does ExtRSAuth do to authenticate SSRS user connections?
For direct URL report server access, the default here is to allow local connections, which grants Admin rights for any local requests. If the SSRS request is external, a fallback option accepts an AES 128-bit encrypted querystring from the calling app, and the application, if decryption works, is authenticated and allowed to communicate using a read-only SSRS user; any exception thrown indicates the request is neither from a local connection nor a secure request from the external app.
ExtRSAuth gives SSRS environments the freedom from MS Active Directory that they deserve
To secure the built-in SSRS REST API v2.0 access, you can simply customize the LogonUser() and VerifyPassword() methods in AutneticationExtension.cs and AuthenticationUtilities.cs, respectively:
If you don't perform a security check here, the SSRS REST API will be open to anyone who knows the SSRS admin username
As you can see, this is but one of many approaches we can take with ExtRSAuth in an SSRS-connected application or business environment. Any type and granularity of custom authentication and level of authorization is possible. The only ingredient needed is a .NET developer or developers willing to customize a pretty basic .NET security model.
Real-world applications
This Custom Auth assembly has been tested with (1) several .NET Framework 4.8 and .NET 5, 6, 7 and 8 web and mobile applications, (2) with the SSRS API and all its operations, (3) with the SSRS /ReportServer and the /Reports management web interface as well as (4) Visual Studio 2022 Reporting Services projects (report designers can seamlessly deploy Report Server projects from VS to the Report Server with ExtRSAuth).
After running InitalizeExtRSAuth.ps1, a successful installation will output the above
Demonstration
This YouTube explainer video describes the SSRS external user authentication problem that ExtRSAuth addresses.
Requirements
This plug-in relies on SSRS (2016 or later), and a report server configuration as described in Microsoft's Reporting Services Custom Security Sample
-Replace [your_sym_encr_key] with your symmetric encryption key. Clients can encrypt SSRS URL access querystring with Sonrai.ExtRSAuth.Excryption.Encrypt() or a similar 128-bit AES encryption implementation, or modify Encrypt() with any encryption algorithm and key and block sizes.
One of the limitations of SSRS is that it cannot be used outside of a Windows environment (security is all dependent on Active Directory and Windows User accounts). Unfortunately app impersonation does not work cleanly enough as it will prompt users for credentials when they 1st authenticate to the Report Server. That doesn't cut the mustard for user expectations of public-facing apps.
Authentication only works as far its interoperability can reach (needs to reach beyond Windows Auth)
So, to get around this, Microsoft has provided a workaround in the form of the CustomAuthentication example. It provides a basic way to authenticate using forms-based authentication with a login page. This does work, "technically". But this also will not work if we want our report authentication to be invisible/seamless to a user who is already authenticated to the main app (that provides SSRS-based reporting features).
Why make a user auth 2x?
So.... (and this isn't incredibly clever but it's useful): enter this extension of the CustomAuthentication example that is Local-only access by default, but suggests several overrides for authentication.
Here is the crux of how the CustomAuth can work in many different ways simply via modifying Login.aspx.cs of the Microsoft example:
private void Page_Load(object sender, EventArgs e)
{
//Your secret authentication sauce goes here..
//appHash should get dynamically generated from the app calling SSRS (ideally for each request if performant enough)
//ie.
//if (CheckAuth(System.Web.HttpContext.Current.Request.Cookies["origAppHash"].ToString()))
//if (CheckAuth(System.Web.HttpContext.Current.Session["otroAppHash"].ToString()))
if (System.Web.HttpContext.Current.Request.IsLocal)
FormsAuthentication.RedirectFromLoginPage("daylite", true);
}
private bool CheckAuth(string appHash)
{
//DecodeAndCryptoChecks on appHash
return true;
}
MS' Example uses Page_Load(); presumably Page_PreLoad() or Page_Init() would also work here- 'just an HttpRequest eval
The idea behind this branch (closed as soon as PR'd as I don't expect MS to integrate this but I did want to not-so-subtly nudge them to explain SSRS' custom extensibility better) is not a defined solution- it is to demonstrate how you can interface with the authentication and authorization operations of SSRS service to achieve virtually any kind of custom security behavior or compatibility that you require.
This app's function/purpose is to use Google Maps API to get geographic data and render locations on maps with editable pins (much like... many apps these days- it is kind of becoming an expectation for any application/service involving a location street address).
In this way you can record or plan the state(s) of an event or location at some particular street address. Or just have a geographic representation of some important locations that you can then print and have a custom map for.
This is a proof-of-concept app illustrating what you can do with a little JavaScript, a web app and the Google Maps API
The code below takes locations records (containing the lat/long of the geographic coordinate) from a database and then initializes the Google Map with some options (I omitted many for brevity). The main interesting thing the code does below, is when it renders the pins (addMarker() function) it adds an event listener to delegate the task of popping up an ASP.NET Core-bound edit modal when a user clicks the pin.
On the Add and Update side as far as mapping Lat/Long from Street, City, State- that is all handled by the incredibly useful GoogleLocationService provided as a Nuget package for .NET Core apps.
Other than that it is just standard JavaScript- Google Maps API does virtually all of the geocoding and map visualization heavy lifting.
The crux of the utilization of the API code (callback and map rendering) is this:
<script>
function initMap() {
var map = new google.maps.Map(
document.getElementById('map'),
{
center: new google.maps.LatLng(@Model.CenterLat, @Model.CenterLong),
zoom: 8
}
);
var pins = @Html.Raw(Json.Serialize(@Model.Locations));
for (var i = 0; i < pins.length; i++) {
var myLatLng = {
lat: pins[i].lat,
lng: pins[i].long
};
addMarker(myLatLng, map, pins[i]);
}
}
function addMarkerAsync(location, map) {
new google.maps.Marker({
position: location,
title: 'Home Center',
});
marker.setMap(map);
}
function addMarker(location, map, pin) {
var marker = new google.maps.Marker({
position: location,
title: '...something dyanmic...',
});
var infowindow = new google.maps.InfoWindow({
content: ''
});
function AsyncDisplayString() {
$.ajax({
type: 'GET',
url: '/Home/GetLocationModalInfo',
dataType: "HTML",
contentType: 'application/json',
traditional: true,
data: pin,
success: function (result) {
debugger;
infowindow.setContent('<div style="background-color:#000000;">' + result + '</div>');
infowindow.open(map, marker);
},
error: function (arg) {
alert('Error');
}
});
}
google.maps.event.addListener(marker, 'click', function () {
AsyncDisplayString(map, marker)
});
marker.setMap(map);
}
</script>
And then this Controller Action that uses GoogleLocationService to get coordinates by address:
This is a proof-of-concept app illustrating what you can do with a little JavaScript, a web app and the Google Maps API
As you can see the Google Maps API provides a lot of opportunity for your application- don't underestimate the power of location-based data. With the tools at our disposal today the functionality of applications is being limited less by available algorithms/frameworks/tools- but rather, our imagination.
I strongly suggest you look into the ways you can integrate geographic/mapped data with Google Maps API; very powerful API
