SSRS alternating 1000 separators with Number Format

Question

I am creating a report in Microsoft Report builder, which to my knowledge uses MS Visual Basic in its expressions, and I am currently stuck with a weird number formatting problem:

For some unknown reason, the requirement is to format numbers (ranging from 10.00 to 10,000,000,000.00+) as following: 10.000,000.000,00 - so alternating 1000 separators and a comma as the decimal separator. Smaller numbers will have less separators obviously, but the order of them does not change.

Is there any way to do this using the format function, or do I have to make some kind of manual monstrosity using the switch and substring functions?

Answer 1

You can use custom code to create the formatted string

Public Function FormatNumber( num As Decimal) As String

Dim s1 As String
Dim s2 As String

' Format integer part using chars a and b
s1 = Format(  num\1, "###a###b###a###b###" )

' Remove unwanted a and b characters from begining

While s1(0) = "a" Or s1(0) = "b" 
s1 = Right(s1,Len(s1)-1)
End While

' Replace a and b characters
s1 = s1.Replace("a",",")
s1 = s1.Replace("b",".")

' Take decimal part and format it as two digits
s2 = Format ( ((num Mod 1)*100) \ 1, "00" )

Return s1 + "," + s2

End Function

Now on your field use the following expression

=Code.FormatNumber(Fields!val.Value)