Format Numbers in Multiple columns of SSRS Report

Question

Currently I am working on a ssrs report. The table in report is having about 30 columns. For each column I have to modify the number format (to either 2 decimal or no decimal numbers). I can do it by click on every column and modify the number property in format menu. But is there any way to format all the columns at a time?

I tried to select entire row -> F4 -> properties -> Number -> Format-> Expression. And set expression to:

=FormatNumber(Fields!HoursWorked.Value&Fields!ContactAttempted.Value&Fields!UnableToContact.Value,2)

But it throws an error

Type character '&' does not match declared data type 'Object'.

Can any one help me on this?

Answer 1

This is really, really stupid; you have to have spaces before and after the ampersand, and if you don't then you tend to get that error message. I don't know why, it's daft as hell but I bet that's it. Catches me out constantly. Try:

=FormatNumber(Fields!HoursWorked.Value & Fields!ContactAttempted.Value & Fields!UnableToContact.Value,2)

Answer 2

You need to set format number for each column separately. If you don't want to set number format in Text Box Properties, you can set format such way:

Click on cell with value -> F4 -> field Format in Properties -> set format (for example, you can use this format: #,0.00 for numbers with space as 1000 separator and negative numbers as -12 345.00)

Answer 3

If you want to set the Format property of a cell (or cells - note you CAN set the format for multiple cells at the same time) then you need to specify a value or expression that resolves to a recognised format string, e.g. '$'0,.00;'$'-0,.00 or C2.

The expression you have given returns the actual formatted value of the cell, so this will not work if entered into the Format property - this needs to go in the Value property of the textbox.