Formatting a number to export to Excel a cell value as a decimal

Question

Hi,

I value some cells using an expression with a REPLACE function in order to get the right field that in the SQL table is a numeric. I've tried to write FORMAT(Fields!MyNumericField.Value, "N1") or FORMAT(Fields!MyNumericField.Value, "##0.0") and I've tried to force the format in the format tab of the cell properties specifying N1, without any results: when I export the report to Excel the cell is formatted as a text.

Any ideas to solve this issue, if possible? Thanks

Answer 1

Hello,

Use your expression like this

=Format(CDBL(Fields!MyNumericField.Value), "##0.0")

or

=CDBL(Fields!MyNumericField.Value) in your textbox expression

and go to texbox proprty

Select format = "##0.0

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 2

Hi, thanks for you reply, but the suggested tip doesn't function. When I export to Excel the cell is formatted as a text. Probably this issue is because in the cell of the report is present an expression.

Thanks

Answer 3

Hello,

Create a dummy report and check this it is happening there ? Using above Steps

=CDBL(12345678.00)

 or just a give a try to this in your actual report

=CDBL(Val(12345678.00)) 

1. Right click on textbox properties .

2. Click on Number.

3. set up to decimal place  choose comma separated option if required.

---

blog:My Blog/

Hope this will help you !!!
Sanjeewan

Answer 4

Hi, as I said in the expression it's present a REPLACE function. In this function I've substituted "0,0" with "0.0". I've used this string to manage zero value in the cell.

Thanks

Answer 5

Hi Pscorca,

This issue may be caused by the limitations when exporting to excel. Text box values that are expressions are not converted to Excel formulas. The value of each text box is evaluated during report processing. The evaluated expression is exported as the contents of each Excel cell.  And text boxes are rendered within one Excel cell. Font size, font face, decoration, and font style are the only formatting that is supported on individual text within an Excel cell. For more information about it, please see:
http://msdn.microsoft.com/en-us/library/dd255234.aspx

Hope this helps.

Regards,
Charlie Liao

---

Charlie Liao
TechNet Community Support

Answer 6

Hi Charlie, and what is the solution?

I'm working with SSRS 2005. Thanks