QL Server Reporting Services versions 2000 and 2005 (SSRS) has many powerful features. SSRS has a well-designed data access engine, a great set of layout tools, and an excellent expression system for creating complex formulas. While the expression system is quite powerful it is not suitable for all applications. This is where SSRS shines. SSRS gives developers the ability to add custom code to their report layouts. This article demonstrates adding custom code to SQL Server Reporting Services reports.

Creating the Sample Report
This article uses data from the SQL Server sample database Northwind. To create new reports, you’ll perform the following tasks.

1. Create a new SSRS report by selecting File | New Project from the Visual Studio menu.
2. Select Business Intelligence Projects | Report Server Project from the provided dialog box. Name the report “CodeReportingServices.”
3. In the Solution Explorer, right-click the Shared Data Sources folder and select Add New Data Source from the pop-up menu. This will activate the Shared Data Source dialog box. Name the data source CodeNorthwindDataSource.
4. Click the Edit button to activate the Connection Properties dialog box.
5. Enter the name of the server in the Server Name field and provide the login credentials to the server (if necessary).
6. Select the Northwind database from the list of databases and click OK.
7. On the New Data Source dialog box, click OK to save the new data source.

Now create a report layout.

1. In the Solution Explorer, right-click the Reports Folder and select Add New Item from the pop-up menu.
2. Select Report from the Add New Item dialog box. Name this report CodeNorthwindCustomerReport.rdl.
3. From the Report Layout toolbar, select the Data tab.
4. Select from the Dataset drop-down list.
5. In the provided dialog box, name the dataset NorthwindCustomers and specify CodeNorthwindDataSource in the “Data source” drop-down list.
6. In the query string field type SELECT * FROM Customers.
7. Click the exclamation mark to execute the query. This will return a list of customers.
8. Switch to the Layout tab of the report.
9. Switch to the Report Items toolbox.
10. Drag a Table object from the Report Items toolbox onto the report.
11. Switch back to the DataSets toolbox and drag the CompanyName and ContactName columns onto the Table object’s detail band.
12. The design should look like the one in Figure 1.
13. You can also preview the report by selecting the Preview tab in the Report Designer. Figure 2 demonstrates what the report looks like in Preview mode.

? Figure 1: Customer report in Design mode.

? Figure 2: Customer report in Preview mode.

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

	?
Figure 3: XML source code showing 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 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.

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:

	?
Figure 5: Add Reference dialog box.

1. From Visual Studio create a new Class Library Project in your language of choice. Name this project CoDeReportingLibrary.
2. Rename the class created by default to CodeReportingFunctions.
3. Add the code from Listing 1 or Listing 2 depending on your choice of language to the CodeReportingLibrary class.
4. 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 (see 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:

   =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 FilesMicrosoft Visual Studio 8      Common7IDEPrivateAssemblies

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

Deploying Custom Assemblies

	?
Figure 6: Report Deployment Options dialog box.

Once you have created your report, you now need to deploy it to your report server. The first step to deploying a project is to specify the Web server location for your SSRS installation. To specify the location for your Web server, right-click the reporting project in the Solutions Explorer and select Properties from the pop-up menu. Specify the location of your Web server in the TargetServerUrl field (see Figure 6) and save your changes by clicking OK. Now you can deploy your report by right-clicking again and selecting Deploy from the pop-up menu. The first time you deploy the report you will receive an error informing you that your custom assembly is missing from the reporting server. Copy your DLL to the following directory:

   C:Program FilesMicrosoft SQL Server      MSSQL.3Reporting ServicesReportServerin

Deploying your report should succeed this time. But there is another problem. If you attempt to run your report you will see an error value where the number of orders should be. So what is the problem now? SSRS does not trust your assembly to execute code from the System.Data.SqlClient assembly. How do you fix this? You need to add some XML to the security policy file for SSRS. To change the SSRS security policy, navigate to the C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer directory and open the rssrvpolicy.config file. This file contains the security settings that SSRS uses to protect itself from rogue code. Search for the value $CodeGen$. Add the code below after that code group’s ending element.

After adding configuration information for your assembly you need to do one more thing. Your method code needs to assert the proper permissions. The permission to assert is the SqlClientPermission. The first step is to import the System.Security.Permissions assembly into your class. After importing this assembly, add the following code at the beginning of your method:

	?
Figure 7: Final Customer report output.

   SqlClientPermission oPerm = new SqlClientPermission(      PermissionState.Unrestricted);   oPerm.Assert();

In Visual Basic add the following code:

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

This code tells the CLR that this assembly can run code from the SqlClient class library. Now you can test your report in the SSRS report manager. Figure 7 shows the contents of your deployed report in the SSRS Report Manager.

Adding .NET code to a SQL Server Reporting Services application is not difficult. You can extend SSRS applications in virtually infinite ways using these mechanisms.