Multi-language reports in SSRS (SQL Server Reporting Services)

Introduction

In this world of globalization, everyone wants to have projects, reports etc being accessed by users across countries/cultures around the world. And thus one needs to support SSRS reports in multiple languages.
Different-2 applications provide multi-lingual support in different-2 manners. Though, SSRS does not have inbuilt support for this, we can achieve it using some code based technique.

Background

Since, in general report contains numeric data which are to be analyzed based upon some textual headers/groups. So to actually say that a report has multi-lingual support, all that textual information should be dynamically changing as per the culture/language of end user.
And since these fields basically are the labels/textboxes displaying textual information, we actually have to manage these labels/textboxes for multi-language support.
Also we must have some mechanism by which we can detect the language/UI culture of end user and can present the page/report in an acceptable format.

Solution:

In any way of its implementation, the resource information as what should be displayed in what language /culture, it has to be stored somewhere. This can be maintained in database or in a resource file etc. The structure of the resource storage table would be like:

Field Name	Value	Language
Column1	Total Amount	En-US
Column1	Somme totale	French

Now while displaying this column1 on the report, rather than hard coding its value, at runtime we have to detect the language of interest & display the corresponding value. We can make use of the global variable of SSRS report ‘User!Language’ to get the current language been set in the client’s browser and then pass it to the data table.
This can be done either by using a custom code (DLL/Assembly) in which we can have a function and this function will return the related value of fields.

Function: GetValue(fieldname, language)

Or we can use dynamic query & directly make join to the resource table in our main data set for all the required fields. In this query we can then pass the language retuned by User!Language.

Steps:
1. Create a hidden parameter (CurrentLanguage) of type string.

 

 

 

 

 

 

 

 

 

 

 

 

 

 2. Use expression to assign a non-queried default value to this parameter as =User!Language

3. Use dynamic query for the main data set as:

  SELECT ColumnName, measure1, measure2

FROM MainTable MT JOIN resourceTable RT

ON MT.FieldName = RT.ColumnName (say ColumnName = Column1)

AND RT.Language = @CurrentLanguage

 

Now at runtime @CurrentLanguage will get the language of the client & display the lables & all in the correct language.

  

About these ads