Integrating .NET Code and SQL Server Reporting Services : Page 2

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
      Return cField.ToUpper()
   End Function

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 to =Code.CoDeDemo(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"
      oConn.Open()
      Dim oCmd As New System.Data.SqlClient.SqlCommand
      oCmd.Connection = oConn
      oCmd.CommandText = "Select count(*) From Orders " & _
         "Where CustomerID = @CustomerID"
      oCmd.Parameters.AddWithValue("@CustomerID", CustomerID)
      Dim nRetVal As Integer = oCmd.ExecuteScalar()
      oConn.Close()
      Return nRetVal
   End Function

Figure 4: Missing reference error.

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.

Previous Page

Next Page