change number format in a SQL Report Server expression string

Posted on 2004-11-16
Last Modified: 2008-01-09
Basically I get how to change the properties of a cell in a SQL Server Report but I can't figure out how to do it when my expression in a cell is not limited to a single field.

Changing the properties of a cell that has an expression like:
="Total NLR USD: " & Fields!NLR_USD.Value

just leaves the number as, well, a number. How can I edit the expression to have the number in "Value" appear as currency.

The result should print:

Total NLR USD: $1,000,000.00 if value = 1000000

Question by:pauldes
    LVL 1

    Expert Comment

    Maybe it works when you change it to:

    ="Total NLR USD: " & Convert(Money, Fields!NLR_USD.Value)
    LVL 14

    Expert Comment

    Quickest way is to have:

    ="Total NLR USD: $" & Fields!NLR_USD.Value

    This will work, though will only use $, so it depends if you need to have varying currency formats in your report.

    Author Comment

    simon, tried that but the number does not format with commas 1,000,000 prints as 1000000

    mjm, I get a build error on your suggestion:
     [BC30684] 'Convert' is a type and cannot be used as an expression.

    LVL 14

    Accepted Solution

    OK, the following should give your value a format you are after:

    ="Total NLR USD: $" & FormatNumber(Fields!NLR_USD.Value, 2, , ,TriState.True)


    ="Total NLR USD: " & FormatCurrency(Fields!NLR_USD.Value,,, TriState.True, TriState.True)

    See these links for the full context parameters of the above two functions:

    FormatNumber Function

    FormatCurrency Function

    Author Comment


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now