Embedding VB.NET Code
SSRS provides the capability of embedding Visual Basic .NET code directly within report layouts. Embed code into reports by selecting Report | Report Properties
from the Visual Studio shell, and then select the Code tab in the provided dialog box. Enter the following code into the "Custom Code" field and click OK:
Function CoDeDemo(ByVal cField As String) As String
|Figure 3: XML source code showing <Code> section.|
You can call your new function from your report by using the Code
function provided via the SSRS expression syntax. To use your new function, switch to the Layout tab in the Report Designer. In the Company Name field, change the Value
property from =Fields!CompanyName.Value
. Now select the Preview tab. The output for the CompanyName
value displays in upper case.
If you don't like editing code using small dialog boxes, you have another mechanism for editing embedded report code. In the Solution Explorer, right-click the report and select "View Code" from the provided pop-up menu. Search for the <Code>
element in the provided XML source code (see Figure 3
). You can type or paste code directly into the XML file.
By default the SSRS custom code mechanism is limited to the most basic functions of the .NET Framework. To add more advanced capabilities (data access for instance) you must add references to the desired assemblies. The code below demonstrates accessing data from SQL Server and returning it to the report.
Function GetCustomerOrderCount( _
ByVal CustomerID As String) As Integer
Dim oConn As New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=Northwind; IntegratedSecurity=SSPI"
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandText = "Select count(*) From Orders " & _
"Where CustomerID = @CustomerID"
Dim nRetVal As Integer = oCmd.ExecuteScalar()
After adding the preceding code to the report you can test it by adding a new column to the report. From the Layout tab add a new column and set its Value
property to =Code.GetCustomerOrderCount(Fields!CustomerID.Value)
. When you select the Preview tab you see a compilation error in the Error List tab of the Visual Studio designer (see Figure 4
). This signifies that a reference to the assembly containing the System.Data.SqlClient.SqlConnection class is missing. To fix this issue you need to add references to the System.Data and System.XML assemblies.
To add a reference to your report, open the Report Properties dialog box from the Visual Studio Designer and select the References tab. Click the ellipsis button. This opens an Add Reference dialog box. Select the System.Data and the System.Xml assemblies from the provided dialog box. Previewing your report should now show the number of orders for each customer record.