rhservan
asked on
How can I remove decimal places if all values to the right of a decimal point are zeroes?
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.
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.
you can use CAST function
CAST(yr_column AS number)
SELECT CAST(YourVarcharCol AS INT) FROM Table
=CInt(Fields!amt.Value)
that will turn the float into an interger (also round the number)
that will turn the float into an interger (also round the number)
What data type they have?
because if you do this, the result is what you expect.
If they are varchar, can do this.
Cheers.
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)
If you use CAST or CONVERT to make them INTEGER, you will loose the case
1. 12345.6789 (no change)
Cheers.
1. 12345.6789 (no change)
Cheers.
select case when cast(substring(cast(1234.1 235 as varchar(12)),charindex('.' ,cast(1234 .1235 as varchar(12)))+1,len(cast(1 234.1235 as varchar(12)))-charindex('. ',cast(123 4.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
This is just example. Instead of static value, you can use your column name
Hi,
I just used varchar(12). You can increase the size as per the length.
I just used varchar(12). You can increase the size as per the length.
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'
Hi,
My last
Cheers
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
this will work for ssrs and only truncate the deciamal when there is 0000
=IIF(InStr(Cstr(Fields!amt .Value),"0 000")<>0,c int(Fields !amt.Value ),Fields!a mt.Value)
=IIF(InStr(Cstr(Fields!amt
ASKER
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),"0 000")<>0,c int(Fields !amt.Value ),Fields!a mt.Value)
Also, "0000" can somtimes be "000" or "00" or "0"
Can we tweak this expression to make it work?
=IIF(InStr(Cstr(Fields!amt
Also, "0000" can somtimes be "000" or "00" or "0"
Can we tweak this expression to make it work?
ASKER
More info -
Error: =IIF(InStr(Cstr(Fields!amt .Value),"0 000")<>0,c int(Fields !amt.Value ),Fields!a mt.Value)
Argument not specified for falsepart
Error: =IIF(InStr(Cstr(Fields!amt
Argument not specified for falsepart
what is the type of the imput field? Float or text ?
ASKER
the dataype retrun I receive is a system.decimal
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?
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?
=iif(instr(cstr(Fields!amt .Value),". ")=0,Cint( Fields!amt .Value),Fi elds!amt.V alue)
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.
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.
ASKER
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@EMES close enough. Thanks everyone for your efforts and responses. I will have more coming stay tuned.