8

argh!

Can't stand it that i can't figure it out myself....

i've used this in the formatting of a number in my report:

'€' #,0.00;('€' #,0.00)

and that formats to € 1,212.89

which is not exactly what i want, because i want € 1.212,89 regardless of the regional setting of the server.

So i tried this

'€' #.0,00;('€' #.0,00)

but that gives me this: 1.212.890

Typing this i realize that i don't know what the # and the . and the , mean in the format string.....

NakedBrunch
  • 48,713
  • 13
  • 73
  • 98
Michel
  • 23,085
  • 46
  • 152
  • 242

6 Answers6

8

You can find the definition of the comma and period dynamic behavior here: http://msdn.microsoft.com/en-us/library/0c899ak8.aspx

I think the best way to reliably get what you want is to hard code a locale into the expression for this field.

= (new Decimal(11123.55)).ToString("€#,0.00;(€#,0.00)",
        new System.Globalization.CultureInfo("es-ES"))

This will always use the comma as decimal, and period as millions, thousands &c.

There are ways to be more dynamic and always return in the clients local set format, that would usually be preferable.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • 1
    Yes. I copied and pasted that code from an SSRS text box (developed in BIDS, not report builder.) If you are using this many places you should use a piece of embedded code. – Jamie F Mar 23 '11 at 12:18
  • 1
    You, Sir, Are a hero. Thanks @JamieF – Eon Dec 07 '14 at 17:22
5

I know this is an old thread, but in case someone needs it, there is an easiest and most proper way to do it :

  1. Right click on the Textbox of your expression
  2. Select "Number" in the popup "Text Box Properties"
  3. In the "Category", select "Number"
  4. Tick the "Use 1000 separator
  5. Click OK

To "customize" your '1000 separator':

  1. Select the Textbox of your expression
  2. In the Properties on the right, apply the right culture in the "Language" property. E.g. Select "fr-CH" to have 123'456 otherwise the default is 123,456 as English separator.
pti_jul
  • 432
  • 1
  • 5
  • 18
4

Try this out. It will format your value to the correct number of decimal places.

=format(1212.89,"€#,#.00")
NakedBrunch
  • 48,713
  • 13
  • 73
  • 98
  • 2
    this works (gives me 1,212.89). But i want the decimal and thousand separator switched. So i tried this: =format(1212.89,"€#.#,00"), which gives me.... 1212.890 – Michel Mar 23 '11 at 08:17
  • 2
    The period in the format string will turn into the decimal separator for what ever locale the report is generated with (this can be user locale or server locale, depending on settings.) The comma in the format will turn into the thousands separator for the same locale. you can force the use of a literal comma with an escaped comma ("\,") but this will still be a pain to get into the right places, particularly since you might need it in the millions and thousands places. – Jamie F Mar 23 '11 at 13:53
2

For indian currency, in the field value use like =Format(Fields!ServiceTaxAmt.Value,"##,##,##,###.00") and change the language value to hi-in for report property.

Saravanapandian
  • 49
  • 6
2

You can use the format € #,0.00 and set the language of your report to de-DE by clicking outside of the report area and in the right properties pane go to Localization -> Language.

user764754
  • 3,865
  • 2
  • 39
  • 55
1

This Works correctly with [set Language as hi-IN and Format as "##,##,##,###.00"]

Appreciate your Efforts

Also Same can be achieved by following Steps

  1. Go to Text Box Properties
  2. Select Number category in Number Tab
  3. Check the check box beside [Use 1000 separator(,)]
  4. Click Ok
Raj Mathur
  • 11
  • 2