• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

change number format in a SQL Report Server expression string

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

Thanks
0
pauldes
Asked:
pauldes
  • 2
  • 2
1 Solution
 
mjm42Commented:
Maybe it works when you change it to:

="Total NLR USD: " & Convert(Money, Fields!NLR_USD.Value)
0
 
simon_kirkCommented:
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.
0
 
pauldesAuthor Commented:
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.

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

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

Or

="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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformatnumber.asp

FormatCurrency Function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformatnumber.asp
0
 
pauldesAuthor Commented:
Thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now