Programmatically Localizing Number Formatting in SQL Reporting Services 2005 (SSRS) reports

A reader comments on Formatting custom dates in SQL Reporting Services Report Designer with a great question.  What do you do if you need to format numbers in US format (123,456.00) or German format (123.456,00).

I was curious as to why the decimal formatting differs by country.  I found this Wikipedia article that details the history of decimal separators.  According to this article, use of the period to separate groups of numbers comes from Roman numerals, and because it was clearer in printed communication, but most English speaking countries use commas.  Countries using commas to separate thousands and periods to separate decimals inculde Australia, English Canada, Japan, Korea (both), Malaysia, Singapore, the United Kingdom, and the United States.

In SQL Server Reporting Services 2005 the number formatting is actually set by the language settings.

In Visual Studio 2005, make sure that you have the properties window displayed, and click on the cell with the number that you want to format.

In the language field in the properties window, you can select English (United States) to format the numbers 123,123.00 and German to format the numbers 123.123,00.

This is also what controls the currency symbol used in the field (Euro, Dollar, Yen, etc).

So what if you want to systematically change the number format based on the geographic area reflected in your report?

You can programmatically change this using an expression in the language properties, if there is a value in the report that reflects the country. Select Expression from the drop-down for language, and enter something like this:

=iif(Fields!Country.Value = "US", "English (United States)", "German") 

Note that if your default format is US format, you can also use default in your expression. For example:

=iif(Fields!Country.Value = "US", "default", "German")

Comments

[Anon]

Surely it would be better to just use =User!Language and that would pick up the client locale settings and use them to apply the formatting to the content - no matter what language was used.
This setting can be applied for individual cells or to the entire report!

[Joel Lindstrom]

Great Question,

What you suggest would work if the report is generated for the same geographic location as the user, but consider the following scenario:

A company has sales reps in Europe who send sales quotations to companies in EU and also companies in USA. If you use =User!Language, the currency values will always be in Euros. There is a requirement in these types of situations to dynamically change the currency based on the location of the recipient of the report, not the location of the user. That's what I was describing in the blog post.