Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server / SQL Server 2008

Multi Language Reports in SSRS

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
31 Mar 2009CPOL4 min read 54.5K   18   3
Explains how to do 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:

SQL
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.

SQL
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:

SQL
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.

VB
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:

VB
=Code.GetLabel(Parameters!Labels, 105)

And if you wanted the colon:

VB
=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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
https://www.youtube.com/c/Pony80


I am a freelance Business Intelligence consultant, specializing in Microsoft technologies.

I pride myself in solving problems for the business. I understand that my role is to support business and help solve technology and information problems and remove roadblocks.

I have expertise in the telecom, mortgage and travel industries, but am not limited to any industry.

I have 16 years of professional experience, ranging from SQL Server DBA, C# Developer, Business Intelligence, and Software Development Management.

I am now accepting new clients. If you would like to retain my services, please feel free to contact me at bobp1339 at yahoo dot com.

I am also available to mentor new SSRS and SSIS users. I can show them the best practices and quickest ways to get the job done.

Comments and Discussions

 
QuestionMany thanks Pin
Member 1375904922-Dec-20 18:45
Member 1375904922-Dec-20 18:45 
Today (23/12/2020), 11 years after posting it(!), I used it to create a new multilingual report. It worked amazingly! Thank you for that
GeneralSome fields are not returning a label Pin
Outthere11731-Aug-09 9:43
Outthere11731-Aug-09 9:43 
GeneralRe: Some fields are not returning a label Pin
Ponytail Bob31-Aug-09 10:00
Ponytail Bob31-Aug-09 10:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.