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)
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.
See Also
Other Resources
Using Expressions in Reporting Services
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|
5 December 2005 |
|