extRS for useful common logic, reference data and extending SSRS

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

     [TestMethod]  
     public async Task CreateGetDeleteSubscriptionSucceeds()  
     {  
       string json = @"{  
       ""@odata.context"": ""https://localhost/reports/api/v2.0/$metadata#Subscriptions/$entity"",  
         ""Owner"": """ + Resources.User + @""",  
         ""IsDataDriven"": false,  
         ""Description"": ""string..."",  
         ""Report"": ""/Reports/USPolls"",  
         ""IsActive"": true,  
         ""EventType"": ""TimedSubscription"",  
         ""ScheduleDescription"": ""string..."",  
         ""LastRunTime"": ""2023-04-13T15:51:04Z"",  
         ""LastStatus"": ""string..."",  
         ""DeliveryExtension"": ""Report Server Email"",  
         ""LocalizedDeliveryExtensionName"": ""Email"",  
         ""ModifiedBy"": """ + Resources.User + @""",  
         ""ModifiedDate"": ""2023-04-13T15:51:04Z"",  
         ""Schedule"": {  
           ""ScheduleID"": null,  
           ""Definition"": {  
             ""StartDateTime"": ""2021-01-01T02:00:00-07:00"",  
             ""EndDate"": ""0001-01-01T00:00:00Z"",  
             ""EndDateSpecified"": false,  
             ""Recurrence"": {  
               ""MinuteRecurrence"": null,  
               ""DailyRecurrence"": null,  
               ""WeeklyRecurrence"": null,  
               ""MonthlyRecurrence"": null,  
               ""MonthlyDOWRecurrence"": null  
             }  
           }  
         },  
         ""DataQuery"": null,  
         ""ExtensionSettings"": {  
           ""Extension"": ""DeliveryExtension"",  
           ""ParameterValues"": [  
             {  
               ""Name"": ""TO"",  
               ""Value"": ""colin@sonrai.io"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""IncludeReport"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""Subject"",  
               ""Value"": ""true"",  
               ""IsValueFieldReference"": false  
             },  
             {  
               ""Name"": ""RenderFormat"",  
               ""Value"": ""PDF"",  
               ""IsValueFieldReference"": false  
             }  
           ]  
         },  
         ""ParameterValues"": []  
       }";  
       Subscription subscription = await ssrs.SaveSubscription(JsonConvert.DeserializeObject<Subscription>(json)!);  
       Assert.IsTrue(subscription.DeliveryExtension != null);  
       var getResponse = await ssrs.GetSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(getResponse.Id != null);  
       var delResp = await ssrs.DeleteSubscription(subscription.Id.ToString()!);  
       Assert.IsTrue(delResp);  
     }  

Getting FedEx/USPS/UPS Shipping Rates

"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." -https://www.informit.com/articles/article.aspx?p=3089304

Reference: https://github.com/sonrai-LLC/extRS

Interfaces.

When it comes to the ability to interact with the inputs and outputs of software, everything is an interface. For those who aren't aware, the Windows OS was originally named "Microsoft Interface Manager". There is interesting info in the Wikipedia entry on the origins of Windows.

The OS that started it all for Microsoft.

"In computing, an interface is a shared boundary across which two or more separate components of a computer system exchange information. The exchange can be between software, computer hardware, peripheral devices, humans, and combinations of these."

 

Without getting into the omnipresence of interfaces too much (the keyboard is one, the mouse and cursor are as well, the monitor is an interface, all of the windows on the screen are interfaces, every single OS event is taking place between two endpoints of an interface, programming languages use interfaces as "contracts" that can be adhered to, to allow for different implementations of the same kinds of data structures and methods, our own 👀 are interfaces to the world around us and on and on- and each of these relies on their own software!...), let's take a simple example.

Let's say we have some really neat library of unique functionality we want to share with as many developers as possible called "extRS". We have so many options for how to get this into the hands of other developers. But it requires assembling the working software into different languages/compilations to fulfill the needs of the different interfaces.

This is what Windows 1.01 looked like; 1985 my, my...

