CODEWITHSUNDEEP
reporting-services
Asieh hojatoleslami
Asieh hojatoleslami

Reputation: 3360

How to remove trailing zeros using ssrs?

I have a column DECIMAL(18,4). when I insert data like 123.45 it becomes 123.4500 I want to show it in SSRS like 123.45. How to remove those zeros in SSRS?

Upvotes: 3

Views: 8782

Answers (7)

DataPispor
DataPispor

Reputation: 381

This will remove trailing zeros: CDbl(Fields!YOURFIELD.Value)

Upvotes: 0

Zorba Tamangen
Zorba Tamangen

Reputation: 1

Open SSRS Query Designer and add:

SELECT CONVERT(DOUBLE PERCISION,FIELD) FROM TABLE SOURCE

enter image description here

OR add to your select result from your Store Procedure source

SELECT CONVERT(DOUBLE PERCISION,FIELD) FROM TABLE SOURCE

Upvotes: 0

Sean
Sean

Reputation: 152

Depending on what exporting formats your need you can set the number formatting to 0.####;(0.####)

I know this is compatible with the SSRS viewer and exporting to PDF, but Excel would take 123.0000 and show it as 123. instead of just 123

Upvotes: 3

Tim
Tim

Reputation: 21

You can change the data type returned to SSRS to a FLOAT. That should do it :-)

Upvotes: 2

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

I dont agree with the accepted answer, casting to a string is another workaround:

=Str(NumericValueWithVariableDecimalPlaces)

Unfortunately a consequence is no numeric formatting settings will apply and setting cell Alignments to right causes numbers to be misaligned.

Upvotes: 1

Asieh hojatoleslami
Asieh hojatoleslami

Reputation: 3360

I try and find solution:

I use Expression for value:

 =IIf(IsNothing(Fields!FieldName.Value), "", IIf(IsNothing(Fields!FieldName.Value), "-", CInt(Fields!FieldName.Value * 10000) / 10000))

Upvotes: -1

Jt2ouan
Jt2ouan

Reputation: 1964

This can be done in the SQL which you can do by casting the value to DECIMAL(18,2) 

ex:
  CAST(FieldName as DECIMAL(18,2))

but if you really want to do it in SSRS. You can right click on the textbox that the field is displaying in and go to textbox properties. In the pop-up box choose 'Number' and set the Category to 'Number' and then decimal places to 2. This should correctly display the value.

You could also right click on the textbox and go to expression and say this in the expression popup box:

   =FormatNumber(Fields!FieldName.Value,2)

Upvotes: -1

Related Questions