Custom Functions/Code in SQL Reporting Service

There can be certain instances where we want to call our custom functions on certain Database fields or any other value.

We can write code in .Net class library and Integrate that assembly with our Reports to gain the functionality.

Here I am listing steps to do so:

1. Create a class library project in Visual Studio.

Add the function implementation (for example given below)

Public Class ResourceHelper

Public Function GetString(ByVal Key As String) As String

Return "Formatted Value is " & Key

End Function

End Class

2. Strong name the Assembly.

3. Build the Solution and Deploy this dll to following two locations:

• Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
• Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

4. Click on Report in Designer area and navigate to "Report/Report Properties..." menu.

5. Go to References tab and add a reference to our .dll

6. Under Classes panel, Give Class name as . (here, ResourceTestClassLibrary.ResourceHelper) and Instance name as say objResource and Click OK. (This step is not required when we have function declaration 'static')

7. Add a TextBox to the report and Right Click and go to "Expression..."

Enter following text

=Code.myObj.GetString("Yahoo!!!")

and click OK.
Here we can pass any Database field value or other argument we wish.

8. Build Report Project and see if it succeeds.

On successful Run, you can see this text box containing the result output of that Custom function with that argument.

This is it.

Likewise, we can use Resource files to localize our labels and headers on report. Though deployment of .resx file will slightly wary in that case.

Cheers,