I would like to format the number 12345 into 12,345. How can I achieve this?
in C#:int num = 1234567890;
String numFormatted = num.ToString("#,#");
Check out this documentation for more specifics on number formatting:
http://msdn2.microsoft.com/en-us/library/0c899ak8(VS.71).aspx
|||I would like to acheive this in reporting services.
|||I found out. I can make it using the format tab in properties of the textbox
i.e. convert
53000000.12
to
53,000,000.12
I'm thinking I'll have to do it with a user defined function and the
various string functions myself but was wondering if anyone had an
easier way?
Cheers
Dave"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312150306.34760a0@.posting.google.co m...
> Once I've converted my floats to chars using STR, is there an easy way
> to put commas in separating the thousands.
> i.e. convert
> 53000000.12
> to
> 53,000,000.12
> I'm thinking I'll have to do it with a user defined function and the
> various string functions myself but was wondering if anyone had an
> easier way?
Yes, do it at your presentation layer, not at the DB layer.
> Cheers
> Dave|||Hi Dave
You shouldnt really do any formatting of numbers in the database. It
should be done on the client.
Sam
dave@.daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca434844.0312150306.34760a0@.posting.google.com>...
> Once I've converted my floats to chars using STR, is there an easy way
> to put commas in separating the thousands.
> i.e. convert
> 53000000.12
> to
> 53,000,000.12
> I'm thinking I'll have to do it with a user defined function and the
> various string functions myself but was wondering if anyone had an
> easier way?
> Cheers
> Dave|||dave@.daveandcaz.freeserve.co.uk (David Sharp) wrote in message news:<ca434844.0312150306.34760a0@.posting.google.com>...
> Once I've converted my floats to chars using STR, is there an easy way
> to put commas in separating the thousands.
> i.e. convert
> 53000000.12
> to
> 53,000,000.12
> I'm thinking I'll have to do it with a user defined function and the
> various string functions myself but was wondering if anyone had an
> easier way?
> Cheers
> Dave
You could do this TSQL by creating your own functions, but you should
probably do it in the front end application instead, if possible. The
format above is incorrect in Spain and Germany, for example - a client
application can retrieve locale information and format the output
accordingly much more easily than doing it on the server side.
Simon|||"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0312150306.34760a0@.posting.google.co m...
> Once I've converted my floats to chars using STR, is there an easy way
> to put commas in separating the thousands.
> i.e. convert
> 53000000.12
> to
> 53,000,000.12
> I'm thinking I'll have to do it with a user defined function and the
> various string functions myself but was wondering if anyone had an
> easier way?
IMHO, this is a job for the Presentation Layer/GUI. I wouldn't store commas,
or format the numbers for me. I'd rather handle this on the client side so I
am not storing needless data (, and .) in the database, and so I can format
the data properly according to regional setting on the client side.
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com|||> Once I've converted my floats to chars using STR, is there an easy way
> to put commas in separating the thousands.
> i.e. convert
> 53000000.12
> to
> 53,000,000.12
> I'm thinking I'll have to do it with a user defined function and the
> various string functions myself but was wondering if anyone had an
> easier way?
> Cheers
> Dave
Hi Dave,
If you really want to do it in TSQL. Cast as Money. Then convert
using style 1. "convert(varchar,cast(myVar as money),1)" - Louis|||Thanks to everyone who replied and of course who are absolutely
correct that this sort of data formatting should be done in a
presentation layer rather than the db.
Unfortunately in this specific case, the text is being generated to
annotate a simple calculation performed in a stored proc called by a
trigger and so doesn't have a presentation layer in which to do the
formatting.
Thanks Louis for highlighting a possible solution.
louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0312151608.7c1f93f2@.posting.google.com>...
> If you really want to do it in TSQL. Cast as Money. Then convert
> using style 1. "convert(varchar,cast(myVar as money),1)" - Louissql
I do this in the expression.
=Format(Fields!ThisValue.Value, "$#.####)
Hope this helps.
|||Both of the above are possible options.
rs12345 - put "c4" into the Format property of the textbox
guyinkalamazoo2 - apply the formatting directly tot he value in the expression of the textbox
Hi Bill,
Thanks for that, unfortunately it's not something I can do in my ASP code, so that should work a treat.
Little'un
sqlI have a column in a dataset and i would like to change the number format of the column so that it has comma's separating the thousands place.
Hi,
Function Fixbigint(lng as long) as string
Dim nfi As System.Globalization.NumberFormatInfo = _
New System.Globalization.CultureInfo _
("en-US", False).NumberFormat
' lng=9223372036854775807
nfi.NumberGroupSeparator =","
return lng.ToString("N", nfi)
end function
s it any useful.
kiki
Is that for Sql.
||| NVM i figured it out again.
hi,
I have a column of numbers which export fine to excel when i use the formatting '$0'.
If any of the values in the column = 0 i dont want them to show so i added this line to the value =iif(Fields!Budget.Value = 0, "", Fields!Budget.Value). This works fine except when i export to excel it now doesnt read them as values and you have to convert all the cells individually. I guess this is becuase i have substituted the string "" when the data = 0 and excel does the formatting for the whole column not each cell individually. What should i do so it exports to excel correctly? Is there another way to show nothing in a cell and it still registers as a number?
geoff
WHat about Nothing rather than "" ?=iif(Fields!Budget.Value = 0, Nothing, Fields!Budget.Value).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
I'm using Reporting Services in conjunction with Analysis Services.
The problem I'm having is formatting numbers to be dollar amounts. I know how to set this in Reporting Services and when a data source was regular SQL, then everything was just fine. However, when I switch to Analysis Services the number would just stay the same.
i.e. "60.2" instead of "$60.20". The only thing I can think of is if Analysis Services is returning the number as a string for some reason, and RS won't format strings for currency.
Any thoughts? Thanks!You should be able to format measures from SSAS the same way you do this with a relational database. Can you repro your issue with the Adventure Works cube?|||I think this is the problem: the numbers that being returned are attributes not measures... which is right b/c I'm using the numbers as parameters for drill-down reports.
So the question is how do you format a number returned by AS20005 as an attribute to be a U.S. dollar amount? Thanks!|||Okay, looks like the attribute number was getting returned as a string. I used CDbl() to convert into a double and it works fine now.
Hi all
I want my measures to be exported(Excel) as numbers instead of text.
When i simply use them as is it does this correct but as soon
as I as the following number formating it no longer exports
correctley.
=FormatNumber(Fields!Example.Value,2,True,True,True)
The same apllies to whe I set the formatting in the text box properties to N
Can some please tell me how I can acheive this result when exporting
Thanks in advance
G
Apply formatting using N in the Format property, then in the value textbox, apply an explicit cast of the value. E.g.
=CDbl(Fields!Example.Value)
Bizzare, but I've found this to work for me.
|||Thanks Adam
I came accros the same thing today when i was casting
the values returned from custom code. I just hope the same
principal will aplly to currency.
In my case I had to figure to use N0
as format type to get 1,345,456
When using the cdbl() to do this you cant use FormatNumber(,,,)
that was my snag.
Thanks again for your time
G
Simon|||There's no easy way to do this. For some reason I did need output like
this and wasn't able to use a front end to do the formatting, so I made
my own function.
Use as so:
SELECT dbo.Format_Number(513434512.2344)
Output is $513,434,512.23
Yes, it rounds and adds a dollar sign. But you can change it around.
:)
HTH,
Jennifer
CREATE FUNCTION Format_Number (@.N decimal(18,2))
RETURNS nVarChar(30)
AS
BEGIN
Declare @.NRnd Decimal(18,2)
Declare @.Dollar nVarChar(30)
Declare @.Dollar2 nVarChar(30)
Declare @.L int
Declare @.A int
Declare @.B int
Declare @.C int
Declare @.Cents nvarchar(20)
Declare @.NC nvarchar(30)
Set @.NC = Cast(@.N as Nvarchar(30))
Set @.NRnd = Round(@.N, 0, 1)
Set @.Dollar2 = ''
Set @.Dollar = Cast(@.NRnd as NvarChar(30))
Set @.Dollar = Substring(@.Dollar,1, Len(@.Dollar) - 3)
Set @.C = PATINDEX('%.%',@.NC)
Set @.Cents = Substring(@.NC, @.C, 3)
Set @.L = Len(@.Dollar)
Set @.A = @.L/3
Set @.B = 3
While @.A >= 0
Begin
Set @.Dollar2 = Substring(@.Dollar,@.L - @.B + 1,3) + ',' + @.Dollar2
Set @.B = @.B + 3
Set @.A = @.A - 1
End
If Left(@.Dollar2,1) = ','
Set @.Dollar2 = Substring(@.Dollar2, 2, Len(@.Dollar2))
Return '$' + Substring(@.Dollar2,1, Len(@.Dollar2)-1) + @.Cents
END|||>> For some reason I did need output like this and wasn't able to use a
front end to do the formatting, so I made my own function. <<
Since this is a fundamental violation of software engineering
prtinciples, might you share with us WHAT that reason was? It is worth
a paper in a journal.|||It was a totally stupid reason, of course. :) My boss wanted an email
output of a query emailed to him on a daily basis, so I set up a job to
do that. And then he came back and said, it sure would be nice if
those dollar amounts looked like dollars, and could the output be
changed. So being completely new and straight out of school I did as
asked.
Let me know how that paper comes out, will you? ;)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Since this is a fundamental violation of software engineering
> prtinciples, might you share with us WHAT that reason was? It is worth
> a paper in a journal.
The world is not always as ideal as you may want to be. There are probably
tons of business reports out there that are run from no other front end
than Query Analyzer, or similar tool. For some reason, someone started to
do it in QA, probably because it was a little urgent, and not possible to
pack into something better. Then that temporary hack became permaent etc.
Until one day, the requirements goes beyond what is really healthy to do
in SQL.
Anoher reason could be that the front-end tool is hopelessly difficult
to use...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152
output need it to be -
00000.1520
How can I do this?
You'll have to convert it a string and prepend/append the zeroes.
-Jamie
|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||
Godai B wrote:
Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.
Too many steps? Really?
"0000" + (DT_STR, 20, 1252) 0.1520
That doesn't seem like too many to me. Can you give me examples of format functions like you require?
-Jamie
|||
this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)
the output format needs to be 000000.0000 .
the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.
same with digits after the decimal point.
ex: Input -->10.120 Output --> 000010.1200
Input --> .1 Output --> 000000.1000
what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.
Hope u can help me with an easy way to do this.
|||I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:
RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))
Yeah, maybe a format function would be good.
-Jamie
