Saturday, May 30, 2015

How to develop multilingual SSRS reports (Dynamic)

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,

CREATE TABLE [dbo].[Translation](
[Report] [varchar](50) NOT NULL,
[LanguageID] [varchar](50) NOT NULL,
[TextID] [varchar](50) NOT NULL,
[Translation] [nvarchar](255) NOT NULL,
[Report] ASC,
[LanguageID] ASC,
[TextID] ASC,
[Translation] ASC

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

,('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)')
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)
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
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,
      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.

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"
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"

No comments:

Post a Comment