Friends,
SSRS is a very good reporting tool and the only problem with this is This is a LIMITED one I feel. Limited in the sense, that the functions you can use is limited and hence it may not help you to acheive all the requirements of the clients. When the customer’s requirement exceeds the capability of Built-in functions of SSRS then we will need to write own 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 this article I will demonstrate how to add custom code to SSRS.
There are two ways in which you can write your custom functions and use it in your report.
- Writing Code inside the report
- Using Custom Assemblies
Let us discuss about the first option here:
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 Funcmtion 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.
That’s it .. Let us discuss about the second option in next post. Hope you understood how to add your custom code and make use of it in your report. Happy Coding !!
Regards,
Roopesh Babu V
