Share via

  • Article

Using Custom Code References in Expressions (Reporting Services)

You can add references to custom code embedded in a report or to custom assemblies that you build and deploy to the report client and to the report server. Use embedded code for custom constants, complex functions or functions that are used multiple times in a single report. Use custom code assemblies to maintain code in a single place and share it across multiple reports.

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 Collection References in Expressions (Reporting Services).

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. Use the References page of the Report Properties dialog box to add additional assembly references. For more information, see How to: Add Code to a Report (Reporting Services) and How to: Add an Assembly Reference to a Report (Reporting Services).

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

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

=Code.FixSpelling(Fields!SubCategory.Value)

The following code, when embedded in a report definition code block, shows an implementation of the FixSpelling method. This example shows you how to use a fully qualified reference to the Microsoft .NET Framework StringBuilder class.

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")
      Return strBuilder.ToString()
      Else : Return s
   End If
End Function

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

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 (Reporting Services).

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

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 (Reporting Services).

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)

Note

In Report Designer, a custom assembly is loaded once and is not unloaded until you close Visual Studio. If you preview a report, make changes to a custom assembly used in the report, and then preview the report again, the changes will not appear in the second preview. To reload the assembly, close and reopen Visual Studio and then preview the report.

For more information about accessing your code, see Accessing Custom Assemblies Through Expressions.