[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);
}
extRS for useful common logic, reference data and extending SSRS
extRS Portal: a modern SSRS client
An SSRS IFrame/CORS infinite redirect loop error and a quick and easy solution
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>
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.
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.
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.
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)
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?
Custom SSRS Authentication Extended
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
Locations - Google Maps API, ASP.NET Core and SQL Server
<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>
[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);
}
SSRS REST API v2
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..
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:
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);
}
}
}
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; }
}
}
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
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:
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:
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 Report Item Reference- NOTE: You cannot use namespaces separated with ".", ie: MyNamespace.Subnamespace
Method 3 Visual Studio Report Design View
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
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:
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!