Custom Code in SSRS Expressions and Functions

Custom code is a powerful feature of SQL Server Reporting Services (SSRS) that allows developers to add custom code to their reports. This code can be used to perform complex calculations, manipulate report data, or integrate external systems. In this tutorial, we will explore how to use custom code in SSRS expressions and functions.

Syntax

The syntax for using custom code in SSRS expressions and functions is as follows:

=Code.<functionName>(<parameters>)

Example

Consider the following example where we have a dataset that contains information about sales revenue. We want to create a report that shows the total revenue for each sales representative:

SalesRep	Revenue
John	500
Jane	750
Mike	1000
John	250
Jane	500
Mike	750

To calculate the total revenue for each sales representative, we can use the following custom code:

Dim RevenueTotal As New Dictionary(Of String, Double)()

Function AddRevenue(SalesRep As String, Revenue As Double) As Double
    If RevenueTotal.ContainsKey(SalesRep) Then
        RevenueTotal(SalesRep) += Revenue
    Else
        RevenueTotal.Add(SalesRep, Revenue)
    End If
    Return RevenueTotal(SalesRep)
End Function

We can then add the following expression to the report to display the sales revenue for each sales representative:

=Code.AddRevenue(Fields!SalesRep.Value, Fields!Revenue.Value)

Output

The output of the report will be a table showing the sales revenue for each sales representative:

SalesRep	Total Revenue
John	750
Jane	1250
Mike	1750

Explanation

In the example above, we created a custom function called AddRevenue that takes in two parameters: the name of the sales representative (SalesRep) and the revenue associated with the sales (Revenue).

We created a Dictionary(Of String, Double) called RevenueTotal that stores the total revenue for each sales representative.

In the AddRevenue function, we first check if the SalesRep already exists in the RevenueTotal dictionary. If it does, we add the Revenue to the existing total. If not, we add the SalesRep to the RevenueTotal dictionary with the initial value of Revenue.

The expression in the report (=Code.AddRevenue(Fields!SalesRep.Value, Fields!Revenue.Value)) calls the AddRevenue function with the appropriate parameters and displays the total revenue for each sales representative in the report.

Use

Custom code is a powerful feature that can be used to perform complex calculations, manipulate report data, or integrate external systems. When creating custom code, it is important to keep in mind the performance implications and to test the code thoroughly.

Important Points

• Custom code in SSRS can be used to perform complex calculations, manipulate report data, or integrate external systems.
• The syntax for using custom code in SSRS expressions and functions is =Code.<functionName>(<parameters>).
• Custom code should be tested thoroughly and optimized for performance.

Summary

Custom code is a powerful feature in SSRS that allows developers to add custom code to their reports. This code can be used to perform complex calculations, manipulate report data, or integrate external systems. The syntax for using custom code in SSRS expressions and functions is =Code.<functionName>(<parameters>). When creating custom code, it is important to keep in mind the performance implications and to test the code thoroughly.