March 14, 2006 at 7:40 am
I'm trying to pretty up a report. How do I get commas in the numeric columns? The columns are defined as decimal (18,0).
Obviously, cast/convert seems to be an option however I cannot seem to find the correct conversion/data type in BOL. For instance the date or money cast/converts have several forms to choose from...can't find any for decimal or numeric. Any suggestions?
March 14, 2006 at 7:56 am
If you really, really have to have the commas in the value coming out of sql server your going to cause extra work for yourself.
No numeric values are returned from sql server with the commas natively. you will have to cast to varchar, then put commas in as necessary.
This is a presentation issue, whererever you are displaying your report should be able to handle it, and that is the correct place to put it.
Excel, Reporting Services, Crystal, all have the ability to put a value to a currency format.
March 14, 2006 at 8:01 am
Thanks. I was just trying to keep it simple. Very tiny 6 record report going out to DBAs only.
Seems such a simple op but no simple code or conversion seems to exists to do it. Bizarre.
March 14, 2006 at 8:14 am
Not really bizarre - it's not the job of SQL Server to present data, that's what the front-end tools that Ray mentioned are for. SQL Server handles the storage, processing and retrieval of data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 14, 2006 at 8:19 am
Yeah, I've never really searched for one. I've usually had other options
check out this thread
it has a link with a function that should do the trick
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=151824
March 14, 2006 at 8:33 am
If you're trying to output this to a file, you might look at using DTS for this.
When you say "report," I'm not sure DTS is the method but you could use a built-in function to format numbers on the column of interest:
FormatNumber( <<Expression>> , <<NumDigitsAfterDecimal>> , <<IncludeLeadingDigit>> , <<UseParensForNegativeNumbers>> , <<GroupDigits>> )
If you're not already familiar with DTS, there's a lot of explaining to do to get you using it and I'd look for a different solution (and, start learning about DTS, anyway, it's very useful).
What tool is generating the report, because most reporting tools do handle number formatting. For instance, I'm sure you could easily do this in an Access report.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply