Article ID: 920769 - View products that this article applies to.
SQL Server Support Voice Column
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
(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.
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.
Create a custom assemblyTo create a custom assembly, follow these steps:
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.
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\"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:
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:
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:
842419Note 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.
(http://support.microsoft.com/kb/842419/ )How to grant permissions to a custom assembly that is referenced in a report in Reporting Services
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.
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.
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:
I’d like to thank John for his hard work on this article. He has in-depth experience in SQL Server and several Microsoft Development languages and enjoys working with customers and solving difficult issues. Hope you enjoyed this month’s topic! Thanks for reading! As always, feel free to submit ideas on topics you want addressed in future columns or in the Knowledge Base using the Ask For It