Custom Assembly in SQL Server Reporting Services (SQL Server 2008)

As we discussed in the last article, sometimes it may be necessary to add functionality which is too complicated for embedded code to handle or there could be some functionality that is to be used in various reports. Since Embedded code can only be used in the report where you have declared, to make the functionality available in multiple reports, you can use custom assembly.

Let us continue with the previous example where depending upon the count, we will specify the font color. We will take the value of count as a parameter and return the font color.

Let us create a class library with following code:

C#

namespace CustomAssembly
{
    public class CustomClass
    {
        public string ReturnColorName(Int16 count)
        {
            string returnValue = string.Empty;

            if (count == 1)
            {
                returnValue = "Red";
            }
            else if (count <= 10)
            {
                returnValue = "Yellow";
            }
            else
            {
                returnValue = "Green";
            }

            return returnValue;
        }
    }
}

VB.NET

Namespace CustomAssembly
    Public Class CustomClass
        Public Function ReturnColorName(ByVal count As Int16) As String
            Dim returnValue As String = String.Empty

            If count = 1 Then
                returnValue = "Red"
            ElseIf count <= 10 Then
                returnValue = "Yellow"
            Else
                returnValue = "Green"
            End If

            Return returnValue
        End Function
    End Class
End Namespace

Build the assembly. Go to Report, Report Properties and Select References tab

We need not give the instance name if we are working with static method. In this case we are working with an instance method.

Now we need to copy the assembly created in the folder :

<drive name>\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

when we are working with the designer.

To make it available for deployed reports, we would need to copy it in the following folder:

<drive name>\Program Files\Microsoft SQL Server\MSRS10.SQL08\Reporting Services\ReportServer\bin

Now specify following expression for font color

=Code.MyClass.ReturnColorName(Fields!<count field name>.Value)

And you are good to go!