There are numerous circumstances when we wish to add a custom function to a SSRS report in order to cater for needs of the customer which exceeds the capability of the built-in functions in SSRS. In these scenarios we will have to write our own functions. In this article I will demonstrate how to add custom code to SSRS.
Using SQL Server 2008 R2 Business Intelligence Studio
Using Custom Code inside a report: In this article we will consider a scenario where we want to design a KPI using custom code inside a report using the code tab of the report properties. For this we will have to write the below VB code as VB is the only language currently supported for the custom code in SSRS. Create a new SSRS project in BIDS studio and add new item as a report from the templates.1. Create a dataset using AdventureWorks as datasource the following query:
SELECT top 1 Name, StandardCost, ListPrice, ListPrice - StandardCost AS ProductProfit FROM Production.Product WHERE (StandardCost >= 1000)2. Select Report-> Report Properties from the menu.
3. Select Code tab from the report property window
4. At present only VB supported for writing custom code inside the report. Copy and paste the below code in the code window and click ok:
Public Shared Function Test(ByVal profit As Decimal) As String
Dim st As String
If profit >= 1000 Then
st = "High Profit"
ElseIf profit >= 500 Then
st = "Moderate Profit"
Else
st = "Average Profit"
End If
Return st
End Function
5. Right-click on the textbox and go to expressions.
6. To call the function written in the custom code window you will have to enter Code.FunctionName . In the expression in our example it would be something like this:
=Code.Test(Fields!ProductProfit.Value)7. The final Output should be something like this.
Using Custom Assemblies:
The custom assemblies can be created using a class library project to create more advanced functionality for your reporting solution. The code reference for function to be used in the reporting services can be given in the references section in the report properties. To do this create a custom assembly and create a class library project called TestClass and add the below code to it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestClass
{
public class Class1
{
public static int Cal(int a, int b)
{
return a + b;
}
}
}
Compile the code to generate the TestClass.dll file. To access the code inside this file we need to place this dll in the default location of the Report Designer which is \Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
To add a reference to the file inside the report perform the following steps:
1. Select Report-> Report Properties from the menu.
2. Select the Reference tab from the report property window
3. Add a reference to the TestClass.dll by browsing to the location of the default report designer i.e \Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
4. Add the following code in the expression of the textbox of the tablix.
=TestClass.Class1.Cal(3,4) This code calls the function defined in the assembly reference. Here you don’t need to use the Code keyword to access the function defined in the Custom assembly. You can also define an instance of the class used in the custom assembly but this is optional as you can access the function using the namespace.classname.functioname inside the expression as shown below:-
5. The output should be as follows
For the custom code to be working after deploying the report you will need to paste the testClass.dll – i.e the dll file in the bin folder of the reporting services whose default location is
\Program Files\Microsoft SQL Server\Instance_Name\Reporting Services\ReportServer\bin
Finally, just click right on the solution and deploy it on the report server to view it in the production environment.
