Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS custom number format

Tags:

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.##

ssrs error message

Does anyone can suggest another way for me? Thanks!

like image 201
K.C Avatar asked Dec 27 '12 08:12

K.C


People also ask

Is Ssrs outdated?

Discontinued functionality in SQL Server 2022 Reporting Services. These features are discontinued and no longer available in SQL Server 2022 Reporting Services and Power BI Report Server September 2022. Power BI reports in Power BI Report Server offer mobile capabilities.

How do you display zeros as blanks in a SSRS report?

You can use FORMAT function to format numbers, e.g. =Format(Fields! MyField. Value,"0.00").


2 Answers

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.

like image 123
Manish Mishra Avatar answered Nov 01 '22 22:11

Manish Mishra


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

like image 41
vonbalaji Avatar answered Nov 01 '22 22:11

vonbalaji