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." -Designing for Extensibility in .NET (Cwalina, Abrams, Barton)

References:

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

https://www.informit.com/articles/article.aspx?p=3089304

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

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());  

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

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

Custom SSRS Authentication Extended

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.

GitHub (my sonrai LLC is my contractor/consultant LLC): https://github.com/sonrai-LLC/ExtRSAuth

Locations - Google Maps API, ASP.NET Core and SQL Server

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:

 [HttpPost]  
     public IActionResult AddLocation(LocationModel location)  
     {  
       string address = location.StreetAddress1.Replace(" ", "+") + "," + location.City.Replace(" ", "+") + "," + location.State.Replace(" ", "+");  
       MapPoint coords = _locationService.GetLatLongFromAddress(address);  
       location.Lat = (decimal)coords.Latitude;  
       location.Long = (decimal)coords.Longitude;  
       using (var db = new SqlConnection(_configuration.GetConnectionString("DefaultConnection")))  
       {  
         db.Open();  
         string sql = @"INSERT INTO [Locations].[dbo].[Locations] ([Name], [Contact], [Email], [Website], [Phone], [StreetAddress1], [StreetAddress2], [City]"  
           + ",[State], [Zip], [LocationContact], [PrimaryContact], [Notes], [Type], [Lat], [Long], [Petitions], [Flyers], [Posters], [LastPickUpDateTime], [LastOutOfStockDateTime], LastDropoffDateTime"  
           + ",[AllTimeOutofStock],[Unsupportive],[VolunteerInterest])"  
           + " VALUES ('" + location.Name + "','" + location.Contact + "','" + location.Email + "','" + location.Website + "','" + location.Phone + "','" + location.StreetAddress1 + "','" + location.StreetAddress1 + "','" + location.City + "'"  
           + ",'" + location.State + "','" + location.Zip + "', -1, -1,'" + location.Notes + "', 1, " + location.Lat + "," + location.Long + "," + location.Petitions + "," + location.Flyers + "," + location.Posters + ",'" + location.LastPickUpDateTime + "','" + location.LastOutOfStockDateTime + "','" + location.LastDropoffDateTime + "', 0, 0, 1) " + ";";  
         db.Execute(sql);  
       }  
       var model = GetDefaultMapView();  
       model.KeyString = _configuration["MapsAPIKey"].ToString();  
       return View("Map", model);  
     }  

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

GitHub: https://github.com/cfitzg/LocationTracks

SSRS REST API v2

Here is a response from the SSSR REST API in action.. (you can access a lot more SSRS item properties and customize at will once you know the API)

The SSRS API v2 has far more functionality than v1, but they essentially work the same. You must be authenticated to the SSRS report server you are targeting (localhost in this case) to make web GET/POST requests to the API.

Once auth'd you can push and pull any useful SSRS data pretty easily to make SSRS do some pretty cool things it can't do out of the box..

This is the SSRS API as accessed through a web browser; simply give your .NET app an HttpClient and you can make use of all these responses; it's just JSON...

You can get a collection of SSRS catalog items as in the example above (folders, reports, KPIs) by just specifying the action name, or you can select an individual item by putting the item GUID in parenthesis in the API request URL:

You can access individual items in the API via GUID in parens after the API action name.

Common Useful SSRS API v2 Actions:

• Reports

• Datasets

• Data Sources

• Folders

• Schedules

• Subscriptions

• Comments

• KPIs

• CatalogItems (everything)

Example of a .NET Standard library with an HttpService abstacting the SSRS API calls:

 namespace ExtRS  
 {  
   public class SSRSHttpService  
   {  
     const string ssrsApiURI = "https://localhost/reports/api/v2.0";  
     HttpClient client = new HttpClient(new HttpClientHandler() { UseDefaultCredentials = true });  
         public async Task<GenericItem> GetReportAsync(Guid id)  
     {  
       client.BaseAddress = new Uri(ssrsApiURI + string.Format("/reports({0})", id));  
       var response = await client.GetAsync(client.BaseAddress);  
       var odata = response.Content.ReadAsStringAsync().Result;  
       return JsonConvert.DeserializeObject<GenericItem>(odata);  
     }  
   }  
 }  