• For an API (the interface for web services), we will want to present our software in the form of an API running on a web server. With a live ASP.NET Web API (esp. one that can respond to XML and JSON) you can allow clients (interfaces) to get live data from an implementation of your library's functionality via something like a simple POST to /ConvertXmlToJson with a body of:

 { <car><passenger></passenger></car> }  

...which then returns the appropriate XML-translated-to-JSON:

 {  
   "car": 
     {  
       "passenger": null  
     }    
 }  

This is an example of an API request and response (FedEx API)

• There is also ASP.NET CoreWCF for interfacing with legacy XML SOAP-based web services:

If you want to interface with a legacy SOAP service in ASP.NET Core- this is what you can use

• For getting our logic into the hands of JavaScript and NodeJS developers we can create and host an NPM package:

htmx and hyperform.js for validation are the neatest 2 open source things I've discovered in the past 2 years

• For Python developers we can publish a python libraries to PyPI which makes it download-able via pip:

This shows the installation for the Python package pandas through the Windows CMD prompt

• And we could even create a Ruby gem version, and packages for other languages to boot.... and on and on....

The problem is that, the more packages and different types of assembled or executable libraries you create, the more code bases you have to continuously test and maintain. But if your logic is simple enough, it makes sense extend the reach of your software as much as possible. Every interface that could use it, should be able to get it, and use it.

We have not even discussed console interface (CLI) which is required if you want your logic to be utilized in scripting and tooling chains that perform logic by piping the results of processes into subsequent processes all in CLI scripts like bash or PowerShell.

• Custom CLI .exe - We can expose our extRS functionality through a command line interface. We compile a CLI program, ExtRS.CLI.exe that accepts user input such as ConvertXmlToJson ""<mouse name="Micky"><mouse>"" and performs prompts to present information to the CLI interface. I hate to disappoint, but that hasn't yet been implemented for the extRS project .sln 🤷🏽‍♂️

A simple .NET CLI program using extRS; this can easily be modified to implement virtually all of the extRS functionality

• PowerShell Module - With a PowerShell module, DevOps and CI/CD devs can easily install the PowerShell modules through the official Microsoft PowerShell Gallery Here is the ReportingServicesTools PowerShell module:

Here is the ReportingServicesTools PS module; as you can see (errors)- I've gotta update some things in extRSAuth to get it to work with this module(!)

• Nuget Package (compiled, portable ".dll" binaries) - allow clients to easily get your library's code into any .NET project. With this, any .NET project can reference extRS via:

This is my SSRS extension library, "extRS" on Nuget.org

• WinForms - though a bit antiquated, there are millions of SMB and huge corporate software applications running proprietary .NET Framework code through Windows forms interfaces. It is like COBOL and FORTRAN- some things just work, and so they persist. For things like point-of-sale systems, sometimes the best solution- is something proven and established that won't surprise anyone (a WinForms POS app). An extRS WinForms app looks like this:

A mock of what an extRS interface implementation in WinForms might look like

• MAUI - my experience is limited here, but from what I have experienced, MAUI is a faster, more capable version of Xamarin (with Xamarin and Mono under the hood). The great thing about MAUI is that, with one project, you can target all manner of mobile form factors (phones, tablets, watches- iOS and Android). An interface for my "tickertapes" Android app on MAUI for an Android phone looks like this: 

An example of one of my apps, "Tickertapes", running in an Android emulator using MAUI

As you can see we have so many ways to expose our unique (extRS, tickertapes) functionality. The only limitation on getting your functional code into operating programs is- the right interface implementation!

PS: A note on physical client interfaces as well... While we have discussed the different software interfaces (API, CLI, .dll, NPM, .psm, etc.), from a UI presentation and experience perspective, we have to consider the different types of physical interfaces that our code will be represented on- particularly the screen dimensions and how that will affect the inclusion vs. exclusion and location of certain content.

