Windows Regional Settings and Number Formatting in RDLC Reports


You probably know this blog post from Robert Miller: http://blogs.msdn.com/b/nav/archive/2014/03/25/formatted-decimal-values-dropping-symbols-in-rdlc.aspx. It talks about changing/forcing number formats when Windows is not able to do so. For Switzerland (de-CH), number formats in Windows are not correct regarding the thousands separator. It should be formatted “1’234,56” but is formatted like “1 234,56” (a space instead of an apostrophe). 

Current status

Due to the nature of RDL and the option to specify a language name for each and every single TextBox Control, Report Viewer does not pick specific user changes for decimal or date formats, even if the requested regional setting is equal to the current user’s regional setting. Report Viewer retrieves the default culture settings for a language name, but does not take into account, that this setting (when selected as current/default user format) may have changed the number format.

 

 

So ReportViewer never knows about the current regional settings and the changed symbols when looking up and using the default settings. So for our friends from Switzerland, there is currently only one known option: manually change the thousands separator and re-format the RDLC decimal values. The drawback of this is that every decimal expression has to be changed in all affected reports.

Talking about the world and everything

Due to a talk between buildings K and L in Munich a while ago, we came across localization topics, globalization and ways to force specific locales (and number symbols) for a Windows Forms application. Unfortunately, there is no way, because globalization changes are only supported for web.config, not app.config (see http://msdn.microsoft.com/en-us/library/vstudio/bz9tc508(v=vs.100).aspx). Not talking about different threads and (from my knowledge) no inheritance of culture information to the ReportViewer thread.

But this discussion caused me to start searching the web and I came across custom locales (http://msdn.microsoft.com/en-us/library/windows/desktop/dd317785(v=vs.85).aspx). There is also a tool to create custom locales named Locale Builder (Download at http://www.microsoft.com/en-us/download/details.aspx?id=41158). 

The end is near

With the Locale Builder tool, you can create custom locales with changed number symbols, build an MSI file of it, and the *REPLACE* an existing locale in Windows by assigning the same name to the new locale:

 

The new locale is installed using the MSI file, replaces the default locale (in this case for de-CH), and is marked with an asterisk as a custom locale: 

Now, by default, the correct grouping symbol is used and shown when selecting de-CH, even without changing it manually: 

With this in mind, what do you think is the result when a Report is executed on this system without any change? See fields VK-Preis (Sales Price) or Betrag (Amount). 

 

BANG – Great!

 

Serving some white wine with the fish

However, when printing reports for foreign countries, it always uses the new default format now. Better than before, but not like a world champion would do…

So, in Object Designer, open table 8 Language in design mode and add the following function: 

GetLanguageName(LanguageCode : Code[10]) : Text[10]
CultureInfo := CultureInfo.GetCultureInfo(GetLanguageID(LanguageCode));

EXIT(CultureInfo.Name);

Name         

DataType

Subtype

CultureInfo

DotNet

System.Globalization.CultureInfo.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

Save table 8.

Open report 206 in design mode and add a new column to the DataItem Sales Invoice Header

Language.GetLanguageName(“Language Code”)             Name: “LanguageName”

Now open the report layout for report 206 and bulk-select all TextBox controls in each Tablix. When selected, change the property Language for all controls to =Fields!LanguageName.Value.

Save everything and run report 206 for to show all posted invoices in the database. For all different languages, you should see correct decimal symbols.

Love it? 

 

Carsten Scholling

Microsoft Dynamics Germany
Microsoft Global Business Support (GBS) EMEA

Microsoft Connect: http://connect.microsoft.com
Online Support: http://www.microsoft.com/support

Comments (3)

  1. Jan Veenendaal says:

    Great! – but now for the icing on the cake: how about date formatting?  I've had that discussion a while ago with Microsoft and the conclusion was that date formatting is fixed (e.g. "1. September 2014" for a European country – in most European countries however the dot after the 1 is not usual). Could this solution be used also to get different date formatting for different languages?

  2. Hi Jan,

    this is a good question. Date formatting follows the same principles as described for decimals above. So the simple answer is "yes".

    However (I bet you expected an "however" right?), looking into standard Reports, passing Dates with FORMAT() (Text), using SetData/GetData (Text), and missing a proper Standard Format (see msdn.microsoft.com/…/az4se3k1(v=vs.110).aspx), this is a bit of more work compared to decimal formatting.

    But if you get rid of the 3 things above and have a correct "Long Date" format (by default or by using the Locale Builder overwriting the default (i.e."dd MMMM yyyy"), you'll get it working the same dynamic way.

    Cheers

    Carsten

  3. Bart Permentier says:

    Hello Carsten,

    I tried your solution but it is not working for me.

    If I change the language in the language property of the report the report will use that property. But you can't use an expression with datasets in that property(not even with get/set).

    But changing the language for one field doesn't seem to do anything (even when not using code and just filling it in manually).

    Am I missing anything?

    Thanks in advance!