Tuesday, September 6, 2011

Localization of your SSRS reports. Translations of labels in your reports.

Localization is not a build in feature in SQL Server Reporting Service (SSRS). The user experience is very important for the success of your reports. In a lot of organizations you will see people who are using their applications in different languages. A user can install\configure his windows operating system and MS Office version in the langauage he or she wants. When using SSRS reports a user can't select the language of his reports. The reports will be generated in the language they are made. This is not a good user experience. This blog will explain what you can do to support multiple languages in your SSRS reports.

1) We need a table which will hold all translations.
CREATE TABLE [dbo].[Translations](
     [ID] [int] NOT NULL,
     [Language] [nvarchar] (10) NOT NULL,
     [Description] [nvarchar] (255) NULL,
     CONSTRAINT [PK_Translations] PRIMARY KEY CLUSTERED
     ([ID] ASC,[Language] ASC)
     ) ON [PRIMARY]


2) We need to add translations to this tabe which are needed in the reports.
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'en-US','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000000,'nl-NL','Management')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'en-US','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000001,'nl-NL','Dashboard')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'en-US','Revenue')
INSERT INTO Translations (ID,Language,Description) VALUES (1000002,'nl-NL','Opbrengst')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'en-US','Cost')
INSERT INTO Translations (ID,Language,Description) VALUES (1000003,'nl-NL','Kosten')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'en-US','Result')
INSERT INTO Translations (ID,Language,Description) VALUES (1000004,'nl-NL','Resultaat')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'en-US','Cash')
INSERT INTO Translations (ID,Language,Description) VALUES (1000005,'nl-NL','Liquiditeit')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'en-US','Risk')
INSERT INTO Translations (ID,Language,Description) VALUES (1000006,'nl-NL','Risico')

INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'en-US','Figures are expressed in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000007,'nl-NL','Cijfers worden weergegeven in ')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'en-US','up to')
INSERT INTO Translations (ID,Language,Description) VALUES (1000008,'nl-NL','t/m')
3) Open a new SSRS report in Business Intelligence Development Studio. (BIDS)
4) Create a dataset which will return the set of labels for a given language. Add dataset named 'Labels' using next query:
SELECT ID, Language, Description
FROM Translations
WHERE Language = @Language


5) After adding the dataset 'Labels' a report parameter named 'Language' is added to the report. Configure the default value of the parameter 'Language' to the language ID of the client running the report.


The language ID of the client is configured by the user in Internet Explorer, Internet Options, Tab General, Button Language

6) For testing purposes it is easier to make the parameter 'Language' visible.

7) Create a second dataset with all available languages. This dataset will be used as available values for the parameter. You can use next query:
SELECT DISTINCT(Language) FROM Translations

8) Create a hidden, multi-valued parameter called 'Labels'.
Set the available values to the Labels dataset, value field: ID, label field: Description


Set the Default values to the Labels dataset, value field: ID This is important as you don't have access to tha available values from within the report. When the report is started by the user, the 'Labels' parameter will contain all translation for the selected language.

9) Add a function to the report. This function will find the translation for the selected language based on the specified ID in the report. To add a function to a report, right click on the yellow part of the report. Choose Report Properties, Code

Use next code:

Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function


10) Now we are ready to configure the labels in your report to use this function.
As example for translation of the label: 'Revenue' use next expresion in your label:
=Code.GetLabel(Parameters!Labels,1000002)

11) Next step is to configure the report to use the date and number format to the selected language.
Open the report properties window and configure language with the parameter value of the selected language.
=Parameters!Language.Value



12) Now execute your report. The report will be generated in the configured language of your Internet Explorer.

13) Select another language if you want, in this case nl_NL\
You will see that all labels are translated and the thousand seperator is changed from , to .

Enjoy it to make your reports in the localization of your users to improve the user experience.

24 comments:

Ronald Voets said...

Excellent description of a solution!

Marjan said...
This comment has been removed by the author.
Marjan said...

Thanks for this post. Very helpful.
I'm trying to come up with a solution for SSRS report to be translated to different languages such as Arabic. Then I need to change the format of the fields to be right justified. How can I do that?
Thanks in advance

André van de Graaf said...

Marjan,
this is possible. For every field to which this applies you need to define an expression. Retrieve the text box properties of this field. Select alignment. The horizontal alignment is by default set to Left. Define an expression something like this: =IIf(Parameters!Language.Value = "AR", "Right","Left")

