Fixed Dollar Sign

Question

Post reply

Fixed Dollar Sign

ppbedz

Old Hand

Points: 369
More actions
June 24, 2011 at 6:22 am

#242591

Hi,
I need to format values on a report using commas , decimals, and a fixed dollar sign. So far, I have only been able to achieve a floating dollar sign. Can anyone provide the correct custom format mask? Thank you....

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

by fixed dollar sign, what do you mean? a dollar sign followed by a number up to say, $999,999,999.99 in size?

so 1.22 has 10 spaces between the $ and the one?

$999,999,999.99

$ 1.22

like that?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

minehere SSC Journeyman Points: 91 More actions · Answer 2

You can use a function to reformat the data as a string.

The function could look like

create function MoneyToFixString(@InValue Money)

returns varchar(30)

as

begin

declare @TempValue varchar(30)

set @TempValue = CONVERT(varchar(30), @InValue, 1)

return '$' + substring(' ', 1, 29 - LEN(@TempValue)) + @TempValue

end

go

declare @test-2 money

set @test-2 = 12345678.1234

select dbo.MoneyToFixString(@Test)

The 29 is the max length (30) less the leader length (1 for $)

You can make the inside of the function do anything you want.

Hope this is helpful

Lowell SSC Guru Points: 323518 More actions · Answer 3

don't forget more than one space in a row will not render in HTML by default, you have to replace with   or CHAR(160)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ppbedz Old Hand Points: 369 More actions · Answer 4

yes, that is exactly what they want. The dollar signs should all line up in the leftmost corner of the cell.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 5

ppbedz (6/24/2011)
yes, that is exactly what they want. The dollar signs should all line up in the leftmost corner of the cell.

A kind of a sideways way to do this would be to add a column to the left of your money column and just put a $ in the column. You could add visibility constraints if you need. Format the money column as a number with 2 decimal places and comma separators for 1,000s.

calvo SSChampion Points: 12930 More actions · Answer 6

Daniel Bowlin (6/24/2011)
A kind of a sideways way to do this would be to add a column to the left of your money column and just put a $ in the column. You could add visibility constraints if you need. Format the money column as a number with 2 decimal places and comma separators for 1,000s.

Was just about to say this. Easy, quick, no data modification, no messing with padding/trimming/& nbsp stuff.

Not sure if this is the "best" or "right" way to do it though.

I'd test it though to see if you like it.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

ppbedz Old Hand Points: 369 More actions · Answer 7

I actually considered that idea too. Going to wait and see whether the report user "pushes" the issue. They plan to export to excel and could change the formating after the fact (but you know users)......