Never give short shrift to UI compatibility across devices- it is dang hard to present a "consistent" experience in myriad "different" ways. ('kinda competing, incompatible angles).

Hardware Form Factors

• Mobile Phones
• Desktop monitors
• Laptop monitors
• Tablets
• TV monitors
• LED scoreboards
• Digital scrolling Marquee signs
• Digital Billboards
• "Smart" Watches
• VR Headsets
• Earbuds
• "Smart" Eye Glasses
• IoT devices with little or no display at all (in BAS equipment for example, typically these are used to start/stop or accelerate/slow down, based on some kind of PLC configuration and to collect and report useful data metrics about the device)

Reference: https://learn.microsoft.com/en-us/powershell/gallery/how-to/publishing-packages/publishing-a-package?view=powershellget-3.x#powershell-gallery-account-and-api-key

*"On November 20, 1985 (38 years ago), the first retail release, Windows 1.01, was released in the United States at a cost of US$99 (equivalent to about $280.00 in 2023)." -Wikipedia

You need to eat your own dog food

I recently realized how useless my custom .NET method, "Sonrai.ExtRS.ReferenceDataService.GetGoogleNews(string searchTerm)", really was.

I had referenced Sonrai.ExtRS via Nuget and began trying to use it to improve the display in scrolling news links, in another application I maintain, tickertapes.net.   

It was useless! I mean a complete waste of code. It returned potentially useful data related to the searchTerm parameter- data from the GoogleNews API. But that is about it. It literally returned the entire XML response. 🤦‍♂️

This was the raw oil, but not the refined gasoline a consumer of a Nuget package expects...

I was parsing all this XML in the tickertapes.net app (why I do not know or care to remember), and so all the work necessary to wrangle the XML response to produce the needed news link collection was on the consumer of the Sonrai.ExtRS Nuget package- not good!

What the Nuget library should do, is all the work. What it should return, to be actually useful, is something structured like a collection of strings, each one representing a single news article, with the news headline being the text for the news article link.

And so I moved back to Sonrai.ExtRS to correct this unfortunate oversight.  

  public static async Task<List<string>> GetGoogleNewsWithLinks(string search)  
  {  
    HttpClient client = new HttpClient();  
    var content = await client.GetStringAsync(string.Format("https://news.google.com/rss/search?q={0}", search));  
    var parser = new HtmlParser();  
    var document = parser.ParseDocument(content);  
    var newsItems = document.All.Where(m => m.LocalName == "title").ToList();  
    var linkItems = document.All.Where(x => x.LocalName == "link").ToList();  
    var newsLinkItems = new List<string>();  
    for (int i = 0; i < newsItems.Count; i++)  
    {  
      newsLinkItems.Add("<a href='" + linkItems[i].NextSibling!.NodeValue + "' target='_blank'>" + newsItems[i].InnerHtml + "</a>");  
    }  
    return newsLinkItems;  
  }  

And this provides the Nuget client with something it can actually use, "turnkey"/out-of-the-box.

A list of HTML links which we can actually use; this is more like it.

The moral of the story is one that is as old as business and manufacturing: you must eat your own dog food. If there are problems or areas that need attention it is best that you find this information out before your product is released into the wild and a customer discovers the bug (or in this case, the uselessness), thus sullying your reputation as a business and software provider.

Test, test, test- always unit and/or integration test every user interaction and data movement for every story/path imaginable or support-able.

But there is nothing that replaces simply using your own product the way it is used by real users. And really using it for the purpose it was made. What you discover may help you shore up previously unknown problems and/or inspire you to make something useful that you would never otherwise think of, unless you were thinking from a user's perspective.

Reference: https://www.nuget.org/packages/Sonrai.ExtRS

PS: Imagine if James Newton-King never used NewtonSoft.Json for his own de/serializations? Or if Stack Overflow never used Dapper for the SO app/site? You need to use your creations right at the ground level (as a user/client) to verify that the functionality you designed in the abstract exactly matches how things will play out in concrete reality. Thoughts.. 💭

How to use .NET User Secrets in MSTest classes

When developing unit and integration tests, we don't necessarily want to share the secret keys and values we use as credentials for APIs, databases, etc.

So similarly to how we implement User Secret functionality in Program.cs, we can implement User Secrets and set test class variables that use the secrets via the MSTest classes' constructor (ReferenceDataTests() below):

  public static string upsId = "";  
  public static string upsSecret = "";  
  private IConfiguration _configuration { get; }  
  
  public ReferenceDataTests()  
  {  
    // set your API ids and secrets in UserSecrets (right-click project: "Manage User Secrets")  
    var builder = new ConfigurationBuilder()  
      .AddUserSecrets<ReferenceDataTests>();  
    _configuration = builder.Build(); 
    
    var secretVals = _configuration.GetChildren().ToList();  
    upsId = secretVals.Where(x => x.Key == "upsId").First().Value!;  
    upsSecret = secretVals.Where(x => x.Key == "upsSecret").First().Value!;   
  }  

Reference: https://stackoverflow.com/questions/41941503/how-to-use-user-secrets-in-a-dotnet-core-test-project

ASCII art fun with .NET

My friend Benn with a nice largemouth bass

And here we have a simple function. Many thanks to its originator, Thinathayalan Ganesan.

You can find this in the Sonrai.ExtRS Nuget project under Sonrai.ExtRS.FormattingService.ConvertToAscii(Bitmap image) and see how it is used in Sonrai.ExtRS.FormattingTests.ConvertToAsciiSucceeds.

I talked a bit about how ASCII art works in a post on a similar Python script.

  // credit (Thinathayalan Ganesan): https://www.c-sharpcorner.com/article/generating-ascii-art-from-an-image-using-C-Sharp  
  public static string ConvertToAscii(Bitmap image)  
  {  
    string[] _AsciiChars = { "#", "#", "@", "%", "=", "+", "*", ":", "-", ".", " " };  
    Boolean toggle = false;  
    StringBuilder sb = new StringBuilder();  
    for (int h = 0; h < image.Height; h++)  
    {  
      for (int w = 0; w < image.Width; w++)  
      {  
        Color pixelColor = image.GetPixel(w, h);  
        //Average out the RGB components to find the Gray Color  
        int red = (pixelColor.R + pixelColor.G + pixelColor.B) / 3;  
        int green = (pixelColor.R + pixelColor.G + pixelColor.B) / 3;  
        int blue = (pixelColor.R + pixelColor.G + pixelColor.B) / 3;  
        Color grayColor = Color.FromArgb(red, green, blue);  
        //Use the toggle flag to minimize height-wise stretch  
        if (!toggle)  
        {  
          int index = (grayColor.R * 10) / 255;  
          sb.Append(_AsciiChars[index]);  
        }  
      }  
      if (!toggle)  
      {  
        sb.Append("\r\n");  
        toggle = true;  
      }  
      else  
      {  
        toggle = false;  
      }  
    }  
    return sb.ToString();  
  }  

The key is the assignment, pixel-by-pixel, of values to the reb, blue and green variables (and then "grayColor" variable) under the comment "Average out the RGB components to find the Gray Color". By getting the average of all colors in the pixel you can get the grayScale RGB color from Color.FromArgb(R, G, B). This grayscale RGB color is then used to select the appropriate ASCII character to represent the shade of gray in each pixel of the image.

• A darker pixel of an image will use an ASCII character like a "." or "-" or ":".

• A lighter pixel of an image will use an ASCII character like a "#" or "@" or "%".

In this way we can easily convert an image from its pixel-based source representation to an ASCII character representation. Essentially, a computer image is just a mosaic, or a composite of parts (pixels usually, sometimes ASCII characters- for art and fun!). 😃

Reference: https://www.c-sharpcorner.com/article/generating-ascii-art-from-an-image-using-C-Sharp

extRS Portal: a modern SSRS client

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 particuliar deployment of the extRS.Portal web client is connected to a report server with custom authentication (extRSAuth) which gets passed 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 the 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 source code can be found here: https://github.com/sonrai-LLC/extRS

tickertapes

Users can search for any word or phrase and opt for news of common financial market indexes

Originally implemented as "Twickertapes" and utilizing the original Twitter API (v2.0), this app is merely a demonstration of what can be done with a little text input, and API (the Google News API) and scrolling text and ASCII art.

You can find it on the web here: https://tickertapes.net

 

An SSRS IFrame/CORS infinite redirect loop error and a quick and easy solution

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" and enableCrossAppRedirects="true" to the authentication <forms> tag in the report server's web.config.

 <authentication mode="Forms">  
   <forms loginUrl="logon.aspx" name="sqlAuthCookie" cookieSameSite="None" timeout="60" path="/" enableCrossAppRedirects="true" requireSSL="true">  
   </forms>   
 </authentication>  

You may also need to enable CORS in your client app. In ASP.NET Core 8, this can be achieved through the following in your application startup code:

 app.UseCors(builder => builder  
 .WithOrigins("https://localhost", "https://[domain]")  
 .AllowAnyMethod()  
 .AllowAnyHeader());  

XML vs. JSON for API requests and responses

XML is a document-centric data format and data processing language and stands for, "Extensible Markup Language".

JSON is a lighter-weight messaging-centric data format and stands for, "JavaScript Object Notation".

A question that often comes up in discussing APIs is whether an endpoint should accept and return JSON (increasingly the standard) instead of XML (was the old standard with XML-based SOAP services). If I have just 1 opinion to give, I would say that it is important for an API response model to be format agnostic (at least in regards to JSON vs. XML). Why not have the API accept and return both types and give clients the choice of either JSON or XML?

If your web service is so complex and has so much XML-specific dependencies, you may want to look into simplifying your model. None of the heavy lifting (of database communication, of data configuration and security information, data processing, etc.) should ever be done with dependencies from our sent and received XML and JSON GETs and POSTs.

That should be done on the server side, as much as is possible.

Here is an illustration from xml.com which describes the functionality equivalents in each format

In reality, if we model our program entities with easily understandable properties and no embedded config in the XML (this was the norm for SOAP), we will have no problem serializing our model objects into XML or JSON- losing no information in the process.

"XML is a data format, AND it is a language also. It has many powerful features that make it much more than a simple data format for data interchange. e.g., XPath, attributes and namespaces, XML schema and XSLT, etc. All these features have been the main reasons behind XML popularity.

JSON’s purpose is solely structured data interchange. It serves this purpose by directly representing objects, arrays, numbers, strings, and booleans. When meta-data and document markup is not a requirement, always use JSON." 

 -Lokesh Gupta

JSON vs. XML: A simple example

 {   
  "json": {   
   "myAppSettings": {   
   "appSettingDatabaseUri": "https://mysecuredatabaseserver.net"   
   }   
  }   
 }   

 <xml>  
 <myAppSettings>  
 <appSettingDatabaseUri>https://mysecuredatabaseserver.net</appSettingDatabaseUri>  
 </myAppSettings>  
 </xml>

It is a matter of preference for developers: For some, XML reads like a book and XML element structure and hierarchy makes for more easily understood data messages. For others, XML is overload, JSON is far more lightweight (ie. faster), and JSON is more readable (esp. for anyone knee-deep in NodeJS development where JSON is the default format for everything). JSON is also inherently parse-able with JavaScript. 

With XML it is not so. And XML's additional concerns (document media type mixing, security, channel bindings (remember WCF? 😅)), looping and various other other namespace and xsl/xslt configurations and transformations)- make it seem far more unwieldly than it actually is.

Aim for offering your API clients the option of JSON or XML for response type via "Accept: application/xml" or "Accept: application/json" header requests. Happy Parsing!

References: 

https://restfulapi.net/json-vs-xml

https://www.guru99.com/json-vs-xml-difference.html

History of XML

• XML was also derived from SGML.
• Version 1.0 of XML was released in February 1998.
• Jan 2001:IETF Proposed Standard: XML Media Types
• XML is the Extensible Markup Language.
• 1970: Charles Goldfarb, Ed Mosher, and Ray Lorie invented GML
• The development of XML started in the year 1996 at Sun Microsystem

History of JSON

• Douglas Crockford specified the JSON format in the early 2000s.
• The official website was launched in 2002.
• In December 2005, Yahoo! starts offering some of its web services in JSON.
• JSON became an ECMA international standard in 2013.
• The most updated JSON format standard was published in 2017.

Power BI and SSRS - A complimentary symbiosis

 

"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.

Reference: https://learn.microsoft.com/en-us/power-bi/guidance/migrate-ssrs-reports-to-power-bi

Dichotomies

Apropos of nothing I thought about some interesting dichotomies and wanted to share my perspective. It is interesting how often there are effectively 2 sides to a coin.

Religious Faith/Religious Dogma - Some folks live the "spirit" of the Word and resist structure vs. others, who follow strict adherence to "the letter" of the Word.

Subjective/Objective - You have here opinion and feeling (the supernatural) vs. facts and laws of nature.

Romantic/Classical - similar to the Subjective/Objective dichotomy (see also, "Zen and the Art of Motorcycle Maintenance").

Liberal/Conservative - The ideology of inclusion and change vs. the ideology of exclusion and stasis.

Conglomerate/Individual Co. - Whereas many corporations sought to obtain economies of scale through M&A in 80s, 90s and 00s, General Electric proved- (sold GE Capital, spun off GE Healthcare, GE Aviation and GE Power into 3 individual new companies)- that the conglomerate model doesn't always stand the test of time.

Thin client/Fat client - It makes no sense to be all one or all the other (an overburdened SPA app or an inflexible server-side only app). But we keep moving between one (thin client terminals in the 70s and 80s) and the other (fat client Home PCs in the 90s and 00s). And we've moved back to thin client again with Azure, AWS and GPC and the omnipresence of SaaS. But at the same time, fat-client SPA apps are as popular as ever... I guess we have fat client UIs and thin client APIs.

Imperative/Declarative code - Instructions that read like a book vs. instructions that read like a mathematical proof.

Monolithic app/Microservices - a very heavy Swiss-Army knife vs. a bunch of lightweight kitchen knives.

Software/Hardware - Recipes vs. the raw food itself.

Socialism/Capitalism - The idea that everyone is the same and should be reduced to (or propped up by) exactly such vs. the idea that everyone is incompatibly unique and should be uniquely catered to and provided maximum personal freedoms that can- like assault weapons and ammunition- come at the expense of the greater community.

Urban/Rural - Those from the densely populated cities and suburbs vs. those from the sparsely populated country towns.

Introvert/Extrovert - He or she who expends a great deal of energy socializing and finds solace in silence vs. he or she who is energized by interpersonal social connection and is not comfortable alone for extended periods of time.

Centralization/Decentralization- Putting the heart of a system at the center with dependent, often necessarily-generic/homogenized nodes vs. putting the heart on the nodes themselves, at the expense of (losing) ease of simultaneous node (state) synchronization and a "single source of truth/golden records".

Charts Suggestions - "A Chart Chooser" (edited)

Just because you can do things with "non-data ink"* does not mean you should do things with "non data ink". Below is a useful guide to charts for presenting different perspectives of data. I have crossed out the ones that are unanimously decried in the data visualization community for having confusing elements that do not effectively communicate the meaning of data.

Bar charts. Line charts. Dot or scatter plots. And bullet charts for really communicating a lot of information in a small space. That's really all you need.

 

The "Thought-Starter" above, edited to cross out (in red) all useless and confusing visuals to AVOID

Bullet charts are highly effective at displaying sub-ranges within a spectrum; the sole max line communicates threshold or "target" values

Focus on ensuring that every piece of ink in your chart is conveying some kind of useful information. If not, delete it. Blank space is better than distracting ink.

If you need an example of "distracting, non-data ink" then look no further than the following almost headache-inducing example:

I imagine the author of this chart was more interested in the art than the meaning of the data; this is a really bad data visualization

Another simple yet very powerful data visualization technique is to show the same type of chart repeated for contiguous time intervals or for different groups at the same point in time. An example is the following small multiples chart on alcohol consumption in different countries:

Small multiples charts really highlight the outliers (S. Korea?!!)

For more on the use of small multiples for effective data visualizations, I show more examples here: kpitsimpl: Small Multiples (are awesome) a while back. KEEP IT SIMPL.

*https://infovis-wiki.net/wiki/Data-Ink_Ratio#:~:text=Non%2DData%2DInk%20is%20accordingly,non%2DData%2DInk

ExtRSAuth for Custom SSRS Authentication (works w/newest SSRS version 16.0.8)

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.

Clone it, customize it further (or not) and get started today: https://github.com/sonrai-LLC/ExtRSAuth