0

I need to add a thousand separator to numbers in a table in a report in SSRS. I am using SQL server 2008 R2. There are plenty of examples when using a textbox but I tried changing the number to a string using an expression and it does nothing. This is my expression:

Format(CStr(Fields!AnnualIncome.Value), "#,###")

I also tried:

FormatNumber(Fields!AnnualIncome.Value, , , , TriState.True)

Where am I going wrong?

UPDATE This how I added the format to the cell: enter image description here

Gloria Santin
  • 2,066
  • 3
  • 51
  • 124
  • A couple of things: Expressions should begin with `=` (like an Excel formula), so for example: `=Format(...)`. Secondly, you shouldn't convert a value to a string before you format, just supply the value as-is. I think your first attempt was close, but should be: `=Format(Fields!AnnualIncome.Value, "#,###")`. – Cᴏʀʏ Jan 11 '17 at 21:44
  • I have an `=` in my report. I will try your suggestion. – Gloria Santin Jan 11 '17 at 21:47
  • Your suggestion did not work. I believe it is because the value in the table column is a number and this function is for strings. – Gloria Santin Jan 11 '17 at 21:49
  • Ditch the expression. Set the value of the cell to the field you want. In the properties window for a cell, find the Format attribute, and then add your format string there. You could use `N0` or explicitly your previous one, `#,###`. – Cᴏʀʏ Jan 12 '17 at 03:10
  • The Format attribute for the cell is under the Number heading. I tried both of your suggestions and it had no affect at all. I added a screen shot of the field that I changed in case this is not the one you are referencing. – Gloria Santin Jan 12 '17 at 03:51
  • Let's start at the beginning: can you show the part of your query that selects the number you want formatted (include the column datatype too). Maybe show the column listing of the dataset in your report. If the number isn't being formatted, my guess is that it's already coming into the report as a string. SSRS needs a numeric data type before it can properly format it using number format patterns. – Cᴏʀʏ Jan 12 '17 at 03:56
  • Duh...you are correct. Annual Income field is a string. I have another column that is Poverty Level that is a number and it worked perfectly by using `#,###`. So, do I use the text functions to format the string? – Gloria Santin Jan 12 '17 at 04:03
  • You can either convert it in the SQL query (e.g. `CONVERT(decimal(14,2), AnnualIncome)`, or go back to using an expression for the value (i.e. `=CDec(Fields!AnnualIncome.Value)`. Either way, leave the format string in the Number > Format property, it's nice to not have that buried in the expression. – Cᴏʀʏ Jan 12 '17 at 04:08

1 Answers1

0

I believe your attempts to format aren't working because the field value you have is already a string. Attempting to apply a numeric format to a non-numeric datatype will result in no format being applied.

You could either change your SQL query to convert the string field to a numeric type:

CONVERT(decimal(14,2), AnnualIncome)

or convert the string value in an expression: 

=CDec(Fields!AnnualIncome.Value)

Either way, I think it's nicer to leave the format string as a cell attribute and not have it buried in the expression. If you choose the first option, you will need to refresh the dataset in your report so that it has the proper metadata for the columns in your query.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • I tried using this `= FormatNumber(CDec(Fields!AnnualIncome.Value), , , , vbTrue)`. Changing it to a number then formating to a number but it does not change the format – Gloria Santin Jan 12 '17 at 04:20
  • I converted `AnnualIncome` to an INT and then used the same formatting as the other column. It now works. THANKS! – Gloria Santin Jan 12 '17 at 04:23