In this case AR is the languagecode op Arabic

André van de Graaf said...

Marjan,
this is possible. For every field to which this applies you need to define an expression. Retrieve the text box properties of this field. Select alignment. The horizontal alignment is by default set to Left. Define an expression something like this: =IIf(Parameters!Language.Value = "AR", "Right","Left")

In this case AR is the languagecode op Arabic

Marjan said...
This comment has been removed by the author.
Marjan said...

oh...I got it...I put it for the textAlign of each field

Marjan said...

Thank you so much Andre for your answer to my question...
now, one more question....how can I flip the position of the labels and data feilds?
In English labels are at left followed by the data on the right side of the label, but in Arabic, it's opposite....Labels on right and data field on left...
Sorry to bug you so much....
thanks in advance

Anthony Green said...

Great post and just what I was looking for, especially for a DBA who doesnt know C or VB like the back of his hand to do this via code and resource files as many links where talking about.

My only question is, would you mind me taking the content and publishing an article on SQLServerCentral in relation to my experience and the helpfull ness of this solution?

André van de Graaf said...

Anthony,

It is not usefull to copy paste content. You can talk about your experiences with translation of labels, but do not copy the content. Please refer to my blogpost. So content is still only on my blog, and it will result for me in more traffic to my blog.

Gr. André

Anthony Green said...

Not a problem, already put the link to the blog in the forum topic in relation to my problem on the site already, so hopefully anyone searching for the topic will find the link to your blog.

Naim said...

Thank you ANDRÉ VAN DE GRAAF. Excellent, very usefull. Searching for the problem and got it. Very nice.

Danny said...

Is there a way to localize the parameter prompts?

André van de Graaf said...

Danny, that's unfortunatley not possible.

DC said...

This is great if everyone uses IE as the autodetection of the culture code matching on the regional settings set on the Windows machine. How about on other operating systems and web browsers? Mac OS, Safari, Firefox, Chrome?

Thanks

DC said...

This is great if everyone uses IE as the autodetection of the culture code matching on the regional settings set on the Windows machine. How about on other operating systems and web browsers? Mac OS, Safari, Firefox, Chrome?

Thanks

Manoj dpossc said...

Thanks for explaining...i have one small question, if there are large values in Parameters, reports gets slower.. is there any way we can quickly get label for its respective value...?. Due to for loop.. reports takes much time to rendered...

André van de Graaf said...

Manoj, you are right, with a big set of translations it take a while to execute the report. This solution is only useful for small amount of translations. In case you have much translations you need to develop and deploy a reporting service extension DLL.

Gr. André

Floris Devriendt said...

While I think your post is informative and brings the point across well, I think it is funny that you mention not to copy & paste content.

This piece of code:
Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function

That can be found on many other blogs. Some dating as far back as 2007 (5 years before you created this post).

The code can be found here:
http://blogs.msdn.com/b/bwelcker/archive/2007/07/11/laser-guided-missiles-report-localization-through-parameters.aspx

or here:

http://markuserlandsson.wordpress.com/2011/08/02/creating-a-report-with-language-support/

Googling the line "Public Function GetLabel(P as Parameter, Label as String) as String" returns 9 results (including yours), 4 four of them at least dating before yours.

So if you want people to refer to your post, please refer to some original posts where you got your inspiration from.

Other than that your post was very informative and provided more visual aid than other blogs.

Floris Devriendt said...
This comment has been removed by the author.
Thiva said...

i have implemented your solution,its working fine in report server and native mode.
but in share point integrated mode,it seems user!language Could not fetch the browser language??
after deploying the report to share point site,fields are not getting translated according to browser language. any solution?

Unknown said...

If the report is exported to PDF or MS Word, is it going to export the translated content?

Unknown said...

In what database would you recommend creating the table holding the translations? If I want to be able to run the report on various databases it's impractical to create tables in the DBs to be analysed. If I want to be able to share the report it's no good me creating it in the Master DB. Is there some way of creating a temp table on the fly with T-SQL embedded in the report?

Benjamin da Silva said...

Well, you could put all data in the dataset's SELECT statement without querying a table, like:

SELECT '001' AS ID,
'en-US' AS Language,
'Costs' AS Description
UNION

SELECT '001' AS ID,
'nl-NL' AS Language,
'Kosten' AS Description
UNION
...

Allthough this can get cumbersome if using a lot of labels and translations.