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

16 09 2009

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.

 1

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.

2

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

3

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.

About these ads

Actions

Information

8 responses

21 04 2010
matt

Good Article, and works like charm.. :).. but would like to know how we use the actual .resx file for each culture with out the satellite assembly

21 04 2010
VK Sangisetti

Hello,

Thanks for visiitng my blog, there are two ways to incorporate cultured files. one is by creating satilite assembly and another one is without satilite assembly. if we create satilite assembly we can crate strong name and put it into the GAC if its required. otherway is non-satilite and that is non-compiled version.

7 04 2011
clare moore

I have tried to implement your solution but its not working.

I get CODE does not exist? Then when i implement some custom code in the report, i get SSRSAssembly is not a member of ReportExprHostImpl.CustomCodeProxy

Is there a step missing??

24 05 2011
Ranga

Hi
Its nice code. I need to know whether its neccessary to place assembly in the GAC. Is it any roundabout without installing in the GAC since we dont have access to GAC and the CAS is been configured for the medium access policy.

20 12 2011
Akash

Hi,
Can anyone please tell me, is it possible to use a silverlight assembly as custom assembly here, also is it required to put the dll in GAC ??

5 01 2012
Stamati Crook

I wondered if you have come across a solution for formatting numbers and dates. The report language does not default to the User!Language and I cannot find out how to set the parameter in SSRS 2008 in code (it seems you can do it with 2005). So you cannot format dates and numbers using the regional settings as they are set to the server locale and not the user locale.

2 05 2013
Duane

Exceptional blog and great design.

30 01 2014
Janko (@Janko)

Things I made differently in order for this to work:

–I had to put a lower .NET version (3.5) for the LocalizedReport project

–The class GetLocalizedStrings had to be static:

public static string GetLocalizedStrings(String strCultureInfo, String strLocalString)
{
// Your C# logic should come here
// Return Localized String
}

–rssvPolicy.config is not in Bin but in one folder up

–removed CODE from the expression. This didn’t work:
=CODE.SSRSAssembly.LocalizedReport.GetLocalizedStrings(User!Language,”Hello”)
and this worked:
=SSRSAssembly.LocalizedReport.GetLocalizedStrings(User!Language,”Hello”)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: