Custom Code in SSRS

When the customer’s requirement exceeds the capability of Built-in functions of SSRS then we  need to write own Code to meet the requirements. Writing your own code(function) is called Custom Code and this is a very useful feature provided by Microsoft.

In any expression, you can call your own custom code. 

To write your own code go to report menu -->Report Properties-->code

For ex,

Let’s say we have to design a function which returns the Status of the sales of each product. For that lets create a report first which displays Product and Sales Amount as shown in the below pic -

• Select “Report Properties” from Reports main menu or in design View Right Click the design surface outside the border of the report and select “Report Properties” as shown below.

• Select Code tab and paste the following code to create custom function

Public Function Test(ByVal Sales As Decimal) As String
Dim status As String
If Sales >= 2000 Then
status = “High Profit”
ElseIf Sales >= 500 Then
status = “Moderate Profit”
Else
status = “Low Profit”
End If
Return status
End Function

• Click OK and now add a column to the Table used in report and give header as “Status” and right click on the data sell and select Expression as shown below.

• Write the expression as shown below and select OK.

=Code.Test(Fields!SalesAmount.Value)

 — “Fields!SalesAmount.Value” field based on which you wish to calculate status.

• Your report with Custom Function to calculate the Status(in other words KPI) is ready and if you click on preview you can see the output of the function you created.