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


Comments (9)

  1. As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques

  2. rpmendes says:

    Hi bwelcker. Good job!

    I have one question: is there a way to put the code you mention in an external class? I don’t want to copy the code in every report I create… I’ve tried to create a class, but I didn’t find the correct namespace to get the Parameter class…

    If you can help…

    Thanks

  3. My programs are mainly client Windows Forms programs, aiming at the Greek market. Recently i decided

  4. mashmallow says:

    Hi Brian,

    I used the method given in this article and it works perfectly fine on my SSRS report.

    Hence I have encounter a problem to pass the language and label parameter in a drill through report. I wish the report accept the same language parameter & select from available label after drill down report.

    Wonder if you could explain how to extend this function?

    Many thanks =)

  5. Munchoa says:

    This is a very neat way to do this! In fact you are the only and first person ever (according to Google 🙂 who found a way to join datasets in Reporting Services! I did a lot of research on the issue your solution is both simple and working 🙂

  6. Nanda says:

    Hi Brian Welcker,

    Man You are just wonderful and genious, I used above method which solved my problem in just 5 mts instead of spending 2 hrs to find solution.

    Many Thanks

    :-

  7. Great blog post. It works very good. Thanks for sharing.

  8. Swaroop says:

    I Had report data with large text and we have to display in multiple languages depending upon the parameter selected in the report. Can anyone suggest the best way.

  9. metralgia says:

    Very clever!  I used this to implement a multi-metric goal system in some of my reporting.  A stored proc returns a table w/ a row for each metric's goal.  I used the method you outlined here to be able to access those goals in any expression.  Thanks!