SSRS custom number format

Question

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

I want to format the number like this...

• 15 is displayed as 15

• 14.3453453 is displayed as 14.35

• 12.1 is displayed as 12.1

• 0 is displayed as 0

• 1 is displayed as 1

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

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

Does anyone can suggest another way for me? Thanks!

Stink

I tried to use {0.##} only but it shows {1.} in the out excel file.. – Stink

Answer 1

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.

Supertanker

but why can i apply this expression into SSRS text box properties..? [=0]0;[=1]1;0.## I made a correct format that shown in Excel but when I apply into SSRS, it shows me an error message... – Stink

why can you?? do you mean how can you apply this??? just right click the textbox of the field you want formatting on – Supertanker

sorry for my typing mistake.. should be "why can't I apply this format [=0]0;[=1]1;0.## " – Stink

see what you want is, a variable format expression based on the number and its decimal places. why you want that, you want some of ur rows to have 4 and some to have 4.0? anyways, it can be done through expression. – Supertanker

the format expression that you are giving is wrong. let me update my answer. – Supertanker

if i use ##.## it will output "1." if the actual value is only 1 I don't want this.. I only want it outputs "1" for me, thanks again – Stink

see if the answer helps now – Supertanker

Thanks! It seems works... But the data format seems converted to String in Excel – Stink

try applying variable formatting through TextBox Properties > Numbers >Custom >Expression (where u were pasting your invalid format expression..go to its expression instead). see if it works, you can only pass format expressions there. so decide your individual format expressions, place an iif condition there and put your individual conditions in the iif. you got the idea right?? – Supertanker

Answer 2

Have you tried with the custom format "#,##0.##" ?

Oralle

Answer 3

You can use

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

Feeder