This is verbose to better break down the steps of what is happening on the ExtRS service end

A very basic class designed to demonstrate using SSRS API Response to create a .NET object:

 using Newtonsoft.Json;  
 using System.Collections.Generic;  
 namespace ExtRS  
 {  
   public class GenericItem  
   {  
     [JsonProperty("@odata.context")]  
     public string ODataContext { get; set; }  
     [JsonProperty("Id")]  
     public string Id { get; set; }  
     [JsonProperty("Name")]  
     public string Name { get; set; }  
     [JsonProperty("Path")]  
     public string Path { get; set; }  
   }  
 }  

The power of the SSRS API is limited primarily your imagination- lots of customization can be made

And finally, called from a Controller Action in an MVC app:

 using System;  
 using System.Web.Mvc;  
 using System.Threading.Tasks;  
 using ExtRS;  
 namespace Daylite.Controllers  
 {  
   public class ReportsController : Controller  
   {  
     public SSRSHttpService service = new SSRSHttpService();  
     public async Task<ViewResult> GetReportsAsync()  
     {  
       return View("Index", await service.GetReportsAsync());  
     }  
     public async Task<ViewResult> GetFoldersAsync()  
     {  
       APIGenericItemsResponse result = await service.GetFoldersAsync();  
       return View("Index", result);  
     }  
     public async Task<ViewResult> GetReportAsync(Guid id)  
     {  
       GenericItem result = await service.GetReportAsync(id);  
       return View("Index", result);  
     }  
   }  
 }  

Reference: https://github.com/Microsoft/Reporting-Services/tree/master/APISamples

SSRS Extension Methods - The 3 Levels

When developing SSRS reports, if you are unable to achieve what you want through SQL and RDL alone, there are essentially 3 ways you can add custom functions/functionality to SSRS.

1). Inline SSRS "Expressions". (VBScript code)

2). Report.Code references (VB code)

3). Custom Assembly references (C# or VB .NET .dll)

The following is a brief example of each.

1). Inline SSRS Expressions
In SSRS, virtually every report property can be dynamic. The way to set the dynamic behavior of any report item is through SSRS Expressions which are simply VBScript code blocks that evaluate some logic against the report data and return the property value at runtime.

For instance, here we are setting the Font >> FontWeight property of the Order Date column for an Orders report (AdventureWorks sample data). If the Date is more than 1 year in the past, this inline expression sets the FontWeight of the OrderDate field to bold:

Method 1 Code

Method 1 Result

You can use IIF(), Choose(), Switch() or any valid VBScript syntax. The report field values are referenced as Fields!OrderDate.Value. As in the example above, you will oftentimes need to cast the report dataset data to the data type which you are evaluating (a cast to CDate in this example as we are evaluating a Date).

2). Report Code references (VB code)
This method is similar to Method #1, but it allows you to define a VB Code space where you can define functions in a central place and reference them any number of times throughout your report.

For this example, let's say we need to have a report function that takes a string as a parameter and then evaluates the length of that string. If the string value is over 10 characters long, it sets the Color to Red, if not, the color is the default Black. Your Report Code methods are defined in: Report >> Report Properties >> Code:

Method 2 Code

Method 2 Reference

Method 2 Result

