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.

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.

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.