Retain leading zero's while exporting SSRS report to excel

• kk.86manu

  Ten Centuries

  Points: 1268

  More actions

  January 28, 2015 at 2:47 pm

  #397940

  Hi All,

  I have a query in a SSRSreport that returns a value that looks like '012345'.The value looks fine on the report preview screen.

  When the report is exported to excel, that value is displayed in a cell as '012345'.When I click out of the field, excel is dropping the leading zero and converting the value in the field to 12345.

  Why is this happening and i have converted the value as string as well using expression.

  Thanks for your help

• OCTom

  SSChampion

  Points: 11785

  More actions

  January 28, 2015 at 2:54 pm

  #1773826

  kk.86manu (1/28/2015)

  ---

  Hi All,

  I have a query in a SSRSreport that returns a value that looks like '012345'.The value looks fine on the report preview screen.

  When the report is exported to excel, that value is displayed in a cell as '012345'.When I click out of the field, excel is dropping the leading zero and converting the value in the field to 12345.

  Why is this happening and i have converted the value as string as well using expression.

  Thanks for your help

  That is a function of how Excel displays numeric values. To display the leading zeroes, you have to change the Excel cell.

  Tom

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 28, 2015 at 6:33 pm

  #1773861

  You could try using an expression to change the output of the cell when the RenderFormat is excel, to include either a leading apostrophe (single quote) OR an equals sign followed by the value wrapped in double-quotes.

  e.g. = iif(Globals!RenderFormat.Name = "EXCEL", "'" & Fields!MyField.Value, Fields!MyField.Value)

  This will then format your value differently for output to Excel.

  However, I think how well this works may be dependent on what version of Excel you use - on my Office 365 version, it displays the leading apostrophe until you edit and save the cell, when it then displays correctly, which is not what you want, but you may be luckier.

  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]