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
{ publicclass ReportLib { publicstaticstring FormatAs2Digits(decimal?
input) { if (input == null) return"N/A"; elsereturn String.Format("{0:##,##0.00;(##,##0.00)}",
input); } } }

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.

Sometimes I find myself applying the same formatting or performing the same task to many elements within a SQL Server Reporting Services (SSRS) report.

When this happens, I consider writing a reusable function to perform this task or formatting. Functions can be added directly to an SSRS report.

To add code to an SSRS report, open the report and select Report | Report Properties from the menu. The Report Properties dialog displays. Select the Code tab to bring this tab forward (Fig. 1).

Fig 1: The Code tab of the SSRS Report Properties dialogue

This tab contains a single edit box into which you can type Visual Basic code. The editor is very limited in that you can only use Visual Basic (no C#, F#, or JavaScript), and it provides no IntelliSense. Still, it does allow you to create functions that can be called elsewhere within your report. Type your function within this edit box.

Note: If, like me, you are having trouble writing valid code with the IntelliSense or other syntax-checking, It might be helpful to create a console application in Visual Studio and type the same function in order to validate the function's syntax and functionality.

In SSRS, you assign an expression to an object’s property using the Expression Editor (Fig. 2)

Fig2: The SSRS Expression Editor

In an Expression editor of any object on the report, you can call the function with the word "Code", followed by ".", followed by the name of the function.

For example, I found that I had a number of textboxes that displayed numeric data. I wanted every number to display with the following rules:

• Format the numeric output so exactly 2 digits appear to the right of the decimal point
• Print "N/A" for null values.

I could accomplish this by doing the following:

1. Set the formatting of every textbox to "##,##0.00;(##,##0.00)"
2. Change the expression in each textbox to something like:
   =Iif(IsNothing (Fields!Price), "N/A", Fields!Price)

But this is inefficient because one needs to perform the above steps for every textbox where this change is needed.

Instead, I created the following function and embedded it into the report:

          Public
          Shared
          Function FormatAs2Digits(ByVal input as Nullable(
of decimal)) asstringreturn Iif(IsNothing
(input), "N/A", Format(input, "##,##0.00;(##,##0.00)")) End Function

Then, I could set the expression of each textbox to something similar to the following

=Code.FormatAs2Digits(Fields!Price)

Be aware that your report runs under a specific user context and that user will likely have very limited access rights, so your functions will be limited in what that user context can do. Generally, I only use functions to format data and perform other tasks encapsulated within the report.

Despite its limitations, an embedded function is an excellent way to create reusable code and consume it in multiple objects within an SSRS report.