Using FormatCurrency in SSRS 2005

Question

Post reply

Using FormatCurrency in SSRS 2005

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
September 25, 2008 at 11:57 am

#193210

Hi,
I'm using the FormatCurrency function to format currency columns like this:
=FormatCurrency(Fields!field1.Value, 2)
I get values like this:
...
$300000.56
$20000
...
How can I get the amounts to show up with commas every 3 digits and always with 2 decimal places, like this:
...
$300,000.56
$20,000.00
...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 16 total)

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

Glen SSCertifiable Points: 6737 More actions · Answer 1

= "$" & Format(Fields!Field1.value,"##,###,##0.00")

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Glen (9/25/2008)
= "$" & Format(Fields!Field1.value,"##,###,##0.00")

thank you! I'll try that.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

Marios Philippopoulos (9/25/2008)
Glen (9/25/2008)
= "$" & Format(Fields!Field1.value,"##,###,##0.00")
thank you! I'll try that.

Actually, this is not giving me the right results:

I have this value in the database: 615.00000

In the report it shows up as: $615615.00

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Glen SSCertifiable Points: 6737 More actions · Answer 4

Marios,

Format() does not change the field value. If instead of 615 you are receieving 615615, you made an error in expression.

abhijeetv SSCommitted Points: 1945 More actions · Answer 5

FormatNumber(yourfield, 2, , ,TriState.True)

and You can always prefix $

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Sorry guys, but I'm not getting the right results.

Take this NUMERIC(16,2) value: 615.00

When I use:

="$" & Format(Fields!col1.Value,"##,###,##0.00")

I get: $615615.00

When I use:

=FormatNumber(Fields!col1.Value, 2, , ,TriState.True)

I get syntax error.

When I use:

="$" & Fields!col1.Value

I again get: $615615.00

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

abhijeetv SSCommitted Points: 1945 More actions · Answer 7

I have copy-pasted the text in your messege and tested with 20000 as well as 615.00 hard-coded values, I do not get error in the out put but I get $20,000.00 and $615.00 respectively.

So I wonder what error you are talking about?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

abhijeetv (9/29/2008)
I have copy-pasted the text in your messege and tested with 20000 as well as 615.00 hard-coded values, I do not get error in the out put but I get $20,000.00 and $615.00 respectively.
So I wonder what error you are talking about?

Here is the screenshot...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

abhijeetv SSCommitted Points: 1945 More actions · Answer 9

Agreed with the Syntax error that is shown in the Edit Exression window however have you still tried to run the report and see the output?

If not, can you please do so ?

I am sure it will work.

Thomas Bastian SSC Rookie Points: 37 More actions · Answer 10

I just tried the Formatcurrency function for SSRS and it worked.

Try passing values for all options. The only issue being if the user exports this to Excel, they will not be able to do mathematical calculations on that since this function returns a string

q-310296 SSC Journeyman Points: 75 More actions · Answer 11

Yes, this is correct. In some cases ignore the red warnings. Likely a small bug.

FormatNumber() is the correct way to achieve this.

xryanlee SSC Journeyman Points: 81 More actions · Answer 12

xryanlee

SSC Journeyman

Points: 81

September 5, 2010 at 6:50 pm

#1216734

Textbox Properties --> Format --> Format Code: C

david_wier SSC Enthusiast Points: 110 More actions · Answer 13

try this approach (copied from my report)- it's much simpler:

=Format(Fields!UnitPrice.Value,"C")

andy_koval SSC Journeyman Points: 85 More actions · Answer 14

Did you ever get an answer on this..I'm have the save issue. The value returned is incorrect, but the formatting is fine...here is what I'm doing ....FormatCurrency(Fields!TotalOrderCost.Value.ToString(),0)