• Article

Using Custom Code References in Expressions (Report Builder 2.0)

You can add references to custom code embedded in a report. Use embedded code for custom constants, complex functions or functions that are used multiple times in a single report.

For time-sensitive calculations that are evaluated once at run-time and that you want to remain the same value throughout report processing, consider whether to use a report or group variable. For more information, see Using Report and Group Variables Collections References in Expressions (Report Builder 2.0).

Custom code can include new custom constants, variables, functions, or subroutines. You can include read-only references to built-in collections such as the Parameters collection. However, you cannot pass sets of report data values to custom functions; specifically, custom aggregates are not supported.

Embedded Code

To add embedded code to a report, use the Code page of the Report Properties dialog box. The code block you create can contain multiple methods. Methods in embedded code must be written in Microsoft Visual Basic and must be instance-based. The report processor automatically adds references for the System.Convert and System.Math namespaces. In Report Builder 2.0, references to additional assemby references are not supported.

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 built-in collections in your custom code, include a reference to the built-in Report object:

=Report.Parameters!Param1.Value

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 Constants category in the Expression dialog box (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

For more information about built-in object collections and initialization, see Using Global Collections in Expressions.

Examples of Referencing Parameters from Custom Code

You can reference the global parameters collection via custom code in a Code block of the report definition or in a custom assembly that you provide. The parameters collection is read-only and has no public iterators. You cannot use a Visual Basic For Each construct to step through the collection. You need to know the name of the parameter defined in the report definition before you can reference it in your code. You can, however, iterate through all the values of a multivalue parameter. For more information, see Using Custom Code References in Expressions (Report Builder 2.0).

The following table includes examples of referencing the built-in collection Parameters from custom code:

Description

Reference in Expression

Custom Code definition

Passing an entire global parameter collection to custom code.

This function returns the value of a specific report parameter MyParameter.

=Code.DisplayAParameterValue(Parameters)

Public Function DisplayAParameterValue(

ByVal parameters as Parameters) as Object

Return parameters("MyParameter").Value

End Function

Passing an individual parameter to custom code.

This example returns the value of the parameter passed in. If the parameter is a multivalue parameter, the return string is a concatenation of all the values.

=Code.ShowParametersValues(Parameters!DayOfTheWeek)

 
Public Function ShowParameterValues(ByVal parameter as Parameter)
 as String
   Dim s as String 
   If parameter.IsMultiValue then
      s = "Multivalue: " 
      For i as integer = 0 to parameter.Count-1
         s = s + CStr(parameter.Value(i)) + " " 
      Next
   Else
      s = "Single value: " + CStr(parameter.Value)
   End If
   Return s
End Function

Custom Assemblies

In Report Builder 2.0, custom assemblies are supported only on the report server. For more information about accessing your code after it has been installed on the report server, see "Accessing Custom Assemblies Through Expressions" in the Reporting Services documentation in SQL Server Books Online.