Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multi Lingual SSRS Reports Expand / Collapse
Author
Message
Posted Monday, August 20, 2012 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
Hi All

Looking for a bit of guidance on a issue with SSRS.

I know SSRS will not natively support translating tablix, labels etc data from what you specify in the RDL to the users language without you having to do some fancy external referencing assembiles which will do it for you.

I'm not C# VB savvy so would say that way is over my head.

Is there anyway you can store the translations in a table, and based on the User!Language and a marker of some sorts, get the data out and manipulate chart labels, report headers, column headers by using expressions.

The data doesnt need to change, its just headers and labels.

Any pointers to useful articles would be appreciated or if anyone has any solutions or an easy eay of doing it in code which a newbie to coding like myself can understand, would again be appreciated.

Thanks




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347195
Posted Tuesday, August 21, 2012 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
One way so far I have looked at is using SWITCH for the labels etc based on User!Language, but seems like a pain to maintain having to modify the reports and redeploy for a new language or a change to a translation, so still looking for ideas about populating labels from data stored in the DB instead of using SWITCH of IIF if possible.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347672
Posted Tuesday, August 21, 2012 4:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 1,974, Visits: 3,334
Hi Anthony,
Will this be for a set limited number of languages, or does it need to handle pretty much any language?

Cheers
Post #1347674
Posted Tuesday, August 21, 2012 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
For the moment, it will be a small set of languages. The reports are for our new SaaS product and could be sold in every country in the world, but at the moment we are looking at Mexican Spanish and English as pilots and then it could take off and be used world wide and need to handle every language.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347677
Posted Tuesday, August 21, 2012 4:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 1,974, Visits: 3,334
Ok, how about this:

Table containing location ID's (en-GB, es, es-MX etc) as PK, columns Header1, Header2.. HeaderN.., Label1 etc (obviously with better names - more on design in a bit)
and the rows containing the translated versions.

Create a dataset Labels in the report, as select header1, header2... from Labels where LocationId = @LocID
Set the available & default values for report parameter @LocID as =User!Language, and set it as a hidden parameter.

Then for everywhere where required, use =First(Fields!HeaderName.Value, "Labels"), where HeaderName is the desired header/label.

Design of the underlying table could be improved by having 3 tables - Location, Header and a link table containing the translated value.
Location table could have another field MasterLocationId which self-references where you don't need extra translations for different languages.

Wrap it all in a view/proc for the dataset to read from.

Hope that makes sense - all completely untested of course!
Think it'll be ok in headers, gets a bit muddier when you start with chart labels etc.

Cheers
Post #1347696
Posted Tuesday, August 21, 2012 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
http://www.keepitsimpleandfast.com/2011/09/localization-of-your-ssrs-reports.html

Going to try this out




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347698
Posted Tuesday, August 21, 2012 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
Gazareth (8/21/2012)
Ok, how about this:

Table containing location ID's (en-GB, es, es-MX etc) as PK, columns Header1, Header2.. HeaderN.., Label1 etc (obviously with better names - more on design in a bit)
and the rows containing the translated versions.

Create a dataset Labels in the report, as select header1, header2... from Labels where LocationId = @LocID
Set the available & default values for report parameter @LocID as =User!Language, and set it as a hidden parameter.

Then for everywhere where required, use =First(Fields!HeaderName.Value, "Labels"), where HeaderName is the desired header/label.

Design of the underlying table could be improved by having 3 tables - Location, Header and a link table containing the translated value.
Location table could have another field MasterLocationId which self-references where you don't need extra translations for different languages.

Wrap it all in a view/proc for the dataset to read from.

Hope that makes sense - all completely untested of course!
Think it'll be ok in headers, gets a bit muddier when you start with chart labels etc.

Cheers


Sounds like an option if the above doesnt work out.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347699
Posted Tuesday, August 21, 2012 5:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 1,974, Visits: 3,334
Ha, they look fairly similar!
Your linked method does have the advantage of taking care of other localisation issues like date and number formats; which mine doesn't.
Post #1347703
Posted Tuesday, August 21, 2012 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
Boom - it works, sweet as a nut

Also helps if you read the article in detail aswell as I missed a critical part on one step.

Only thing it doesnt translate is the month names on the axis, but that will be because I am getting them as the English from the DB using DATENAME but that wont be a big thing to change, all the other data in the DB is inputted by user so stored in locale specific, was just the labels and other none database driven chart labels etc which where needed.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1347712
Posted Tuesday, August 21, 2012 7:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 1,974, Visits: 3,334
Cool, good stuff.
Post #1347766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse