How to format numerics with the sign on the right?

I've got an amount field in a report where I want the values to have the sign
(if negative) on the right. But I also want the numbers to line up properly.
I can't seem to find a way to format this. In Excel, you'd use a format like
#,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
see you can use custom formats and the syntax is similar to Excel but not
quite the same. I tried:
#,##0.00 ;#,##0.00-
but RS seems to ignore the whitespace. I also tried:
#,##0.00' ';#,##0.00-
also to no avail.
Any suggestions?What about using double quotes: " " ?
"virtualfergy" wrote:
> I've got an amount field in a report where I want the values to have the sign
> (if negative) on the right. But I also want the numbers to line up properly.
> I can't seem to find a way to format this. In Excel, you'd use a format like
> #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> see you can use custom formats and the syntax is similar to Excel but not
> quite the same. I tried:
> #,##0.00 ;#,##0.00-
> but RS seems to ignore the whitespace. I also tried:
> #,##0.00' ';#,##0.00-
> also to no avail.
> Any suggestions?|||Tried it. No luck I'm afraid. Works the same as single quotes. Any other
advice out there?
"Albert" wrote:
> What about using double quotes: " " ?
> "virtualfergy" wrote:
> > I've got an amount field in a report where I want the values to have the sign
> > (if negative) on the right. But I also want the numbers to line up properly.
> > I can't seem to find a way to format this. In Excel, you'd use a format like
> > #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> > see you can use custom formats and the syntax is similar to Excel but not
> > quite the same. I tried:
> >
> > #,##0.00 ;#,##0.00-
> >
> > but RS seems to ignore the whitespace. I also tried:
> >
> > #,##0.00' ';#,##0.00-
> >
> > also to no avail.
> >
> > Any suggestions?

How to format numbers in SQL Query

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99Create the below function and use it as said below.

/*This function is only for thousand separator for numbers with length 5 or 4*/
CREATE FUNCTION DBO.SEPARATETHOUSANDNUM
(
@.STRVALUE VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @.STRRETURNVALUE VARCHAR(8000)
SELECT @.STRRETURNVALUE = CASE LEN(@.STRVALUE)
WHEN 5 THEN LEFT(@.STRVALUE,2)+ ','+ RIGHT(@.STRVALUE,3)
WHEN 4 THEN LEFT(@.STRVALUE,1)+ ','+ RIGHT(@.STRVALUE,3)
ELSE @.STRVALUE END
RETURN @.STRRETURNVALUE
END

SELECT DBO.SEPAREATENUMBERS(23565) AS CHANGEDCOLUMN

gives 23,565

SELECT DBO.SEPAREATENUMBERS(2365) AS CHANGEDCOLUMN

gives 2,365

So use
Select DBO.SEPARATETHOUSANDNUM(TotalAmount) from Table1

Quote:

Originally Posted by sukeshchand

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

|||I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser

Excellent! Thanks for posting the solution!|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(money,TotalAmount),1) from BillMaser

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?|||

Quote:

Originally Posted by mjv

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

try adding precision on your convert function.

actually, although this is feasible in the database/back-end, i believe this can be better be handled in the front-end.

How to format numbers in a query

Hey - I have a quick question and know that it is probably pretty simple, but I am stumped. I have a query where I need to make a colum a number that looks like a percent with 2 significant digits:

i.e.,
SELECT tblNumericCovert.number1, tblNumericCovert.number2, [number1]/[number2] AS testDiv
FROM tblNumericCovert

where testDiv needs to spit out results like this ###.##

I am totally lost, if anyone can help, I would appreciate it.select to_char(number1/number2, '99.99') As testDiv from table;|||Originally posted by r123456
select to_char(number1/number2, '99.99') As testDiv from table;

When I do this, I get the error message that "to_char is not a vaild function name'|||What db are you using? to_char works for Oracle, but not SQL server.

Try:

select convert(money, (number1/number2)) As testDiv from tablesql

How to find the Product of 4 Numbers in a Column?

Hi I have a table with this data

Description Number

-
Something1 2
Something2 3
Something3 4
Something4 6

I would like to find the product of 4 numbers (2*3*4*6) as output (144)

Please advice

Thanks

hi try this

SELECT *
INTO #Number
FROM (
SELECT 1 as Something,2 as Numbers UNION ALL
SELECT 1 as Something,3 as Numbers UNION ALL
SELECT 1 as Something,4 as Numbers UNION ALL
SELECT 1 as Something,6
) Number

DECLARE @.Product int

SET @.Product = 1

SELECT @.Product = @.Product * Numbers
FROM #Number

SELECT @.Product

DROP TABLE #Number|||

There was a request here to find a generic method of finding aggregate products of column entries:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1435635&SiteID=1

Umachandar posted a reference to a site here that has a generic solution that can be used:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

|||

Nice answer

But I am expecting answer without using those values in the select statement

Some times I have only 3 rows sometimes I have 6 or 7 rows

But I want to find the product of those numbers in a Column

Please advice

Thanks

|||hi, those were only sample data to test if the query works. you can then filter the values from the other columns.

umandars' aggregate product were great! two thumbs up umandar..|||

Here's little way to do what you ask.

Create a string that is the formula. Evaluate the string:

Code Snippet

declare @.Formula varchar(8000)

select @.Formula = isnull(@.Formula + ' * ' + convert(varchar,[Number]),convert(varchar,[Number])) from <DataTable>

select @.Formula

exec ('select ' + @.Formula)

|||

Simply superb

I got my answer from your query

Thanks a lot

With Regards

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>>SELECT ...
>>FROM ...
>>WHERE number %1 = 1
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
>> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>> SELECT ...
>> FROM ...
>> WHERE number %1 = 1
>> Hi Roji,
>> I think you meant to write
>> WHERE number % 2 = 1
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
Steen
SELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>
|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.4ax.com...

How to find odd numbers?

Hi
What is the best/easiest way to find records where a certain number is odd?
I need to search for some records in one of our databases and one of the
criterias is that a number is odd.
Regards
SteenSELECT ...
FROM ...
WHERE number %1 = 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%231LOEH$FFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Hi
> What is the best/easiest way to find records where a certain number is
> odd?
> I need to search for some records in one of our databases and one of the
> criterias is that a number is odd.
> Regards
> Steen
>|||On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:

>SELECT ...
>FROM ...
>WHERE number %1 = 1
Hi Roji,
I think you meant to write
WHERE number % 2 = 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep, thanks Hugo..
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...
> On Mon, 21 Feb 2005 14:23:54 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
> I think you meant to write
> WHERE number % 2 = 1
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||...thanks guys...that was the function I was looking for. I just couldn't
find it in BOL.
Regards
Steen
Roji. P. Thomas wrote:[vbcol=seagreen]
> Yep, thanks Hugo..
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:d1ej11te1mgli39rt6hcg4e17tvm06qofr@.
4ax.com...