Adding Custom Assemblies in SQL Server Reporting Services 2005

Introduction

SQL Server Reporting Services (SSRS) is a very powerful tool for generating reports and one of the important features in SSRS is adding custom code and custom assemblies to it to add more functionality to your reports.

Adding Custom Code

I will work with SSRS 2005. First I will add custom code to a sample report. Please note that you can only use VB.NET in adding embedded code for your reports.

I will work with Visual Studio .NET 2005, so let’s start and open it and create a new Report Server Project from Business Intelligence Projects and give it a name ReportSample and specify a location for the project.

Open your Solution explorer, choose Add New Item, select Report template and give it a name SampleRpt.rdl.
Three tabs will be shown for the newly created report: data, Layout and Preview. Open the Layout tab and then go to your menu choose Report->Report Properties, then choose Code tab from the Report Properties window, write the following in Custom Code Field:

Public Function TestFun(ByVal name As String) as String
 Return "Welcome "& name
End Function

After adding the custom code, go back to the report layout and drag textbox from the Toolbox to the report and write the following in the textbox:

=Code.TestFun("M. Abdelghani") 

Choose Preview tab, you shall see Welcome M. Abdelghani.

Now we are done with adding custom code to the reports. You can add whatever functions you want by adding it to the custom code field in the report properties but what if I don't write my code in VB.NET and what if I already have multiple classes and I want to use their methods in an object oriented way so it’s time to know how to add custom assemblies to the report.

Adding Custom Assemblies

Open a new instance of VS.NET 2005 and create a new class library template. I will use C# but you can use any other language. Give it a name TestLib and specify its location.
Rename Class1.cs in the Solution explorer to WelcomeClass.cs, then open it, add the following methods to the class:

public  string  TestFun(string name)  
{  
   return "Welcome "+name;  
}  
public static string StTestFun(string name)  
{  
   return "Welcome "+name;  
}

I've added both static and instance methods to the class to demonstrate how to call them from the report. Now build the project, go to the project folder and open Bin\Debug folder and copy the TestLib.dll file to the following folder: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies.
Please modify the path to match the path where VS.NET 2005 is installed on your machine.

Now we will return back to our ReportSample project. Go to the menu, choose Report->Report Properties and then open the References tab. In the References section, add our assembly by clicking the browse button and browse for the TestLib.dll file. After adding the assembly and in the same window, go the Classes section and specify the class name as TestLib.WelcomeClass, and choose an instance name to be MyWelcomeClass.

We need to add the class name and instance name if we are going to use instance methods, we don't need to specify a class name for calling static methods. Now go to the layout tab of our report and drag another textbox. I will call the TestFun instance method. To call the instance method in the report, you have to specify an instance of WelcomeClass class which is already done and we have an instance called MyWelcomeClass.

We will write in the new textbox the following:

=Code.MyWelcomeClass.TestFun("M. Abdelghani") 

Now click on the preview tab of the report. We have another “Welcome M. Abdelghani” displayed on the report.

So to call an instance method in the report, it will have the following format:

=Code.ClassInstanceName.InstanceMethodName(ListOfParameters) 

Go back to the layout tab and drag another textbox where we will call our static method, we will write in the textbox the following:

=TestLib.WelcomeClass.StTestFun("M. Abdelghani") 

Then switch back once more to the preview tab. The new textbox will display “Welcome M. Abdelghani”.

So to call a static method in the report, it will have the following format:

=AssemblyName.ClassName.StaticMethodName(ListOfParameters) 

It’s time now to deploy our report project. Go to Solution Explorer, choose the properties of the ReportSample project.

In the ReportSample Property Pages window:

• Set the configuration to be Production.
• Set the StartItem to be SampleRpt.rdl.
• Set TargetServerURL to your URL of the report server, for me: http://localhost/ReportServer.

Then click Ctrl+F5 to deploy the project, Deployment will fail and the following error is displayed:

Error while loading code module: ‘TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’. Details: Could not load file or assembly ‘TestLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. The system cannot find the file specified.

We will go back to the project folder of TestLib class library and copy the TestLib.dll to the following folder:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin
Please modify the path to match the installation folder of SQL Server on your machine.

Now try again to deploy the ReportSample project and the deployment will succeed.

History

• 30^th October, 2008: Initial post