Saturday, April 25, 2015

How to develop multilingual SSRS reports (Static)

Multilingual is not a built in feature in SSRS.  We assume that the SSRS report is canned PDF report. The reports can be generated based on specified language. Please see screenshot below
The goal is to figure out a practical, easy but flexible way to develop multilingual SSRS reports. The flexibility means that all translations will be database driven, not hardcoded in reports. This document is focus on all static data, such as titles, labels and notes. Three different approaches are listed below.

Database design
As we want to all translations are database driven, we need to define tables below to store all translations

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)')
Custom code approach for main report
If it is single report, then custom code is the best approach, as it will provide best performance and also easy coding.

Create multi values translation parameters

It is based on translation data set, which is filtered by language

Create custom code to retrieve translation from multi values parameter

Public Function GetTranslation(P as Parameter, Translation as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If InStr(1,P.Value(i), Translation) >0 Then Return P.Label(i)
Next i
Return Translation
End Function

Use expression to get translation

lookup approach for all sub reports
If there are many sub reports, then simple database retrieval is the best approach.
Create a translation data set

Directly use lookup to get translation

Hide and Show approach for major paragraphs
This approach is best for a block when there are paragraphs. The translation could be complicated with different formats.

Align all text boxes with different languages together

Make it conditional show and hide based on language (note: true - hidden)

No comments:

Post a Comment