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/

T-SQL STUFF, STRING_SPLIT

In cases where you must extract a series of values from a delimited string, you can utilize SQL Server's built-in string_split() function. You need only specify the delimited string and the delimiting character (commas in this case ",") as the arguments.

When a requirement calls for you to roll up data into a single row, you can use STUFF() (with or without combined with FOR XML, depending on your data). Just specify an inner query as the argument for STUFF().

 --Get ids from a csv of ids  
 SELECT * FROM string_split('100,101,201,301,411,414', ',')  
 

--Push a series of values into one row  
 SELECT playerID,  
 STUFF((select '; ' + teamID  
      from Managers  
      where teamID like('%P%')  
            group by teamID  
      for xml path('')), 1, 1, '') allTeams  
 FROM Managers  

Result of these functions

T-SQL and .NET for XSLT

Steps to perform transformation from SQL to XML to XSLT Output data (csv, htm, tab-delim, fixed-width, etc.):

1). Get your SQL data (assuming you are using SQL Server) into XML format via:

 SELECT [OrderId], [PersonId], [OrderDateTime] FROM [Order] FOR XML AUTO, ELEMENTS, ROOT ('Orders');  

XML Result of "FOR XML AUTO, ELEMENTS, ROOT"..

2). Load XSL transform file (OrderXform.xsl) into a new XslCompiledTransform object (transform):

       XslCompiledTransform transform = new XslCompiledTransform();  
       transform.Load("C:\\Xform\\OrderXform.xsl");  

XSL file used to transform the XML to the desired file output (EDI 834 standard, etc)

3). Using a StringWriter (writer), StringReader (inputReader) and XmlReader (inputXmlReader), read the inputReader XML string into inputXmlReader and apply the XSL transformation to the transform object via: transform.Transform(inputXmlReader, args, writer); //(or another override of Transform())

  using (StringWriter writer = new StringWriter())   
     {   
      // Make an XML reader (inputReader) out of the string (input)  
      XmlReader inputXmlReader;   
      using (var inputReader = new StringReader(input))   
      {   
       inputXmlReader = XmlReader.Create(inputReader);   
       // Apply the transformation to XmlReader (inputXmlReader) and write it to StringWriter (writer)   
       transform.Transform(inputXmlReader, null, writer);   
      }      
      // Retrieve the output as string from writer object  
      return writer.GetStringBuilder().ToString();   
     }   

4). Return the result and write the bits (to memory as Label Text for a demo like this, to file, to domain network location, to non-domain customer ftp, etc.).

The result normally gets written to remote SFTP location or posted to API endpoint for processing

Nothing incredibly complex about this, but it seems to frustrate a lot of people in .NET land so I have documented a quick and easy example.

GitHub Source: https://github.com/Radagast27/Simpl_XSLT_dot_net

Tool for XSLT validation: https://www.freeformatter.com/xsl-transformer.html

References:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://www.w3schools.com/xml/xsl_examples.asp

https://www.c-sharpcorner.com/article/transform-xml-output-of-sql-query-using-for-xml-auto-statement-to-html-using-xsl/

Dynamic SQL

"a programming technique that enables you to build SQL statements dynamically at runtime." -Oracle Docs

Dynamic SQL is a very useful tool but should only be used when absolutely necessary. Having SQL compiled at runtime amplifies the threat of SQL injection and the readability of the SQL code (dynamic SQL SPs result in large double and quadruple quoted red text which can make things much more difficult to read than when reading regular highlighted SQL code).

But when circumstances require you to use dynamic SQL to get the data you need (ie. there is no other way to structure your database environment and/or the "dynamism" can't be done equally as easy or easier using some sort of data adapter like .NET CLR code) dynamic SQL may be your best option.

For more in-depth info on this subject, see this great reference from SQL Server MVP Erland Sommarskog on this subject and the pros and cons of using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Here is a brief example of dynamic SQL:

 DECLARE @mult bit = 0  
 DECLARE @sqlToExecute varchar(500)  
 DECLARE @srvWest varchar(50) = 'WestZoneSqlSrv08'  
 DECLARE @srvEast varchar(50) = 'EastZoneSqlSrv08'  
 IF @mult = 0  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ALGO.dbo.[Order]'  
 ELSE  
      SET @sqlToExecute = 'SELECT TOP 1 OrderId, PersonId FROM ' + @srvEast + '.ALGO.dbo.[Order] UNION ALL SELECT TOP 1 OrderID, PersonId FROM ' + @srvWest + '.ALGO.dbo.Order'   
 EXEC(@sqlToExecute)  

Check SQL Server Version from T-SQL

Sql Connection String for .NET

Standard Security:

"Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;"

References:

https://www.connectionstrings.com/sql-server-2016

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax

(on unit vs. integration tests) So maybe this is the distinction I find useful. There are tests where a failure points directly to the problem.

These tests are tests either of well-understood abstractions or using only well-understood abstractions.

Then there are tests where a failure indicates a genuine problem, but where oh where? - Kent Beck

Find all instances of a string within a string in T-SQL

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) 
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Simply use the function above.

If you want to understand technically how this works, see the reference below.

Essentially, this function dynamically creates a table (CTE) of the term being searched for with Number being the Row_Number of each charindex within the string.

This CTE is then searched to elicit the starting positions of each location where string term matches ([WHERE] SUBSTRING(@term + @string, Number, LEN(@term)) = @term).

Usage:

The part: "SELECT ROW_NUMBER() OVER (ORDER BY [object_id].." is there to provide space to establish the CTE with which to store search string indexes. If you need more space to traverse over (your search string is > 2k characters) you can use sys.all_columns instead of sys.all_objects or a CROSS JOIN for tables with greater than 8k records.


Reference: https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

All credits: Aaron Bertrand. He is a T-SQL genius. If it can be done in T-SQL, Aaron has done it, benchmarked it and probably blogged about it: https://sqlperformance.com/author/abertrand

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!

SQL NULL cannot be applied to arithmetic operations (select NULL > 1 = NULL)

"Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null." -http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm#i59110

In SQL Server, this behavior can result in string concatenation failing due to one of the values being NULL, which, as is NULL's nature, turns the entire variable into NULL.

....results in: NULL

Two easy ways to work around* the inability to evaluate NULL are as follows:

 (COALESCE takes first non-NULL value specified whereas ISNULL takes just one alternate value)

Different database platforms have different rules for NULL but arithmetic ops will not work in any, as NULL is only ever NULL, and NULL is an unknown. If you are checking for the existence of data, you should be using:

select top 5 m.Column1 FROM MYTABLE m WHERE m.Column1 IS NOT NULL

*you can do this in lieu of not getting bad data in the 1st place, obviously- if the column in question needs to hold a legitimate numeric value for calculation.. your app probably should not be generating and storing null values into that column.