SSRS expression to format two decimal places does not show zeros

Question

I am using the following expression to format my value to show only two decimal points. Which Works fine if the value is not 0. However when the value is 0 it does not show 0.

eg. Expression used

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

If CUL1.Value is 2.5670909 the value shown in the report 2.56 (this is brilliant!) If CUL1.Value is 0.006709 no value is shown (I would like it to show 0.00) If CUL1.Value is 0 no value is shown ( I would like to show 0)

Thanks.

Chesterfield

Do you have specific rounding requirements? I would expect 2.5670909 to round to 2.57 and 0.006709 to round to 0.01, and that's exactly what happens when you apply your expression to your data. Are you effectively truncating them? Can you please confirm? – Server

This isn't properly documented by Microsoft, but have you tried using "#0.##" in stead? – Reliant

To answer Ian's questions. I have no specific rounding requirements. I only want to show two decimal points. Yes I am effectively truncating the value however when the value is 0.006709 nothing is shown! – Chesterfield

Answer 1

If you want it as a string use:

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

As a number use:

=FormatNumber(Fields!CUL1.Value, 2)

This will ensure it exports out to excel correctly as a number.

Braque

It think it's worth noting that this converts the value to a string. It's not a number anymore when exported to Excel... Going to the Properties Window (F4) and find Number, then enter N2 as the format (or N0 is you want no decimal places) will generate a number in Excel. – Anet

use FormatNumber(Fields!CUL1.Value,2) instead – Hevesy

I had to do CDec() on my field before using Format() – Blaineblainey

Just worked for me when I used cDbl as here: FormatNumber(Sum(cDbl(Fields!CUL.Value)), 2). – Gump

Answer 2

You need to make sure that the first numeral to the right of the decimal point is always displayed. In custom format strings, # means display the number if it exists, and 0 means always display something, with 0 as the placeholder.

So in your case you will need something like:

=Format(Fields!CUL1.Value, "#,##0.##")

This saying: display 2 DP if they exist, for the non-zero part always display the lowest part, and use , as the grouping separator.

This is how it looks on your data (I've added a large value as well for reference):

If you're not interested in separating thousands, millions, etc, just use #0.## as Paul-Jan suggested.

The standard docs for Custom Numeric Format Strings are your best reference here.

Server

I had to do CDec() on my field before using Format() – Blaineblainey

I have used the format as =Format(Fields!CUL1.Value, "#,##0.00") to add decimal place as .00 if whole number passed (for ex: 35 or 35.00). – Marsupium

how to make 2.5 display as 2.50? – Keenan

Answer 3

If you want to always display some value after decimal for example "12.00" or "12.23" Then use just like below , it worked for me

FormatNumber("145.231000",2) Which will display 145.23

FormatNumber("145",2) Which will display 145.00

Tomy

Answer 4

Format(Fields!CUL1.Value, "0.00") would work better since @abe suggests they want to show 0.00 , and if the value was 0, "#0.##" would show "0".

Lehmbruck

Answer 5

Actually, I needed the following...get rid of the decimals without rounding so "12.23" needs to show as "12". In SSRS, do not format the number as a percent. Leave the formatting as default (no formatting applied) then in the expression do the following: =Fix(Fields!PctAmt.Value*100))

Multiply the number by 100 then apply the FIX function in SSRS which returns only the integer portion of a number.

Harcourt

Answer 6

Please try the following code snippet,

IIF(Round(Avg(Fields!Vision_Score.Value)) = Avg(Fields!Vision_Score.Value), 
Format(Avg(Fields!Vision_Score.Value)), 
FORMAT(Avg(Fields!Vision_Score.Value),"##.##"))

hope it will help, Thank-you.

Schumann