Numbers exported to excel in SSRS

• SatishAyyar

  SSCrazy

  Points: 2825

  More actions

  June 15, 2010 at 7:19 pm

  #233750

  I have Sales field having some numeric values and some Hyphens(Based on some condition) in a report. Now when I export the report to excel I get the error "number stored as text" for the Sales field. I do not want to display this error. I want the Sales field as Numeric.Please advice.

  Thanks

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  June 16, 2010 at 11:44 am

  #1181202

  Hi can you just try this:

  1. In the layout, right click the field and go to Property of the field.

  2. Click on the format tab then provide the required format.

  Thanks & Regards,
  MC

• SatishAyyar

  SSCrazy

  Points: 2825

  More actions

  June 16, 2010 at 1:12 pm

  #1181247

  For example I have a column with Amount having values Null and 10.

  I want to display hyphen in place of Null. When I export to excel it gives me an error number stored in text.

  MC,

  I wrote the below code in the format . But its not displaying the hyphen when I run the report.

  =IIF(IsNothing(Fields!Amount.Value),"-",Fields!Amount.Value)

• vasanti mestri

  Right there with Babe

  Points: 786

  More actions

  June 17, 2010 at 3:36 am

  #1181428

  I am also having same problem..Can anybpdy suggest on this?

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  June 17, 2010 at 5:57 am

  #1181486

  Hi,

  What I understood is after exporting to excel, when you keep the cursor over the field the error is displaying , am I correct?

  This is because along with the numeric value the "-" also coming.I would suggest two things.

  1. In the field of above expression right click and make the format to text (not sure whether it acceptable to you if you want the numeric type itself , it is up to your requirement)

  OR

  2. In the database table,try to store zero instead on NULL.

  Thanks & Regards,
  MC

• SatishAyyar

  SSCrazy

  Points: 2825

  More actions

  June 17, 2010 at 7:56 am

  #1181575

  I asked my Manager and he asked me to print blank (Nothing) instead of "-". Now the values that are exported to excel are comming up as numeric. My problem is solved for now, but worrying if I get the same situation in future.

  But still I did not get, how to make it work with "-". I tried to convert the values and "-" to text and then export to excel, but gives me the same error.

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  June 17, 2010 at 10:07 am

  #1181682

  Hi,

  Are you able to try the above two suggestions I have mentioned?

  Thanks & Regards,
  MC

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 22, 2010 at 5:34 am

  #1183239

  I know this is a bit old and "sort of sorted" but here is the way you can do it with "-".....

  In the Value expression for the field, use

  =IIf(IsNumeric(Fields!Number.value),Fields!Number.Value,0.000000)

  And in the Format expression use

  =IIF(ISNUMERIC(Fields!Number.Value),"0.00","""-""")

  This will ensure the excel export uses numerics and displays a "-" where the value was NULL.

  NOTE: This places a value of 0 (zero) into the spreadsheet in place of the NULL value.

  ALSO NOTE: The choice of "0.00" to format the value when it is numeric was arbitrary, however the use of "0.000000" in the value expression is required to make SSRS display the "-"

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]