How to use custom assemblies or embedded code in Reporting ServicesTo customize this column to your needs, we want to invite you to submit your ideas about topics that interest you and issues that you want to see addressed in future Knowledge Base articles and Support Voice columns. You can submit your ideas and feedback using the Ask For It form. There's also a link to the form at the bottom of this column.
For this month's column, John Sirmon will give you the steps to use a custom assembly or embedded code in SQL Server 2000 Reporting Services and SQL Server 2005 Reporting Services. This article was designed to give you the basic steps to get you up and running with a custom assembly in Reporting Services. John is a Support Escalation Engineer with the SQL Developer Support team at Microsoft and has worked for Microsoft since March 2001. He has worked with SQL Server for over 10 years and has extensive development experience with Microsoft Visual Studio. John holds a B. S. degree in Business Administration from The Citadel. He also holds a Microsoft Certified Solution Developer (MCSD) certification and a Microsoft Certified Database Administrator (MCDBA) certification. I hope that you enjoy the column and find it helpful.
Create a custom assemblyTo create a custom assembly, follow these steps:
- Create a Visual Studio class library. On the File menu, point to New, point to Project, and then click Class Library.
- Specify the name and the location. For example, I used SimpleClassLibrary and C:\Documents and Settings\user1\My Documents\Visual Studio 2005\projects.
- Open the Class file (in my case, Class1.vb), and then create the functions that you want to use in Reporting Services. In my case, I just create one simple shared function.
Note Because this is a shared function (static in C#), we do not have to access it by using an instantiated object. Keep this in mind as this has an effect on how we reference our assembly later in the article.
Public Class Class1
Public Shared Function DoSomething() As String
Return "string data returned from custom assembly"
- Once you have finished adding all the code, click Build SimpleClassLibrary on the Build menu. This step creates the assembly or managed .dll in the respective bin folder. In my example, this step created my assembly, SimpleClassLibrary.dll, in the My Documents\Visual Studio 2005\Projects\ SimpleClassLibrary\bin\Debug folder.
Copy the custom assembly to the SQL Reporting Services foldersMake your assembly available to Report Designer and Report Server in Reporting Services. To do this, you must copy your .dll to the Report Designer folder and to the Report Server folder.
Note The path may be a bit different, depending on your installation path.
- For Reporting Services 2005, copy the .dll to the following folders:
- Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
- Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin
- For Reporting Services 2000, copy the .dll to the following folders:
- Program Files\Microsoft SQL Server\80\Tools\Report Designer
- Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin
REM Name: SRSDeploy.bat
REM This batch files copies my custom assembly to my Reporting Services folders.
REM Run this from the directory where the customer assembly was compiled.
REM Be sure to close any applications that have your custom assembly open.
REM This is the SQL Server 2005 version:
copy SimpleClassLibrary.dll "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies"
copy SimpleClassLibrary.dll "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin"
REM This is the SQL Server 2000 version:
REM copy SimpleClassLibrary.dll "C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer"
REM copy SimpleClassLibrary.dll "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin"
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\"
Adding a reference to the custom assembly in Reporting ServicesTo add a reference to your custom assembly, open the Reporting Services report in Report Designer. To do this, follow these steps:
- Open the report that will reference the custom assembly.
- On the Report menu, click Report Properties.
- In the Report Properties dialog box, click the References tab.
- Under References, click the ellipsis (...) button that is next to the Assembly name column header.
Note The Classes section is only for instance-based members. It is not for static members. Static (also referred to as "shared" in some of our Reporting Services documentation) means that the member is available to every instance of the class and every instance uses the same storage location. Static members are declared by using the shared keyword in Microsoft Visual Basic and the static keyword in C#. This can be a bit confusing. What this means is, if your custom assembly contains instance members that you need to access, you will have to specify the class name and instance name in the Classes section. Because the method I will be calling from Reporting Services was defined as static by using the shared keyword in Visual Basic, I'll use the References section instead of the Classes section.
- In the Add References dialog box, click Browse. (In SQL Server 2005, click the Browse tab.)
- Locate and then click the custom assembly. Click Open. (In SQL Server 2005, click Add instead of Open.)
- In the Add References dialog box, click OK.
- In the Report Properties dialog box, click OK.
- Open the report (an .rdl file) in Report Designer.
- To demonstrate how to use the custom assembly in Reporting Services, add a text box. To do this, drag a blank text box from the toolbox to the report.
- In the text box properties, click the Value property, and then call a function by using the following syntax. In my example, I specified the following.
=ClassLibraryName.ClassName.MethodName or Namespace.ClassName.MethodNameOther formats are used to reference code in Reporting Services. For example, use the following format if you call embedded code.
=SimpleClassLibrary.Class1.DoSomething()If you call a non-static, or instance-based, method from within a custom assembly by using an instance, use the following format.
=Code.MethodName()Note You would have set up your reference differently if you wanted to use an instance-based method. To do this, you would need to go to Report Properties, click the References tab, and then specify the class name and the instance name in the Classes section.
Code access security if the custom assembly requires additional permissionsIf the custom assembly requires more permissions than the default Execution level permissions, you must make some code access security changes. If a permissions issue with code access security occurs, you will most likely see "#Error" from your custom assembly instead of the expected results. For a few quick steps that you can perform to determine if this problem is occurring, and for detailed instructions about how to grant additional permissions to your custom assembly, click the following article number to view the article in the Microsoft Knowledge Base:
Create a new CodeGroup for your custom assembly, and then grant full trust permissions. To do this, open the Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rssrvpolicy.config file, and then add the following code.
Description="Code group for my data processing extension">
Note Custom assemblies often work fine in Report Designer. However, you may find that when you deploy and then try to run the custom assembly in Report Server, the default Execution level permissions are insufficient. The reason for this is, by default, Report Designer runs custom assemblies with "FullTrust" permissions. However, when you deploy your reports to Report Server, the default permission that is granted in Report Server is set to Execution level. If this problem occurs, you will most likely see "#Error" in the report control instead of expected results from the custom assembly.
Embedded codeEmbedded code is code that is written in the Code section of the Report Properties dialog box. Embedded code is a good choice for code that will be called several times within your report. If you want to reuse code in multiple reports, a custom assembly is probably a better choice. To create an embedded function, follow these steps:
- On the Report menu, click Report Properties.
- In the Report Properties dialog box, click the Code tab
- Add the following function, and then click OK.
Public Function EmbeddedFunction() as String
Return "this is from embedded code function"
- In the report, add a new text box.
- Add the following to the Value property.
รหัสบทความ: 920769 - การตรวจสอบครั้งสุดท้าย: 25 ม.ค. 2007 - ฉบับแก้ไข: 1