Tuesday, October 30, 2007

Multi Language Reports

Well, I promised to write about handling multiple languages in SSRS so I thought I would at least get started on that. Judging from the visitor stats, that seems to be what a lot of people are looking for.

As you may already be aware, SSRS does not inherently support multiple languages. And what I am about to demonstrate does not apply to the data, just the labels and text that are pre-populated on a report.

What I do is use database driven report text. I populate a parameter with the label text, and then use code to retrieve the label. Since a parameter is nothing more than a key/value list, this works quite well.

First, I create a master Label table. This will be the list of labels that I will have available to me. I have another table called Language. This table obviously holds my list of available languages. And I have a third table called LabelText. This table holds the actual text in the different languages that will be placed on the report.

Here is the script:

CREATE TABLE [dbo].[Label](
[LabelID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](900) NOT NULL)

CREATE TABLE [dbo].[Language](
[LanguageID] [int] IDENTITY(1,1) NOT NULL,
[Language] [varchar](100) NOT NULL,
[Active] [bit] NOT NULL)

CREATE TABLE [dbo].[LabelText](
[LanguageID] [int] NOT NULL,
[LabelID] [int] NOT NULL,
[LabelText] [nvarchar](max) NOT NULL)

I set the LabelText up as NVARCHAR to support Unicode character sets.

Then I populate the Label table with all of the labels that I will need on the reports. Here is a short example:

71 % Total
75 Account
120 Address 1
121 Address 2
122 Address 3

I also populate the Language table with the languages that will be available:

1 English 1
2 Spanish 0

The LabelText table really does all of the work. That contains the label text in the language of choice.

I am fortunate enough to have a very good background as a software developer, so I created a web page to populate these two tables and the LabelText table. I let the business unit populate this table since my grasp of other languages is not the best.

Once those are set up and populated, it’s time to turn to the report design.

Create a dataset called Languages. Use it to retrieve the rows in the Language table.

Select LanguageID,
From Language
Where Active = 1

Since populating all of the labels in a new language may take some time, we only want to load languages that are set to Active. This gives the business unit time to load the text, without giving the user the ability to select that language on a report.

Create a parameter called Language. Populate it with the values from the Languages dataset. Pick a default if desired.

Now create a dataset called Labels. This dataset is going to pull back the rows from label text for the selected language:

Select LabelID,
From LabelText (NOLOCK)
Where LanguageID = @Language


Select LabelID,
from LabelText lt (NOLOCK)
Where LanguageID = 1
and Not Exists ( Select 1
From LabelText z (NOLOCK)
Where LanguageID = @Language
and z.LabelID = lt.LabelID)

This SQL pulls back all of the labels for the language selected, and then, for any labels missing in that language, unions the labels in language 1 (In my case, ID 1 is English)
This way you always have a label returned.

Go back to the report parameter screen and create a new parameter called Labels. This parameter should be internal, multi-value, and an integer.

For the available values, populate from the Labels dataset, and do the same for the default values. This gives us a parameter filled with the label ID and text, with all of them selected by default. Since inside a report, you can only “see” selected parameter values, and not the populated values, this is an important step.

In the report properties window, under the code tab, add code similar to this to loop through the selected values in the Labels parameter, and return the text for the ID passed in. Since this parameter is loaded with the translated version, this is what will populate your labels.

Public Function GetLabel(Parm as Parameter, LabelID as Integer, Optional AddColon as Boolean=False) as String

Dim t as Integer

For t = 0 to Ubound(Parm.Value)
If (Parm.Value(t) = LabelID) Then
if AddColon = true then
Return Parm.Label(t) & ":"
Return Parm.Label(t)
end if
End if
Next t

Return ""

End Function

Notice the AddColon parameter. I put this in because I have a lot of fields in my reports that are setup like a data entry form: Label: Value. Since I didn’t want to type +”:” in every textbox, I just added it to the routine. You can add as much custom code in here as you want. This will make your job easier in the long run.

The last step is to use your label table as a lookup (I usually load it into Excel and sort it alphabetically) and add this formula to every label textbox:

=Code.GetLabel(Parameters!Labels, 105)

And if you wanted the colon:

=Code.GetLabel(Parameters!Labels,2, True)

And that’s it! Now you have multi-language report labels. On the downside, it makes the report harder to maintain, but if the requirement is multi-language, this works very well.

Keep in mind; this will not work on parameter labels. I have not yet found a way to translate these. Probably the only way to do it is to have a custom front end, and create your own parameter display using a combination of your own code and the SSRS web services.

Many thanks to Brian Welcker for the initial idea of using a parameter for the labels. (http://blogs.msdn.com/bwelcker/archive/2007/07/11/laser-guided-missiles-report-localization-through-parameters.aspx)
I was orignally calling a C# assembly to populate each label from the database. That generated way too much database activity.



Sam said...

Hi Bob,

This is a very good example! Do you mind to post a simple RDL sample?

Thanks =)

Bob Pearson said...

I will try to put something together over the next few days.


Floris Devriendt said...

I know this is a very late comment, but I believe there is a small mistake in your query:

Select LabelID,
From LabelText (NOLOCK)
Where LanguageID = @Language


Select LabelID,
from LabelText lt (NOLOCK)
Where LanguageID = 1
and Not Exists ( Select 1
From LabelText z (NOLOCK)
Where LanguageID = @LanguageID
and z.LabelID = lt.LabelID)

The very last "Where"-clause should be: Where LanguageID = @Language and z.LabelID = lt.LabelID

In other words, the parameter on which you compare should be @Language instead of @LanguageID.

Bob Pearson said...

Thank you! Yes, that was a mistake. I have corrected it.
I am glad people are still reading these posts 6 years later... :)

Floris Devriendt said...

I'm glad you took the time to create the post. It helped me in my work. So thank you ;)