Using Custom Code References in Expressions (Reporting Services)

You can add custom functions to a report or add references to functions that exist in external assemblies. The report server automatically adds references for the Microsoft.VisualBasic, System.Convert, and System.Math assemblies. Additional assemblies can be referenced using the Report Properties dialog box or the CodeModules element in the report definition.

The Report Properties dialog box can also be used to define new custom functions. To define custom functions in the report definition, use the Code element. However, functions cannot be passed sets of data values; specifically, custom aggregates are not supported.

You can write custom code that is used in expressions throughout a report. You can do this in two ways: by embedding code within a report, or by referring to methods within a custom assembly. Use embedded code for complex functions or functions that are used multiple times in a single report. Use code assemblies to maintain code in a single place and share it across multiple reports.

Embedded Code

To use code within a report, you add a code block to the report. This code block can contain multiple methods. Methods in embedded code must be written in Microsoft Visual Basic and must be instance-based.

For more information about how to add code to a report, see How to: Add Code to a Report (Report Designer).

Methods in embedded code are available through a globally defined Code member. You access these by referring to the Code member and the method name. The following example calls the method ToUSD, which converts the value in the StandardCost field to a dollar value:

=Code.ToUSD(Fields!StandardCost.Value)

To reference global collections in your custom code, include a reference to the built-in Report object:

=Report.Parameters!Param1

The following examples show how to define some custom constants and variables.

Public Const MyNote = "Authored by Bob"
Public Const NCopies As Int32 = 2
Public Dim  MyVersion As String = "123.456"
Public Dim MyDoubleVersion As Double = 123.456

Although custom constants do not appear in the Expression Editor Constants view (which only displays built-in constants), you can add references to them from any expression, as shown in the following examples. In an expression, a custom constant is treated as a Variant.

=Code.MyNote
=Code.NCopies
=Code.MyVersion
=Code.MyDoubleVersion

The following example includes both the code reference and the code implementation of the function FixSpelling, which substitutes Bicycle for all occurrences of the text Bike for the value of the SubCategory field.

=Code.FixSpelling(Fields!SubCategory.Value)

The following code, when embedded in a report definition, shows an implementation of the FixSpelling method. The first time this custom code runs, a message box displays the substituted text. This example shows you how to refer to the Microsoft .NET Framework StringBuilder class and the System.Windows.Forms.MessageBox class. You need to add a reference to your report properties for System.Windows.Forms. For more information, see How to: Add Code to a Report (Report Designer) and How to: Add an Assembly Reference to a Report (Report Designer).

Dim firstTime As Boolean = True 
Public Function FixSpelling(ByVal s As String) As String
   Dim strBuilder As New System.Text.StringBuilder(s)
   If s.Contains("Bike") Then
      strBuilder.Replace("Bike", "Bicycle")
      If firstTime Then
        System.Windows.Forms.MessageBox.Show(strBuilder.ToString())
'       or MsgBox(strBuilder.ToString())
        firstTime = False
      End If
      Return strBuilder.ToString()
      Else : Return s
   End If
End Function

For more information about global object collections and initialization, see Using Global Collections in Expressions and Initializing Custom Assembly Objects.

Custom Assemblies

To use custom assemblies in a report, you must first create the assembly, make it available to Report Designer, add a reference to the assembly in the report, and then use an expression in the report to refer to the methods contained in that assembly. When the report is deployed to the report server, you must also deploy the custom assembly to the report server.

For information about creating a custom assembly and making it available to Reporting Services, see Using Custom Assemblies with Reports. For instruction about how to add a reference to a report, see How to: Add an Assembly Reference to a Report (Report Designer).

To refer to custom code in an expression, you must call the member of a class within the assembly. How you do this depends on whether the method is static or instance-based. Static methods within a custom assembly are available globally within the report. You can access static methods in expressions by specifying the namespace, class, and method name. The following example calls the method ToGBP, which converts the value of the StandardCost value from dollar to pounds sterling:

=CurrencyConversion.DollarCurrencyConversion.ToGBP(Fields!StandardCost.Value)

Instance-based methods are available through a globally defined Code member. You access these by referring to the Code member, followed by the instance and method name. The following example calls the instance method ToEUR, which converts the value of StandardCost from dollar to euro:

=Code.m_myDollarCoversion.ToEUR(Fields!StandardCost.Value)

See Also

Other Resources

Using Expressions in Reporting Services

Help and Information

Getting SQL Server 2005 Assistance

Change History