Multi Language Reports in SSRS

Multi-Language Labels

As you may already be aware, SSRS does not inherently support multiple languages. And what I am about to demonstrate does not apply to data, just 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. By setting this up in the database, you can allow your business users to maintain your labels, and not have to make any code or report changes if you need to add a new language!

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. It 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,
Language
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 the label text for the selected language:

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

union

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

This SQL pulls back all of the labels for the language selected, and then, for any label 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 you can only "see" the selected parameter values inside a report, 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) & ":"
      else
         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! I also have this documented on my blog.

You can view my blog here.