Laser Guided Missiles (Report Localization through Parameters)

  • Článek

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

  • Anonymous
    July 11, 2007
    As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques
  • Anonymous
    April 02, 2008
    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
  • Anonymous
    May 05, 2009
    My programs are mainly client Windows Forms programs, aiming at the Greek market. Recently i decided
  • Anonymous
    May 15, 2009
    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 =)
  • Anonymous
    July 01, 2010
    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 :-)
  • Anonymous
    August 11, 2010
    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:-
  • Anonymous
    August 25, 2011
    Great blog post. It works very good. Thanks for sharing.
  • Anonymous
    April 11, 2012
    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.
  • Anonymous
    February 28, 2013
    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!