Using FormatCurrency in SSRS 2005

  • 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]

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

  • Glen Sidelnikov (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 (9/25/2008)


    Glen Sidelnikov (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]

  • Marios,

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

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

    and You can always prefix $

  • 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]

  • 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?

  • 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]

  • 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.

  • 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

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

    FormatNumber() is the correct way to achieve this.

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

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

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

  • 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)

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

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