3). Custom Assembly references (C# or VB .NET .dll)
This is the most comprehensive of the 3 methods and allows you to do anything that you could normally do with a .NET assembly.

For illustrative purposes, let's say we have a need to interface with a backend system and get a URI which will fill a report image item with the appropriate image from an external server that is not connected to the Report Server. You can implement the necessary .NET logic and API calls, etc. in your custom assembly and then reference that .dll in your report:

 namespace SSRSCustomAssembly  
 {  
   public class ErpReferenceLib  
   {  
     public static string GetImageForCustomerAccount(int customerID)  
     {  
       //this example is static; in real-world implementation the customerID might fetch value from db or API  
       bool isActiveAndValid = CheckCustomerIDIsValidAndActive(customerID);  
       if (isActiveAndValid)  
       {  
         return "https://abccorp.imagesrv?customerID=" + customerID + ".svg";  
       }  
       else  
       {  
         return "https://abccorp.imagesrv?inactive.svg";  
       }  
     }  
     public static bool CheckCustomerIDIsValidAndActive(int customerID)  
     {  
       return customerID >= 100 ? true : false;  
     }  
   }  
 }  

Method 3 Code

Method 3 Report Reference


Method 3 Report Item Reference- NOTE: You cannot use namespaces separated with ".", ie: MyNamespace.Subnamespace

Method 3 Visual Studio Report Design View

Method 3 Result in VS2017 Preview Mode

Method 3 Result on local Report Server

For more information (esp. how to do the necessary custom assembly security permission config changes, for creating object instances vs. static methods, etc.), see the relevant references below.

Note(!): your custom assembly must have permission to run within Visual Studio in order to Preview a report that calls a custom assembly method and it must also have permission within Report Server. You can grant it FullTrust or whatever CAS group you deem is appropriate. The paths to deploy the .dll are as follows (may vary by SQL Server and Visual Studio version- this is for SQL 2017 and VS2017):

Visual Studio Report Designing/Preview assemblies*: C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\CommonExtensions\Microsoft\SSRS

Report Server assemblies*: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin

GitHub for SSRS Project: https://github.com/Radagast27/CustomSSRS

GitHub for SSRS Custom Assembly: https://github.com/Radagast27/CustomAssembly


*https://www.mssqltips.com/sqlservertip/3224/sql-server-reporting-services-custom-code-assemblies/

SSRS URL Access Query String Parameters

If you want to hide the parameter UI, skip to a certain page or, as in my case, need to force the "target=_blank  |_self | _parent | _top" attribute of Action URLs in your report, simply add the appropriate SSRS Qs param to the URL of your target report.

http://myrshost/reportserver?/Sales&rc:FindString=Mountain-400

http://myrshost/ReportServer?/myreport&rs:Format=PDF

http://myrshost/ReportServer?/myreport&rs:LinkTarget=_top

In my case, I was running into a scenario where my report link targets were being suppressed in FireFox and IE due to server restrictions on the content surrounding my report view. Simply applying rs:LinkTarget=_top to the report URL query string forced the necessary target=_top to override the undesired behavior. Because this took me longer than I'd think it should take to find the resolution, I have shared this tidbit in the hopes it helps save time for another developer at some point.


Reference:

https://www.w3schools.com/tags/att_a_target.asp

https://docs.microsoft.com/en-us/sql/reporting-services/url-access-parameter-reference

Customize SSRS Stylesheet

This is an easy thing to do, especially if you are well-versed in CSS/DOM designing.

1). Go to this location and open up HtmlViewer.css in your favorite text editor:

2). Edit/override styles however you see fit. You can change almost all of the styles on the SSRS toolbar:

(before)

(change made)

(after)

3). If you want to point to different stylesheets for different report executions (ie. if you need security and want to hide the Save or Print button from some users), you can easily do that by adding the filename of your own custom stylesheet to the rc:Stylesheet parameter in the SSRS Request URI a la:

Other things like client-side scripting can be done to extend the Report Viewer UI further, but doing that sort of "functionality" integration is not well documented. If you want complete control over your Reporting Services app UI, take a look at the Report Server web service: https://docs.microsoft.com/en-us/sql/reporting-services/report-server-web-service/report-server-web-service

Happy Reporting!