I was trying to export a report which contains a number format. When I do that, all the numbers in excel will have a green small tag beside it saying "Convert from Number to Text". Is there anyway that I can change the format to a number when I export it to excel?

You need to make sure that the number has the correct datatype is RS. I found wrapping the field reference with an explicit conversion function solves the excel problem e.g. =CDbl(Fields!your_numerical_column.Value)

Nice solution!

i am exporting report to excel from SQL Reporting services 2000 .The size of report is ver large .when i filter data is less and its working fine . but for repot .Its is not working .any Idea Thanks Tauseef

This doesn't seem to work on calculated fields. Can anyone help me to get the numbers to export as numbers rather than text for a calculated field (IE Sum(Fields!Field.Value)). I have tried CDec(Sum(Fields.Field.Value)) and that doesn't work. Please help, the export is kind of worthless if it is doing all text when some fields should be numeric. jfk

blocked !!! i Used FormatCurrency function in SSRS report. when i export into excel it again converted into text. But if suppose i didn't used FormatCurrency so it's work !!! Any solution for this!!! Thanks

Set the Format of TextBox as C2. Where C2: convert to Currency with 2 decimal places. If some of column has Percentage so u can use for this is P2. Where P2: convert percentage sign with 2 decimal place. This is work definately!!! thanks

I'm having the same problem with a sum field (in a matrix) that's formatted as N0. But when it gets to excel, it seems to convert itself back to text. My sum is part of an IIF statement since the user wants a zero to appear if there's no value. Anything you can suggest?

Can you post your expression?

Adam Tappis wrote: Can you post your expression? =iif(Sum(Fields!usertotal.Value) = 0,0,Sum(Fields!usertotal.Value))

can you try: =CInt(Sum(Fields!usertotal.Value))

Adam Tappis wrote: can you try: =CInt(Sum(Fields!usertotal.Value)) Thank you, it worked!

Thanks Adam. I also had the same number formatting problem and Searched many pages on net for solution. CInt worked for me also.

Hi Adam, I have a field when its numeric should display the number and when it is not numeric it should display "-". I have written an expression but when I try to export to excel the nuber is converted to text field. I should get the number in the numeric format when exported to excel. Please help. The expression I am using is iif(IsNumeric(Fields!Net_Sales.Value) = true,CDbl(iif(IsNumeric(Fields!Net_Sales.Value) <> 0, format(round(Fields!Net_Sales.Value,8),"##,##,##,##,###.########"),0)),"-") Thank You

Hi Guys, I am also having the same Number Format issue when exporting to excel using SSRS 2008 and MS Excel 2003/2007 In this particular report, I am displaying INT and DECIMAL(10,4) fields in SQL Server 2008, and I expect to have the INTs as integers with no decimal points, and I expect to display the DECIMAL(10,4)s as a number with one decimal place, with no commas in either case. I already had wrapped the INT fields with CInt() and the DECIMAL(10,4) fields with CDec() and I have also formatted the text fields with f0 or f1 (as the case may be), but the export to excel STILL does not export the number fields as numbers. Does anyone have any other clues, please?

This worked for me. Thank you very much.

Hi Satish, I am getting the same issue. Please share your solution. Thanks, Ram Sharma

Thanks for all the helpful tips on this thread. I've now solved most of my issues with numbers being exported to excel. I still have one: - I am formatting integers with commas: =Format(Fields!Integer.Value, "#,###0") for ease of reading. Is there a way to specify the integer format together with the comma? FYI: I use SQL 2008 Thanks

Tommy, rather than applying your own formatting using the Format function in the Value property, I suggest putting the format string into the Format property. I believe that should export the cell formatting to Excel properly.Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)