How to develop multilingual SSRS reports (Static)

Check list of all posts

Context

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,

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'运行')

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)