Hello Jim,

could you attach your actual report, or describe the specific =Format(...) RDL expressions you are currently using?

I'm trying to better understand why you cannot achieve the desired effect by using the Format property on the textbox instead of using the Format function.
You may be aware of this, but just to make sure, I'd like to point out that for a textbox.Format property you can use custom .NET numeric format strings as explained in the following documentation links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
Specific examples:
http://msdn.microsoft.com/en-us/library/7x5bacwt(VS.71).aspx

HTH,
Robert Bruckner
SQL Server Reporting Services Team

Sorry, forgot to include the MSDN link for the custom format string examples:

http://msdn.microsoft.com/en-us/library/7x5bacwt(VS.71).aspx

Hi Robert,

We have a lot of options in our product to support different types of formatting for numbers and currencies. We also have a requirement to have the report formats for numbers and currencies use the same formatting options set in Regional Settings for that user. Regional Settings lets you set any character as the decimal and thousands separator.

I thought about doing this with a format string but could not come up with a good way to set the decimal separator and groupSeparator with a format string. Instead we decided to wrap all our numeric fields with a custom function Code.MyFormat(Fields!col.Value, decimalSeparator, groupSeparator, currencySymbol). The custom function creates a NumberFormatInfo object which allows us to set the decimalSeparator and groupSeperator to whatever we want to get the correct format. For example:

Lets say the user sets their group separator to "-" and their decimal separator to "," in regional settings. We would do this:

Private nf As New System.Globalization.NumberFormatInfo

nf.CurrencyDecimalSeparator = ","
nf.CurrencyGroupSeparator = "-"
Return wkNum.ToString("C", nf)

This all works great with the exception of Excel which treats all our numbers as string because there is no actual Type for a textbox to let Excel know this is actually a number.

If there is a way to have a format string like this

#-###,00;(#-###,00);#

that would recognize a that "," is a decimal separator, I would love to know how to do that. The best I could find is to manipulate the language to do this but I don't think that is really how we want to handle this.

One of the needs here is that a US user may be creating an invoice for a client in Europe and they may want to use a number format with different separators so we need to do this kind of formatting.

Any input or suggestions on this would be greatly appreciated.

Hey Jim,

We were running into the same issue while exporting to excel with SSRS 2005. We had to take off the format from the expression in the value field, like FormatCurrency, FormatNumber, etc. Let the value field for the textbox be set to the data field. Under properties of textbox, go to the Format tab and enter a format code in there. Currency would be something like C1 or C2. The output to excel then recognises the formatting.