CODE Magazine - Article: Integrating .NET Code and SQL Server Reporting Services

Reader rating:
Click here to read 7 comments about this article.
Article source: CoDe (2007 - Jan/Feb)

Article Pages: < Previous - 1 2 3 - Next >

Integrating .NET Code and SQL Server Reporting Services (Cont.)

Adding Custom Code to a Report

Now that you have created a simple report you can add custom code. SSRS provides two mechanisms for adding code to your report: You can embed Visual Basic .NET code directly in your reports or you can add externally created and compiled assemblies.

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

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 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 (Figure 3). You can type or paste code directly into the XML file.

Click for a larger version of this image.

Figure 3: XML source code showing <Code> section.

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 in Listing 1 demonstrates accessing data from SQL Server and returning it to the report. After adding the code from Listing 1 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 (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.

Figure 4: Missing reference error.

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.

Adding Custom Assemblies

As explained earlier, SSRS provides the ability to add code to your reports using two mechanisms: direct embedding of Visual Basic code or linking to external assemblies. This section discusses creating an external assembly and using it in your reports. Why would you want to create an external assembly for your reports? Two reasons come to mind. The first reason is reuse. You can use external assemblies in multiple reports. You cannot access embedded code from multiple reports. The second reason is that you might prefer writing code in C#, C++, or some other .NET language. As stated earlier, SSRS embedded code is limited to using only Visual Basic code.

So how do you create and embed your own assembly into an SSRS report? It’s pretty simple. The first step is to create a new class library with a shared method (static for C# folks). To create the class library for this example, do the following:

From Visual Studio create a new Class Library Project in your language of choice. Name this project CoDeReportingLibrary.
Rename the class created by default to CodeReportingFunctions.
Add the code from Listing 2 or Listing 3 depending on your choice of language to the CodeReportingLibrary class.
Compile your assembly.

The next step is to add a reference to your new assembly 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. This time select the Browse tab and navigate to your custom assembly (Figure 5). You can now call functions from this library by specifying the property syntax for your shared function in the Value property of a text box. To call a function from an external library, specify the fully delimited syntax for the class; that is, provide the Namespace, Class, and Function name. For this example the value property is given below:

Figure 5: Add Reference dialog box.

=CoDeReportingLibrary._
CodeReportingFunctions._
GetCustomerOrderCount _
(Fields!CustomerID.Value.ToString())

Once you have specified the proper syntax for calling your library you can preview your report. You will immediately receive an error stating that the CodeReportingLibrary class is not found. This error occurs because the Report Designer looks in a specific directory for your assemblies. Take the DLL you created and copy it to the following directory:

C:\Program Files
\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies

Now you can preview the report. The results provided by your report extension are visible on the report output.

Good Books on Reporting Services

Hitchhikers Guide to SQL Server Reporting Services by Bill Vaughn and Peter Blackburn

ISBN: 0321268288

Pro SQL Server 2005 Reporting Services by Rodney Landrum and Walter J. Voytek II

ISBN: 1590594231

Listing 1: Custom code for retrieving customer order counts

Function GetCustomerOrderCount(ByVal CustomerID As
String) As Integer
Dim oConn As New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = _
"Data Source=(local);
  Initial Catalog=Northwind;
  Integrated
  Security=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

Listing 2: Visual Basic shared function for querying data in a custom report extension

Imports System.Security.Permissions
Imports System.Data.SqlClient

Public Class CodeReportingFunctions

Public Shared Function GetCustomerOrderCount(ByVal _
   CustomerID As String) As Integer

  Dim oPerm As New _
  SqlClientPermission(_
  PermissionState.Unrestricted)
  oPerm.assert()

  Dim oConn As New System.Data.SqlClient.SqlConnection
  oConn.ConnectionString = _
     "Data Source=(local);
     Initial Catalog=Northwind;User
     Id=<<USER>>Password=<<PASSWORD>>"
   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
End Class

Listing 3: C# static function for querying data in a custom report extension

using System;
using System.Security;
using System.Security.Permissions ;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace CoDeReportingLibrary
{
public class CodeReportingFunctions
{
  public static int GetCustomerOrderCount(string
  CustomerID)
  {
  System.Data.SqlClient.SqlClientPermission
  oPerm =  new System.Data....
  SqlClientPermission(PermissionState.Unrestricted);
oPerm.Assert();

SqlConnection oConn = new SqlConnection();
oConn.ConnectionString
  = "Data Source=(local);
     Initial Catalog=Northwind
     ;User Id=<<USER>>;Password=<<PASSWORD>>";

oConn.Open();
SqlCommand oCmd = new SqlCommand();
oCmd.Connection = oConn;
oCmd.CommandText =
   "Select count(*)
      From Orders
     Where CustomerID = @CustomerID";
oCmd.Parameters.AddWithValue(
   "@CustomerID", CustomerID);

int nRetVal = (int)oCmd.ExecuteScalar();
oConn.Close();
return nRetVal;
    }
  }
}

Article Pages: < Previous - 1 2 3 - Next Page: 'Deploying Custom Assemblies' >>

Page 1:	Integrating .NET Code and SQL Server Reporting Services
Page 2:	Adding Custom Code to a Report

Page 3:

Deploying Custom Assemblies

How would you rate the quality of this article?

	1	2	3	4	5
Poor						Outstanding

Tell us why you rated the content this way. (optional)

Average rating:
3.9 out of 5

52 people have rated this article.