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/