Laser Guided Missiles (Report Localization through Parameters)

As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques for localizing reports. One technique is to use the LocID propertie to create a version of the RDL in each language. Another approach is to have a single report and create a custom assembly to load the strings for each label. Here is an relatively easy technique for providing localized reports using hidden parameters.

First, you will need to create a table to hold the translations. The following T-SQL will create the table and add a few labels for translating the Product Line Sales sample that is included with the product.

CREATE TABLE [dbo].[Translations](    [Label] [nvarchar](150) NOT NULL,    [Language] [nvarchar](10) NOT NULL,    [Translation] [nvarchar](150) NOT NULL ) GO INSERT [dbo].[Translations]    VALUES ('Top Stores','fr-fr','Les meilleurs magasins') INSERT [dbo].[Translations]    VALUES ('Top Employees','fr-fr','Les meilleurs employés') INSERT [dbo].[Translations]    VALUES ('Top Stores','en-US','Top Stores') INSERT [dbo].[Translations]    VALUES ('Top Employees','en-US','Top Employees') INSERT [dbo].[Translations]    VALUES ('Top Stores','de-DE','Oberseite Speicher') INSERT [dbo].[Translations]    VALUES ('Top Employees','de-DE','Obere Angestellte') GO

Now, create a query that will return the set of labels for a given language. Add a dataset named 'Labels' that has the following query:

SELECT Label, Language, Translation
FROM Translations
WHERE (Language = @Language)

By default, a new report parameter will be created named Language. If you would like to automatically bind the translations to the user's  language, you can delete this parameter and bind it to User!Language in the dataset properties dialog. However, for testing purposes it is easier to just type it in for preview.

Next, you will need to create a hidden, multi-valued parameter called 'Labels'. Set the Available Values to the Labels dataset, the Value field to 'Labels' and the Label field to 'Translation'. Set the default values to the same dataset and the Value field to 'Labels'. This is important as you don't have access to the available values from within the report, only the actual values. When the user runs the report, the parameter value will contain all of the labels.

Now, add a function to the report (from the code tab of the Report->Report Properties menu item)

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

This function will find the translated label within the supplied multi-valued parameter. If the label is not found, the passed in value is returned. This is important as you may have a user language for which you have not created the translations.

The only thing left is to change the static labels in your report to use this new function. For example, to translate the Top Employees label, use the function

=Code.GetLabel(Parameters!Labels,"Top Employees")

I've included a version of the Product Line Sales report that has all of this working. Enjoy!

Product Line Sales Localized.rdl