Solved

How can I remove decimal places if all values to the right of a decimal  point are zeroes?

Posted on 2010-11-10
21
2,896 Views
Last Modified: 2012-05-10
In a report I have 2 format  possibilities:
1. 12345.6789
2. 12345.0000

How can I  make #2  just a whole number due to all zeroes in decimals,  when displayed and keep #1 the same including the decimals?

The desired results would then look like this.
1. 12345.6789 (no change)
2. 12345 (now just a whole number)
If I havn't provided you enough info just ask.
0
Comment
Question by:rhservan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +2
21 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34103389
you can use CAST function
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34103395
CAST(yr_column AS number)

0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34103406
SELECT CAST(YourVarcharCol AS INT) FROM Table
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 14

Expert Comment

by:Emes
ID: 34103488
=CInt(Fields!amt.Value)

that will turn the float into an interger (also round the number)
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34103492
What data type they have?

because if you do this, the result is what you expect.


If they are varchar, can do this.


Cheers.
DECLARE @a FLOAT = 0.1111
DECLARE @b FLOAT = 1.0000

SELECT @a, @b

(No column name)	(No column name)
0,1111	                 1



---


DECLARE @a VARCHAR(10) = '0.1111'
DECLARE @b VARCHAR(10) = '123456.0000'

SELECT @a, LEFT (@b, CHARINDEX( '.', @b) - 1)

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34103511
If you use CAST or CONVERT to make them INTEGER, you will loose the case

1. 12345.6789 (no change)


Cheers.
0
 
LVL 11

Expert Comment

by:rajvja
ID: 34103530
select case when cast(substring(cast(1234.1235 as varchar(12)),charindex('.',cast(1234.1235 as varchar(12)))+1,len(cast(1234.1235 as varchar(12)))-charindex('.',cast(1234.1235 as varchar(12)))) as int) = 0 then cast(1234.0000 as int) else 1234.6578 end

This is just example. Instead of static value, you can use your column name
0
 
LVL 11

Expert Comment

by:rajvja
ID: 34103548
Hi,

  I just used varchar(12). You can increase the size as per the length.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34103567
Just an improvement


DECLARE @a VARCHAR(10) = '0.1111'
DECLARE @b VARCHAR(10) = '123456.0000'

IF CAST(RIGHT(@a, LEN(@a)- CHARINDEX( '.', @a)) AS INTEGER)  = 0 
	print 'decimal'
else
	print 'integer'
	
	
IF CAST(RIGHT(@b, LEN(@b)- CHARINDEX( '.', @b)) AS INTEGER)  = 0 
	print 'decimal'
else
	print 'integer'

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34103659
Hi,

My last


Cheers
DECLARE @table TABLE (value VARCHAR(20))

INSERT INTO @table
VALUES('0.1111')

INSERT INTO @table
VALUES('123456.0000')


SELECT CASE WHEN CAST(RIGHT(value, LEN(value)- CHARINDEX( '.', value)) AS INTEGER)  = 0 
			THEN LEFT (value, CHARINDEX( '.', value) - 1)
		ELSE
			value
		END AS value
	FROM @table

Open in new window

0
 
LVL 14

Expert Comment

by:Emes
ID: 34103803
this will work for ssrs and only truncate the deciamal when there is 0000

=IIF(InStr(Cstr(Fields!amt.Value),"0000")<>0,cint(Fields!amt.Value),Fields!amt.Value)
0
 

Author Comment

by:rhservan
ID: 34105345
I like the expression format above however, when I attempt to configure substituting amt with my field "quantity"  the entire expression is underlined in red

=IIF(InStr(Cstr(Fields!amt.Value),"0000")<>0,cint(Fields!amt.Value),Fields!amt.Value)

Also, "0000" can somtimes be "000" or "00" or "0"

Can we tweak this expression to make it work?
0
 

Author Comment

by:rhservan
ID: 34105488
More info -

Error: =IIF(InStr(Cstr(Fields!amt.Value),"0000")<>0,cint(Fields!amt.Value),Fields!amt.Value)

Argument not specified for falsepart
0
 
LVL 14

Expert Comment

by:Emes
ID: 34105537
what is the type of the imput field?  Float or text ?
0
 

Author Comment

by:rhservan
ID: 34106414
the dataype retrun I receive is a system.decimal
0
 
LVL 14

Expert Comment

by:Emes
ID: 34106436
Ok need to take that value and convert it to text.

then need to get the text value right of the decimal  using instr.

then convert that value back to an number and see if it is 0

do you want the function?
0
 
LVL 14

Expert Comment

by:Emes
ID: 34106557
=iif(instr(cstr(Fields!amt.Value),".")=0,Cint(Fields!amt.Value),Fields!amt.Value)

I tested this on 2008 r2

when you take the cstr(value) it truncates the deciamal and all the 0 when the value is .00000
(not sure why but you can test it)

so all we look for is the decimal if it still there we know what to do.

0
 

Author Comment

by:rhservan
ID: 34107197
Excellent that is working correctly.

I discovered one more issue and that relates  to the #1 value which originally was stated as no change desired.  However, my notes tell me that value must be truncated to only 3 decimal places.

Origianlly I had that set through the tablix to be set to only 3 decimals but this will no longer work with the expression..

simply stated can we concanctenate an expression that will limit the decimal places to three which have > than 0 values.

For an example:

12345.6789 (4 decimal places) incorrect

Should read as:

12345.678 (3 decimal places) correct & desired
0
 

Author Comment

by:rhservan
ID: 34107797
Okay here is an update on this issue,

So on the Tablix properties under Number you can set the Custom format on the cell.

0.###;(0.###) This will give you both a positive & a negative format of:
12345.001 or (12345.001)
0r with all zeroes:
0

The 3 pound signs allow for only 3 decimal places but if they are all zeroes it will return only a 0.
 
0
 
LVL 14

Accepted Solution

by:
Emes earned 500 total points
ID: 34107960
set the format ##,###.###
0
 

Author Comment

by:rhservan
ID: 34112127
@EMES close enough. Thanks everyone for your efforts and responses.  I will have more coming stay tuned.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question