Microsoft的Reporting Servcies发布以来，由于其简单易用，功能强大，越来越多的用户选择它来做为报表解决方案。在当今国际化的大趋势下，很多用户在使用Reporting Servcies的时候会遇到一个难题，那就是怎样根据用户的语言偏好显示本地化的信息，比如为中国的用户显示中文的报表标题，为美国的用户显示英文的报表标题；还有如为不同国家的用户显示不同的货币符号等。本文提供了解决这个问题的两个方法． 方法１: Let’s suppose you want to display column names for different users (for example, for Chinese users, display “姓名”; for other users, display “Name”,), you may use expressions to do so as follows: =IIF( User!Language = "zh-CN","姓名","Name") User!Language is a global variable in Reporting Services which will return the user’s language, for English users, it will return “en-US”; for Chinese users, it will return “zh-CN”. For more information regarding User!Language, please refer to MSDN. 方法2: A more flexible but complex way to do so is to use custom assembly in your report. That is, store the localized string in external storage such as SQL Server database or XML files, then write a custom assembly to retrieve the localized string from the external storage based on user’s language, and then call this assembly in your report. The detailed steps are: 1. Create a table to store the localized string: use Northwindgocreate table localizationTbl(sourceStr nvarchar(20), language nvarchar(10), destStr nvarchar(20))goinsert into localizationTbl values(N'Name',N'zh-CN',N'姓名')insert into localizationTbl values(N'Name',N'en-US',N'Name')go 2. Create the custom assembly. Launch Visual Studio .NET 2003, create a Class Library project, and add a static method to retrieve the localized string from the database: using System;using System.Data;using System.Data.SqlClient;using System.Security;using System.Security.Permissions; namespace getLocalString{ public class Class1 { public static string GetLocalizedString(string language, string sourceStr) { SqlClientPermission permission = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted); permission.Assert(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"server = serverName;database=northwind;uid=sa;pwd=xxxx;"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select destStr from localizationTbl where language = '" + language + "' and sourceStr = '" + sourceStr + "'"; cmd.CommandType = CommandType.Text; cmd.Connection = conn; conn.Open(); return cmd.ExecuteScalar().ToString(); } }} 3. Build the project, and then deploy the DLL file to Reporting Services. To deploy the DLL file, copy it from your build location to the report server bin folder and the Report Designer folder. The default location of the bin folder for the report server is C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin. The default location of the Report Designer is C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer. 4. By default, the custom assembly doesn’t have permission to run in Reporting Services, you need to modify the configuration files of Report Designer and report server to grant it the FullTrust Permission. The configuration file for report server is C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\rssrvpolicy.config. The configuration file of report designer is C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\rspreviewpolicy.config. You need to add a code group for your custom assembly similar as follows (please modify the value for the URL attribute based on your DLL file name):
For more information regarding code access security for Reporting Services, please refer to a comprehensive white paper http://msdn.microsoft.com/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp 5. Add assembly reference to the report. To add an assembly reference to a report, on the Report menu, click Report Properties. On the References tab, do the following: in References, click the add (...) button and then select or browse to the assembly from the Add References dialog box. 6. Then you may use expression in the Report to call the static method defined in the assembly as follows: =getLocalString.Class1.GetLocalizedString( User!Language ,"Name")
