Custom Assemblies in SSRS

 Custom assemblies in SQL Server Reporting Services (SSRS) allow you to extend the functionality of your reports by incorporating custom .NET code. This is useful for implementing complex logic, reusable functions, or leveraging external libraries. Here’s a comprehensive guide on how to create and use custom assemblies in SSRS:

Steps to Create and Use Custom Assemblies in SSRS

1. Create a Custom Assembly
2. Deploy the Custom Assembly
3. Reference the Assembly in SSRS
4. Use the Assembly in Expressions
5. Security and Configuration

1. Create a Custom Assembly

Example: Creating a Simple Assembly

1. Create a New Class Library Project:

   • Open Visual Studio and create a new Class Library project.

2. Write the Custom Code:

   • Add the methods you need in your report. For example, a simple method to format a string.

     csharp
     using System;
     
     namespace CustomAssembly
     {
         public class ReportUtilities
         {
             public static string FormatCurrency(decimal amount)
             {
                 return "$" + amount.ToString("N2");
             }
         }
     }
     
     

3. Build the Assembly:

   • Build the project to generate the DLL file, typically located in the bin\Debug or bin\Release folder.

2. Deploy the Custom Assembly

1. Copy the DLL:

   • Copy the generated DLL file to the SSRS report server’s bin directory:
     • For a native mode installation, the directory is typically:

       arduino
       C:\Program Files\Microsoft SQL Server\MSRSXX.MSSQLSERVER\Reporting Services\ReportServer\bin
       
       

2. (Optional) Deploy to Report Designer:

   • If you want to use the assembly in Report Designer (e.g., in Visual Studio), copy the DLL to the Visual Studio private assemblies directory:

     java
     
     C:\Program Files (x86)\Microsoft Visual Studio XX.0\Common7\IDE\PrivateAssemblies
     

3. Reference the Assembly in SSRS

1. Open Your Report in Report Designer:

   • Open the report where you want to use the custom assembly.

2. Add Assembly Reference:

   • In the Report Data pane, right-click on the report and select "Report Properties."
   • Go to the "References" tab.
   • Click "Add" under "Assemblies" and browse to the DLL file, or simply type the assembly name if it’s in the GAC or the report server bin directory.

3. Add Namespace Imports:

   • Add the namespace(s) of the custom assembly to the report to make it easier to call its methods.

     vb
     
     CustomAssembly.ReportUtilities
     

4. Use the Assembly in Expressions

1. Call the Assembly Methods in Expressions:
   • In the report, you can now use expressions to call methods from the custom assembly.

     vb
     
     =CustomAssembly.ReportUtilities.FormatCurrency(Fields!SalesAmount.Value)
     

5. Security and Configuration

1. Update RSSrvPolicy.config and RSPreviewPolicy.config:
   • By default, SSRS runs custom assemblies with limited permissions. To grant additional permissions, update the RSSrvPolicy.config (for the report server) and RSPreviewPolicy.config (for report preview in Visual Studio).

Example: Adding Code Group to RSSrvPolicy.config

xml
<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="FullTrust"
    Name="CustomAssemblyCodeGroup"
    Description="Code group for custom assemblies">
  <IMembershipCondition
      class="UrlMembershipCondition"
      version="1"
      Url="C:\Program Files\Microsoft SQL Server\MSRSXX.MSSQLSERVER\Reporting Services\ReportServer\bin\CustomAssembly.dll"
  />
</CodeGroup>

2. Ensure Appropriate Security Settings:
   • Make sure that your assembly does not perform actions that require higher trust levels unless explicitly needed, as modifying these settings can have security implications.

Example Walkthrough

1. Create and Build the Assembly

• Name: CustomAssembly
• Namespace: CustomAssembly
• Class: ReportUtilities
• Method:

  csharp
  
  public static string FormatCurrency(decimal amount)
  {
      return "$" + amount.ToString("N2");
  }
  

2. Deploy the Assembly

• Copy CustomAssembly.dll to:
  • C:\Program Files\Microsoft SQL Server\MSRSXX.MSSQLSERVER\Reporting Services\ReportServer\bin
  • (Optional for Report Designer) C:\Program Files (x86)\Microsoft Visual Studio XX.0\Common7\IDE\PrivateAssemblies

3. Reference and Use in SSRS

• In Report Designer:
  • Go to Report Properties -> References
  • Add CustomAssembly.dll
  • Add Namespace: CustomAssembly.ReportUtilities
• Use in Expression:

  vb
  
  =CustomAssembly.ReportUtilities.FormatCurrency(Fields!SalesAmount.Value)
  

4. Configure Security

• Edit RSSrvPolicy.config and RSPreviewPolicy.config as needed to grant the required permissions.

By following these steps, you can extend SSRS reports with custom assemblies, allowing for more complex logic, reusable functions, and leveraging external libraries to enhance your reporting capabilities.