Bryant Likes

Writing Custom Code in SQL Server Reporting Services - Part II

In my first article on Custom Code I went through the basics of using custom code. The goal of this article is to answer a question that was asked in the comments of the first article.

Sidney asked:

Could you elaborate on instance based classes and the procedures required to make this work.

So I looked into instance based code to see how it works and it is actually very simple.

Instance Based Code

In the first article we created a simple assembly called MyCustomAssembly that had a class called SayHello. For this article we will add an instance based method to that class and then call it from our report. First let's add the new method to the class. Below is the new code highlighted in bold.

[VB]
Public Class SayHello

  Private counter As Int32 = 0

  Public Function HelloWithCount() As String
    counter += 1
    Return String.Format("Hello! (for the {0} time)", counter)
  End Function

...

[C#]
public class SayHello
{

  private int counter = 0;

  public string HelloWithCount()
  {
    return string.Format("Hello! (for the {0} time)", ++counter);
  }

...

This new method will be instance based and will change the output based on how many times it has been called. So if we call HelloWithCount() three times we would expect the third call to output "Hello! (for the 3 time)".

After you've added the new method re-compile the project. You will need to copy the dll file again to the C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer folder on your computer and overwrite the old version. Next open the report you're using to test the dll and add a reference to the dll (instructions here) if you haven't already done so in the last article. This time we also need to add an entry in the Class section.

In the class section add a new entry by clicking the the class name box and typing "MyCustomAssembly.SayHello" without the quotes. Next click in the instance box next to it and type "hello" without the quotes. This basically adds a variable named hello to the report of the type SayHello. So when the report is run a new instance of this object will be created (even if you don't access the object).

Now that we have an instance of our object we can make calls to it. Add three textboxes to your report and put "=Code.hello.HelloWithCount()" in each one. When you click the preview tab your report should run and you should see a different output in each box (1,2, and 3). You now have used instance based code in your report.

That's it! Hopefully you were able to get everything to work. Now you should have a basic understanding of how to use instance based code with reporting services. If you have a question leave a comment and I will try to address it. Thanks again for the great comments on my last article!