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
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.
