Happiness is automatic SQL Reporting Services localization



Many people understand that you can leverage User!Language in order to grab the regional / language settings of a user, then use this information to localize your report data & label text. If you don’t, no worries. Check this topic out:


http://msdn2.microsoft.com/en-us/library/ms156493.aspx


While leveraging User!Language certainly will work in terms of allowing you localize your reports, doing so is admittedly a pain in the tail – you essentially have to replace all your label text with expressions that call into code that you write to do “label localization” ala:


= MyNameSpace.MyConvertingClass.MyConverter(“someLabelName”, User!Language)


Your code takes the name of a label and the language to use, then goes off to a resource file to pull and return the correct string.


Localizing your data is less painful, you just store your “description text” in multiple languages (see the Product.ProductDescription table in AdventureWorks to see what I mean) in a table and use a parameterized query to yank the correct record based on User!Language.


Leveraging  the SQL Analysis Services Translations feature makes all this a thing of the past, however. Assuming that your data is moving through a cube, you can use this great feature to localize all your text, including captions, data, etc: See the AdventureWorks DW OLAP sample in C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise\ . Once you get it open, click the Translation tab and explore a bit.


Anyway, if you have translations in place, all you have to do is tell SSRS to use ‘em, and your life is good. To do so, utilize the LocaleIdentifier connection string property of ADOMD.NET 9.0 to set a locale (like 1036 for France). See the attached (very simple) RDL document which utilizes the Adventure Works DW cube.


In Excel 2007, you get this behavior “for free”, but in SSRS you must add the LocaleIdentifier connection string property yourself, and give it a numerical value (1033, 1036, etc.) Here’s the part which makes it all work – you use an expression-based connection string to sense the user’s language (in this case “Are you French? If not, you’re getting English”) and feed in the correct LocaleIdentifier:


[ImageAttachment]


 



 (Looks like I can’t post two files at the same time…go to the “Here’s the RDL” page if you really want to see the RDL…I’ll post it there.(

Report2.jpg

Comments (11)

  1. Vick says:

    Nice Post.I have a question with regard to changing the Label text.I am using multiple resource files but not in different langauges.How can I use these resiource files to do reporting.The resource files determine the UI of the webform and the reports are generated directly from the webforms.so the reports should follow the webform control text whihc are determined by the resources.

  2. Vic, I’m not 100% sure I understand your question, but the custom code that you write will basically have to determine which resource files to use using whatever logic you choose to, then load/interrogate them. In  localization land, we’d rely on User!Language to be the "decider", but in your case, you’ll have to key on something else (what it is, I don’t know).

  3. Vick says:

    Thanks for the response.Yes I understand that I need to write custome code to access the LabelText from the resource file but I have no clue as to how to go about it.

  4. Examples here:http://www.java2s.com/Code/CSharp/Development-Class/Resource-ResX.htm. Your labels will call the custom code you write like this: =Code.MyFunction(User!Langauge)

  5. George says:

    Russ,

    Is there a way to make use of resource files with Analysis Services to localize my reports.  Our process requires that we separate localization from cube development itself. So we need a way to merge the translation in or make use of translation files.

  6. Hey George. I’m pretty sure the answer is "no". I’ll confirm with those smarter than I and re-post if you CAN do this.

  7. tosh says:

    Looks like we have to create the reports per language. Bum 🙁

  8. Jun says:

    Does SQL reporting Services support Hebrew (Right to Left) languages?  will the report display properly, i.e. R-to-L?

    thanks,

  9. Q says:

    Is there any way of picking up what timezone a user is in? We have a database storing time in GMT and would like to convert it for them without needing to prompt for an off set. They would be using report manager to view the reports.

  10. Sandy says:

    my implementation if multinational.  Data input is mixed languages, chinese and english.  The folks in china when they run reports with ssrs get back the mixed language and it displays correctly all the characters.  When I run reports I get the english characters okay, but the chinese characters don’t display as chinese, instead they display as a mix of ascii, including things like control characters…  I am guessing that the data is stored correctly as unicode, but that my local machine is not using the correct code page.  Can you tell me where in the configuration for ssrs I fix this?

  11. Swaroop says:

    I Had report data with large text and we have to display in multiple languages depending upon the parameter selected in the report. Can anyone suggest the best way.