Report service中的自定义程序集和嵌入式代码调用方法

How to use custom assemblies or embedded code in Reporting Services

View products that this article applies to.
SQL Server Support Voice Column

How to use custom assemblies or embedded code in Reporting Services

To 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 (http://support.microsoft.com:80/common/survey.aspx?scid=sw;en;1301&p0=&p1=&p2=&p3=&p4=) form. There's also a link to the form at the bottom of this column.
 

On This Page

 
Welcome back! I am Sandy Yakob with the Microsoft SQL Server Content team. I am your hostess for the SQL Server Support Voice columns. A quick note about me: I have been with Microsoft for 14 years. For the past three years, I have been working with the SQL Server Content team.

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.
Back to the top

Create a custom assembly

To create a custom assembly, follow these steps:
  1. Create a Visual Studio class library. On the File menu, point to New, point to Project, and then click Class Library.
  2. Specify the name and the location. For example, I used SimpleClassLibrary and C:\Documents and Settings\user1\My Documents\Visual Studio 2005\projects.
  3. 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.

    NoteBecause 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"
    End Function

End Class
  4. 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.
Back to the top

Copy the custom assembly to the SQL Reporting Services folders

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

NoteThe 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
NoteBecause you have to do this each time you change the code, it can be a bit tedious. Many developers create batch files to handle this task. Here is a sample batch file you can use. 
@ECHO OFF
REM   Name: SRSDeploy.bat
REM
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
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"





I usually handle this task by creating a custom post-build event in my project. To do this, go to the properties of your project. Next, click the Compile tab, and then click Build Events. This opens the Build Events dialog box. Choose Post-build event command line, and then type the following:

copy "$(TargetPath)" "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\"
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\"

This uses a post-build macro to specify the location of my assembly. For more information on creating post-build events, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/42x5kfw4.aspx (http://msdn2.microsoft.com/en-us/library/42x5kfw4.aspx) 


Back to the top


Adding a reference to the custom assembly in Reporting Services

To add a reference to your custom assembly, open the Reporting Services report in Report Designer. To do this, follow these steps:
    

  1. Open the report that will reference the custom assembly.
    2. 

  2. On the Report menu, click Report Properties.
    3. 

  3. In the Report Properties dialog box, click the References tab.
    4. 

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

  5. In the Add References dialog box, click Browse. (In SQL Server 2005, click the Browse tab.)
    6. 

  6. Locate and then click the custom assembly. Click Open. (In SQL Server 2005, click Add instead of Open.)
    7. 

  7. In the Add References dialog box, click OK.
    8. 

  8. In the Report Properties dialog box, click OK.
    9. 

We are now ready to use the custom assembly in Reporting Services.
    

  1. Open the report (an .rdl file) in Report Designer.
    2. 

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

  3. In the text box properties, click the Valueproperty, and then call a function by using the following syntax.

    

    

    =ClassLibraryName.ClassName.MethodName or Namespace.ClassName.MethodName
    

    

    
In my example, I specified the following.

    

    

    =SimpleClassLibrary.Class1.DoSomething()
    

    

    
Other formats are used to reference code in Reporting Services. For example, use the following format if you call embedded code.

    

    

    =Code.MethodName()
    

    

    
If you call a non-static, or instance-based, method from within a custom assembly by using an instance, use the following format.

    

    

    =Code.InstanceName.Method
    

    

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



Back to the top


Code access security if the custom assembly requires additional permissions

If 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:

842419  (http://support.microsoft.com/kb/842419/ ) How to grant permissions to a custom assembly that is referenced in a report in Reporting Services

Note The following approach is a test to help determine if code access security issues are occurring. We do not recommend that you use the following approach because it grants full trust permissions to your custom assembly. 

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.








<CodeGroup class="UnionCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="MyCodeGroup"
   Description="Code group for my data processing extension">
      <IMembershipCondition class="UrlMembershipCondition"
         version="1"
         Url="C:\pathtocustomassembly\customassembly.dll"
       />
</CodeGroup>







We recommend that you create a more specific permission set instead of assigning this permission set to your code group. This article is intended to get you up and running with custom assemblies, and this requires more detail. 

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

Back to the top


Embedded code

Embedded code is code that is written in the Code section of the Report Propertiesdialog 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:
    

  1. On the Report menu, click Report Properties.
    2. 

  2. In the Report Properties dialog box, click the Code tab
    3. 

  3. Add the following function, and then click OK.

    

    

    Public Function EmbeddedFunction() as String
 	Return "this is from embedded code function"
End Function

    

    

    

    4. 

  4. In the report, add a new text box.
    5. 

  5. Add the following to the Valueproperty.

    

    

    =code.EmbeddedFunction
    

    

    

    6. 



Back to the top








        

            posted on 
2012-02-21 16:43 
why~ 
阅读(326) 
评论(0) 
 
收藏 
举报

        

    











    
    

    
    

    

        

    

        

            
                
                
            
        

    

    

    

    





	






	
		
		

导航