Thursday, October 11, 2007

Multi-Language Tips in SSRS

In looking through the forums at Microsoft, I often see questions pertaining to the formats of dates, number and currency in a report.

In my latest gig with a multinational travel management company, I have had a lot of exposure to the multi-language/culture formatting.

I will cover multiple languages in a future blog, and today focus on the formatting of dates, numbers, and currency values.

To start with, Microsoft has a good page explaining each of the terms used by them to customize the user interface to the user’s regional setting:

http://www.microsoft.com/globaldev/getwr/steps/wrg_lclmdl.mspx


SSRS handles formatting the display of dates, numbers and currency by adding a property to a report and textbox: Language. This is misleading since it really does not change the language except on the verbose date formats. For example, changing a number from the US format: 1,234.56 to a French format: 1 234,56 really isn’t a language change, just a locale change, according to the link above.

The language property displays the locale description, and behind that, the locale code.
For example, the property displays English (United States), and the value behind that is en-US.

I found a good list of these locale codes here:

http://msdn2.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx

I put these into a database table, so that I can use them as a parameter in my reports.

In the reports that I create, I have a dataset called Locales in which I call a stored procedure that returns the locale code and the description, sorted by description.

I then create a parameter called Locale. This is a String parameter that is setup to read available values from the Locales dataset, with the code as the value and the description as the label.

Depending on the report target audience, I set a non-queried default value, usually en-US.

In the report properties window (NOT the properties that show up when you select the menu items: Report, Properties. You have to click on the outlying yellowish area of the report designer and press F4), I set the language as an expression: =Parameters!Locale.Value

Now you should format all of the textboxes that contain dates, numbers and currencies.

You should use the standard formatting strings rather than hardcoding a format into the format property. For example, for a short date format, use d rather than d/M/yy, as this will always format the date d/M/yy no matter what you select for the language property.

Here are some standard formatting codes:

Date/Time: http://msdn2.microsoft.com/en-us/library/az4se3k1(VS.71).aspx
Numeric: http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.71).aspx

Note that some of the formatting strings are “InvariantCulture.” This means that they will not be affected by the language property.

NOTE: When changing locales on currency fields, all this does is change the formatting of the displayed number. It does NOT do any currency conversion. For this reason, I usually do not format currencies as currency, but as a N2 (Numeric, 2 decimal places) and then display the ISO currency code (USD, EUR, JPY, etc) to indicate which currency is being displayed. I also use a separate parameter to select the currency based on this ISO code. Since the ISO currency codes and the Microsoft locale list do not tie in any way, it makes it a lot more difficult to use the language properties to format currency changes.

The way parameters are displayed is controlled by the browser regional settings. There is no way in reporting services to control that. However, the regional settings on a browser should be set to the way the user likes to see the dates, numbers, etc, so you should not have to worry about this setting.

Well, that wraps up this tip. I hope that someone out there benefits from this. I have done a lot of research, as well as a lot of work on my own, to put this together. I use this extensively on existing reports and it works very well.

Let me know what you think!

Peace…

5 comments:

Saba said...

Hi, I really liked your article. I'm trying to setup the date format so the result will come as Feb 09 2010. I have tried different dd/mm/yy but i don't know how to get the month in the word form. please advice.
Thanks

Bob Pearson said...

To get Feb 09 2010, you can use this format: MMM dd yyyy

peace

Lars said...

Hi. Great write-up.

Have You found a way to solve multilanguage also for the parameter captions? I wouldn't think so, but I just had to ask :-)

Thank's anyway!

Bob Pearson said...

No, you have to upgrade to SSRS2008 to get that functionality.

Anonymous said...

Thank you for this great tip. I took me whole day playing with all settings I found all over SSRS - no idea why formatting is different between development studio and browser output. Even more confusion put the fact, that after export to excel, desired formatting miracleously appeared. After reading your article, all is clear and formating works like a charm. Once again thx!