Retain leading zero's while exporting SSRS report to excel

  • 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

  • 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

  • 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]

  • a simple solution to your problem.

    just convert that field to varchar in your query like this

    select convert(varchar,colname) from table.

    when report will be exported to excel leading 0s will not missed.

  • Viewing 4 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply