How To Implement Multi-Lingual (Localized) Reports In SSRS 2008

As part of my project execution , I had to work on the multi-langauge reports. I literally spent more than 4 hours to find out the information required for my project. Still I could get only bits and pieces of information not  an end-end post on multi-language reports. This situation is inspired me to write a post on this topic.

This post describes the end-end way of implementing multi language reports in the SQL Server 2008 environment. any experienced techie if he follows the example given in this article will be able to create multi-langauge reports in his project. Multi-language reports are also called localized reports in the programming context.

Architectural Process behind  Localization

These reports can support different languages based on the resource files. Resource file is a text based file where we have different strings in english and also their localized meaning in the local language. we need to create one resource file for one langauge. One particular resource file will be loaded by the .NET CLR in to the SSRS 2008 Reporting Processor depends upon the browser langauge of the Report Manager. Reporting Server programming extension acts as the through channel in between .NET CLR and Reporting processor in this communication process.

Sample Multi – Lingual Report In SSRS 2008

Please follow the below example to implement the multi-lingual reports in SSRS 2008.

Go to Start-> SQL Server 2008 -> SQL Server Business intelligence Development

Create the new project in the Report Server project Template. Give the name SampleMultiLingualReport  , save the template.

 

In the solution explorer right click the Reports folder and add new item.

Select the Report from the Add New Item box. Give the name SampleReport then click the button Add.

Drag the textbox from the toolbox and drop into the Report Area.

1.      Create the Localization Code (Class Library) in C# or VB.Net in Visual Studio 2008

1.         Open Visual Studio 2008 to create resource files for en-US, en-UK and fr-FR for the English, USA and English, UK and French, France respectively (you can create the same files with either text files as well).

2.         Using Visual Studio Create a new Class Library (VB or C#) I am going to name my Project SSRSAssembly

3.        Rename your Class1.VB or Class1.CS to LocalizedReport, presumably you would have your re-usable methods in this class

4.        Add the following Method to your Class

C#

Public StringGetLocalizedStrings(String strCultureInfo, String strLocalString)

{

// Your C# logic should come here

// Return Localized String

}

5.       Build your project.

2.     Copy the Custom Assembly to the SQL Reporting Services Folders

Once the C# application has been compiled it will generate separate DLL for each resource file in the application Bin directory with the folder names en-US, en-GB and fr-FR.  It also generates the DLL for the Visual Studio project called ssrsAssembly.DLL

Copy en-US, en-GB, fr-FR folders into the below path in your system.

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies.

Copy the project ssrsAssembly.DLL into the below mentioned paths in your System.

.Net Path: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

Report Server Path:  C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin

3.    Add the DLL File to your Reporting Application

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. On the Report menu, click Report Properties.
3. In the Report Properties dialog box, click the References tab.
4. Under References, click the ellipsis (…) button that is next to the Assembly name column header.
5. 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 2008, click Add instead of Open.)
6. In the Add References dialog box, click OK.
7. In the Report Properties dialog box, click OK.

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

4.   Call the C# Code into the Report

Now the Report will be able to refer the code in the C# that we have written in the .Net , as we have embedded that DLL in to our Report.

1.Right click the textbox that we have dropped in to our report earlier. Click the menu item Expression from the Menu.

2. Expression editor will be opened. Write the below code in the editor.

=CODE.SSRSAssembly.LocalizedReport.GetLocalizedStrings(User!Language,”Hello”)

Explanation of Above Code

CODE – This is the standard keyword pre defined by SSRS to call the custom assembly Function

SSRSAssembly – This is the name of our C# application name space

LocalizedReport – This is the name of our class library

GetLocalizedStrings – This is the name of our method defined in C# to get the localized strings.

User!Language – This is the built-in variable from SSRS. It defined the browser langauge of the Report.

Hello – it can be any string for which we need a localized meaning. This string should be defined in the all resource files with their corresponding localized meaning.

5.   Update the rssvPolicy.config File in the Report Server  

Open rssvPolicy.config located in C:\Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Report Server\bin

Add the below <CodeGroup> after the last <CodeGroup> in the rssvPolicy.config file.

<CodeGroup version=”1″ PermissionSetName=”FullTrust” Description=”reportHelperLib. “>

<IMembershipCondition

Class=”UrlMembershipCondition”

Version=”1″

Url=

“C:\ProgramFiles\MicrosoftSQLServer\MSRS10.MSSQLSERVER\ReportingServices    \ReportServer\bin” />

 </CodeGroup>

6.   Restart the Reporting Services Service

Go to the Control->Administrative Tools -> Services.

Find the SQL Server Reporting services and Restart the Services of SSRS

7.   View Report in the Report Manager

Compile the report, deploy the report onto the report manager and execute the report in different language options by setting language mode at IE->Tools->Appearance->Languages tab of the Internet Explorer.

Limitations

Let me conclude this post by discussing the limitations of localized reports in SQL BI 2008. We can define the localized string for any report item like Report Data,Report Headers, Report Column etc. Only drawback of this feature is we can not apply this logic to the Report Parameters. If you want the Report Parameter also in diffrent languages like the report data it is not possible.

Since Report parametrs can not be defined as the Expressions in SSRS, we can not open the Expression Editor to call the C# code into the Report Parameter definition. Report Parameter always contains static text value.

 

please give your feedback / comments on this topic.

if you need any further help on this please mail me at VK.Sangisetti@GMail.com.