Overview

In SQL Server reporting services, you can write custom code in two ways. The first is to embed VB.net code directly into the report. The second is to create a .Net assembly in C# or VB.net and deploy this assembly to the reporting server. Embedding the code is quick and easy, but you have no intelli-sense, code coloring, or any of the other nice IDE features. You can not share the code between reports without doing a copy and paste. A deployed assembly is more difficult to manage (as I will show below), but gives you the full power of the IDE for development, allows you to write unit tests, and allows you to share code between reports.

For my latest project, I started out with embedded code, but then switched to a custom assembly, once I determined that I would be reusing code between reports. The first piece of code I wanted to share, was some code that allows you to do alternating row color in a Tablix with a dynamic number of columns. The other code I wanted to share was for formatting text to display based on a parameter selection and data returned to the database. Normally I would keep that code with the report, but since we made another decision to base the report off of a shared dataset, I knew that other reports would need to take advantage of the formatting logic whenever they used the shared dataset.

Creating the Custom Assembly

1) Create the Assembly

To use a custom assembly, you first need to create the assembly and give it a strong name. I added a Class Library project targeting .Net 3.5 to my report solution and added a signing key. I then added 2 classes, Helper, which will contain general purpose methods, and a class that will contain methods for use with my shared dataset. So far this is no different then if you were working with a regular application.

2) Partially Trusted Callers

Before using your assembly, you will need to configure it to allow Partially Trusted Callers. You do this by adding an assembly level attribute: [assembly:AllowPartiallyTrustedCallers] 

3) A note on Static Variables

One footnote I came across while researching this, and that I wanted to point out, was on the use of static variables. You are advised against using static member (class level) variables, as those variables are shared across all reports. Thus, there is a possibility that sensitive data is displayed unintentionally.   

Using the Custom Assembly in the Designer

To use the custom assembly in the report in the designer environment (i.e. in Visual Studio 2008), you have to deploy the assembly, add a reference, and finally declare an instance (if the method is non-static).

1) Deploy the assembly

Even when you are working locally, in Visual Studio, you MUST deploy your assembly to C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssemblies. If you are still working in Visual Studio 2005, then the path would contain “Visual Studio 8.0” instead. The documentation states that the assembly is only loaded once, which means if you make a change to your custom assembly, you must restart Visual Studio (at least the instance you are using to design the report) before the changes will be picked up.

I found out that I couldn’t even deploy the new assembly with Visual Studio open after I added the reference (next step) because it had a lock on the assembly. I certainly hope that the next version of Reporting Services, which should target Visual Studio 2010, does away with this model and allows us to use project references like everything else.

2) Add a Reference (Assembly)

From within your report, you must add a reference to the assembly. You can reference any assembly in the Base Class Library, in addition to your custom assemblies. Types from System.Math and System.Convert area already available to you. To add a reference, open up the report properties. You can do this by right clicking outside of the report area on the design surface, or by clicking the report properties button.

Report Properties Button, and Context Menu

Once in the report properties dialog, click on References. Then click on the Add button under “Add or remove assemblies” and browse for your assembly. You can select the assembly from your debug folder, or the PrivateAssemblies folder.

3) Add a Reference (Class)

If all you will be dealing with are static methods, then you can skip this step. However, you must remember that you will need to reference the method using it’s fully qualified name (in the screen shot above, that would be BI.ReportLibrary.Helper.[StaticMethodCall]() ). If you want need to deal with instance methods, you will need to complete this step.

Continuing from where we left of in step 2, we have just added the assembly. Now click Add under “Add or remove classes”. Type in the name of the our fully qualified class. In my example this is BI.ReportLibrary.Helper. Then give it an instance name, again in my example, this was mHelper.

You can now reference both static and instance methods using the instance name you provided. What I am unsure on, is if there would be a noticeable performance penalty to add an instance name for types that only contain static methods, just so you wouldn’t have to type out the fully qualified name in your report.

4) Using your custom assembly

To make a call to a static or instance method on which you have previously configured an Class and Instance name for (step 3), you use the syntax: =Code.mHelper.MethodName(). If you are working with only static methods and did not configure a Class/Instance name, than you need to use the fully qualified name without the Code: BI.ReportLibrary.Helper.MethodName()

Deploying the Custom Assembly on the Report Server

1) Deployment

In order for you’re report to successfully deploy to the report server, you must first deploy you’re custom assembly. You do this by copying it to: C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServerbin. I also had to restart the SQL Server Reporting Services windows service in order for the DLL to be loaded.

2) Additional Configuration

You may have to perform additional configuration steps depending on what you are doing in your custom assembly. This usually becomes an issue when you need to execute code in Full trust. The following links talk about granting additional access, and asserting permissions:

• MSDN – Asserting Permissions in Custom Assemblies

Taking it to the Next Level

I already touched briefly upon security and asserting permissions above. Another thing that you may want to do with your custom assemblies, is to access the Global Collections, as well as the Parameters, Fields and Report Items. Unfortunately, while you can access the Globals and User collections, you can not access the Parameters, Fields and Report Items as outlined in this MSDN reference.

Finally there is the topic of debugging. While I am setting up a unit test project to automate the testing of my custom assembly as much as possible, there are times were you still want to be able to step thru your code as it is being executed. The MSDN documentation describes two way to debug your report in VS2008 (SSRS 2008 R2), one using one instance of Visual Studio, the second using two instances. I opted to follow the instructions for the Single Instance of visual studio, since my custom assembly was already part of my reporting solution.

There was one hang-up, and that was I couldn’t get the pop-up preview window to launch when I pressed F5. Instead, my report was being deployed to the report server and was being brought up in the browser. The problem was that by build configuration for DebugLocal had been changed such that the deploy checkbox for the report check box had been checked. It also seems that the documentation is a little incorrect. It states that you should configure your custom assembly project to deploy to C:Program FilesMicrosoft SQL Server100ToolsBinnVSShellCommon7IDE. However, I was getting an error on debug start that indicated that I needed to use C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssemblies, the folder we had to use to get the assembly referenced for the designer.

References

• MSDN – Deploying a Custom Assembly
• MSDN – Accessing Custom Assemblies Through Expressions
• MSDN – Using Strong Name Custom Assemblies
• MSDN – Initializing Custom Assembly Objects
• MSDN – Asserting Permissions in Custom Assemblies
• MSDN – How to: Debug Custom Assemblies

Conclusion

In this post I have shown how to make use of a custom assembly to encapsulate and reuse shared functionality amongst reports in Sql Server Reporting Services. We created a custom assembly, deployed it to our development environment, and then finally our report server.