Last week, I demonstrated how to embed code directly into a SQL Server Reporting Service (SSRS) report.

In this article, I will explain how to reference code in an external assembly from an SSRS report. The basic steps are

  1. Create External Code
  2. Create Unit Tests
  3. Deploy the assembly to the Report Server
  4. Add a reference to the assembly
  5. Call external functions in Expression Editor
  6. Deploy Report

Create External Code

The first step is to create and compile the external code. The project type will be a Class Library and you will add a public class with a public static method. This code can be in C#, Visual Basic, or F#.
A sample is shown below in Listing 1.

using System;

namespace ReportFunctions
{
    public class ReportLib
    {
        public static string FormatAs2Digits(decimal? input)
        {
            if (input == null)
                return "N/A";
            else
                return String.Format("{0:##,##0.00;(##,##0.00)}", input);
        }

    }
}
Listing 1

Compile this code in Release mode

Create Unit Tests

It's a good idea to create unit tests around this code because it can be difficult to test it on the Report Server.
At a minimum, write tests that mimic how you expect to call the function within your reports.

Deploy Assembly to Report Server

In order to use the functions, you must deploy the compiled DLL to the report server. You can either create a Setup  project to create an MSI package or you can simply copy the DLL to the drive where SQL Server Reporting Services is installed in the following folder on the SQL Server installation drive:

\Program Files\Microsoft SQL Server\Instance_Name\Reporting Services\ReportServer\bin

where Instance_Name is the name of the instance of SQL Server on which SSRS is running.

Add a reference to the assembly

Open your Report project and open the report that will call the custom function. From the menu, select Report | Report Properties. Select the References tab (Fig. 1).


Fig. 1 – “Reference” tab of Report Properties

Browse to select the deployed assembly containing the code you want to call.

After adding the reference, you will need to compile the Report project before you  can use the assembly functions. To compile the report, select Buld | Build Solution from the menu.

Call external functions in Expression Editor

Open an expression editor and call a function in the external assembly. You will need to include the entire namespace and classname. In our example, this be

An example is shown in Fig. 2.


Fig. 2 – Expression Editor

You can test that the expression works by clicking the Preview tab of the report.

Deploy Report

The final step is to deploy the report. Assuming you have permissions on the Report Server and the report sever is set in the project properties, the easiest way to deploy is to right-click the report in the Solution Explorer and select Deploy.

Now you can test the report and the function on the Report Server.

Conclusion

In this article, we described how to call code in an external assembly from a SQL Server Reporting Services report.