reporting-servicesnumber-formattingcustom-formatting

SSRS custom number format

I am go to generate an excel file from SSRS, and

I want to format the number like this...

I can apply this in Excel but unable to apply in SSRS

[=0]0;[=1]1;0.##

ssrs error message

Does anyone can suggest another way for me? Thanks!

Solution

  • am assuming that you want to know how to format numbers in SSRS

    Just right click the TextBox on which you want to apply formatting, go to its expression.

    suppose its expression is something like below

    =Fields!myField.Value

    then do this

    =Format(Fields!myField.Value,"##.##")

    or 

    =Format(Fields!myFields.Value,"00.00")

    difference between the two is that former one would make 4 as 4 and later one would make 4 as 04.00

    this should give you an idea.

    also: you might have to convert your field into a numerical one. i.e.

      =Format(CDbl(Fields!myFields.Value),"00.00")

    so: 0 in format expression means, when no number is present, place a 0 there and # means when no number is present, leave it. Both of them works same when numbers are present ie. 45.6567 would be 45.65 for both of them:

    UPDATE :

    if you want to apply variable formatting on the same column based on row values i.e. you want myField to have no formatting when it has no decimal value but formatting with double precision when it has decimal then you can do it through logic. (though you should not be doing so)

    Go to the appropriate textbox and go to its expression and do this:

    =IIF((Fields!myField.Value - CInt(Fields!myField.Value)) > 0, 
    Format(Fields!myField.Value, "##.##"),Fields!myField.Value)

    so basically you are using IIF(condition, true,false) operator of SSRS, ur condition is to check whether the number has decimal value, if it has, you apply the formatting and if no, you let it as it is.

    this should give you an idea, how to handle variable formatting.