How to develop multilingual SSRS reports (Dynamic)

Check list of all posts

Context

Multilingual is not a built in feature in SSRS.  We have discussed about translation of all static data, such as title, label and notes.   However, there are other sets of translation, which is dynamic , based on report nature, such as report generated month, months as x-axis and product names.

Normally, the approach to resolve this problem is to add all language columns to date dimension table, or product table, then use case statement to select language column based on specified parameters. (BTW, Cognos has a built in prompt macro to handle it automatically in Framework manager.)  This approach is good when there is massive product list, or report on different granularity in term of date. However, its disadvantage is that table structure needs to be changed when add new language.

This document is intended to offer another concept to handle all these dynamic translation. The idea is turn dynamic translation into static text translation. This approach is easy to maintain and develop.

Database design

We leverage the same table structure for dynamic translation

CREATE TABLE [dbo].[Language](

[ID] [varchar](50) NOT NULL,

[Description] [varchar](50) NOT NULL,

CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED

(

[ID] ASC

)

)

GO

CREATE TABLE [dbo].[Translation](

[Report] [varchar](50) NOT NULL,

[LanguageID] [varchar](50) NOT NULL,

[TextID] [varchar](50) NOT NULL,

[Translation] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_Translation] PRIMARY KEY CLUSTERED

(

[Report] ASC,

[LanguageID] ASC,

[TextID] ASC,

[Translation] ASC

)

)

Go

ALTER TABLE [dbo].[Translation] WITH CHECK ADD CONSTRAINT [FK_Translation_Language] FOREIGN KEY([LanguageID])

REFERENCES [dbo].[Language] ([ID])

Samples:

,('ReportTypeX','en','Treatment Count','Treatment Count')
,('ReportTypeX','fr','Treatment Count','Nombre de traitements')
,('ReportTypeX','de','Treatment Count','Die Behandlung Graf')
,('ReportTypeX','zh','Treatment Count',N'治疗次数')

,('ReportTypeX','en','Total Downtime (h)','Total Downtime (h)')
,('ReportTypeX','fr','Total Downtime (h)','Les temps d''arrêt total (h)')
,('ReportTypeX','de','Total Downtime (h)','Gesamtausfallzeit (h)')
,('ReportTypeX','zh','Total Downtime (h)',N'总停机时间（H）')

,('ReportTypeX','en','Runtime','Runtime')
,('ReportTypeX','fr','Runtime','Runtime')
,('ReportTypeX','de','Runtime','Laufzeit')
,('ReportTypeX','zh','Runtime',N'运行')

Besides, we have define a function to get translation, which can be used all report SQL statement.

CREATE FUNCTION [dbo].[ufnGetTranslation] ( 
@TextID varchar(100),
@Language varchar(2)  ='en',
@IsMonthShortName int =1 )
RETURNS nvarchar(100)
AS
BEGIN
declare @ret nvarchar(100)
if( @IsMonthShortName = 1 and @TextID = 'May' and @Language ='zh' )  return '5'
set @ret = @TextID
SELECT  @ret = Translation
FROM    Translation
WHERE   (LanguageID = @Language) AND TextID = @TextID and (Report = 'ReportTypeX')
return   @ret
END

Use scalar function ufnGetTranslation in any SQL statements

As this is a scalar function, it is very easy to be embedded in SQL statement. In this case, we don’t need to change any mapping.  Sample below

SELECT dbo.ufnGetTranslation(DimDate.MonthShortName,@Language,1) as MonthShortName,

      DimDate.YearMonthNumber,

      differnet measures......

FROM   ......

WHERE  ......

GROUP BY DimDate.MonthShortName, DimDate.YearMonthNumber

SELECT dbo.ufnGetTranslation(DimProduct. ProductName,@Language,1) as ProducttName,

      differnet measures......

FROM   ......

WHERE  ......

GROUP BY DimProuct.Product Name

Use lookup function to control current month

There are many cases where you need to show as of report month based on prompt.

Before

Switch (
 Right(Str(Parameters!ReportMonth.Value),2) = "01","January"
,Right(Str(Parameters!ReportMonth.Value),2) = "02","February"
……..
,Right(Str(Parameters!ReportMonth.Value),2) = "12","December"
)

After

Switch (
 Right(Str(Parameters!ReportMonth.Value),2) = "01",Lookup("January",Fields!TextID.Value, Fields!Translation.Value,"Translation")
,Right(Str(Parameters!ReportMonth.Value),2) = "02",Lookup("February",Fields!TextID.Value, Fields!Translation.Value,"Translation")
…..
,Right(Str(Parameters!ReportMonth.Value),2) = "12",Lookup("December",Fields!TextID.Value, Fields!Translation.Value,"Translation")
)

Use reversed lookup function to control color consistence

Please see sample below, we want to make sure color will be consistence among pie chart, tablix and area chart.

Originally, we can make color consistence based on script below (Please see other blog for detail)

Switch (

Fields!Kind.Value = "Max Life","CornflowerBlue",

Fields!Kind.Value = "Min Life","Blue",

Fields!Kind.Value = "Clotted","LightBlue",

Fields!Kind.Value = "Degraded","DarkBlue",

Fields!Kind.Value = "Other","SlateBlue"

)

The control won’t work anymore with translations, as Fields!Kind.Value becomes translated value from ufnGetTranslation.

The simplest solution is use reversed lookup function to get original English value from translation as below

Switch (
Lookup(Fields!Kind.Value,Fields!Translation.Value,Fields!TextID.Value, "Translation") = "Max Life","CornflowerBlue",
Lookup(Fields!Kind.Value,Fields!Translation.Value,Fields!TextID.Value, "Translation") = "Min Life","Blue",
Lookup(Fields!Kind.Value,Fields!Translation.Value,Fields!TextID.Value, "Translation") = "Clotted","LightBlue",
Lookup(Fields!Kind.Value,Fields!Translation.Value,Fields!TextID.Value, "Translation") = "Degraded","DarkBlue",
Lookup(Fields!Kind.Value,Fields!Translation.Value,Fields!TextID.Value, "Translation") = "Other","SlateBlue"
)