Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

No product can satisfy all developers out of the box. SQL Server Reporting Services is no exception. But you can extend the SSRS expression system with .NET code.

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 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.

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 Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\bin

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 Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer 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.

<CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="CoDeMagSample" Description="CoDe Magazine Sample. "> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer\bin\ CoDeReportingLibrary.dll" /> </CodeGroup>

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.

Rod Paddock is Editor-in-Chief of CoDe Magazine.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.