locked
Hide Header footer Export to Excel RRS feed

  • Question

  • Hello

    I want to hide header and footer only when I export report to excel as it makes report look messy.

    I have set the below visibility expression for my Header/Footer 

    =IIF(Globals!RenderFormat.Name = "EXCELOPENXML" OR Globals!RenderFormat.Name = "EXCEL", true, false)

    It works fine when I export to Excel 2007. However , it doesn't work for Excel 2003.

    Or I would better say it works for xlsx but does not work for xls.

    any inputs.

    Many thanks



    Monday, May 16, 2016 2:36 PM

Answers

  • Hi Pragati,

    According to your description, your expression for the head/footer in report works fine when exporting to excel 2007, but doesn’t work for excel 2003. Right?

    In Reporting Services, Since the Microsoft Excel 2003 rendering extension is already a deprecated feature.  And the “Globals!RenderFormat” variable was released from SQL Server 2008 R2. Then render format name when exporting to Excel 2003 may not be read by the newer Excel rendering extension. Please see;
    https://msdn.microsoft.com/en-us/library/dd255234.aspx

    If you still have question, please feel free to ask.
    Thanks,
    Xi Jin.

    Tuesday, May 17, 2016 9:42 AM

All replies

  • Hi Pragati,

    According to your description, your expression for the head/footer in report works fine when exporting to excel 2007, but doesn’t work for excel 2003. Right?

    In Reporting Services, Since the Microsoft Excel 2003 rendering extension is already a deprecated feature.  And the “Globals!RenderFormat” variable was released from SQL Server 2008 R2. Then render format name when exporting to Excel 2003 may not be read by the newer Excel rendering extension. Please see;
    https://msdn.microsoft.com/en-us/library/dd255234.aspx

    If you still have question, please feel free to ask.
    Thanks,
    Xi Jin.

    Tuesday, May 17, 2016 9:42 AM
  • Hello, how did you define this expression?  I'd like to do the same but have not found where to add the expression.  I also attempted to modify the rdl xml without sucess.  Thank you.
    Monday, March 11, 2019 7:45 PM
  • you can also modify rsreportserver.config and replace: 

     <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"> <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>

    with

     <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"> <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering">                <OverrideNames>                        <Name Language="en-US">Excel With No Header                        </Name>                </OverrideNames>                <Configuration>                    <DeviceInfo>                        <SimplePageHeaders>True</SimplePageHeaders>                    </DeviceInfo>                </Configuration>            </Extension>            <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering">                <OverrideNames>                    <Name Language="en-US">Excel With Header                    </Name>                </OverrideNames>            </Extension>

    it will give you the option to export reports to excel with or without the header. 

    This is a one time change and no need to modify all reports. No need to restart SSRS service.

    • Edited by Babak z Thursday, March 21, 2019 9:33 PM additional info
    • Proposed as answer by Babak z Thursday, March 21, 2019 9:34 PM
    Thursday, March 21, 2019 9:31 PM
  • Babak z

    This is amazing! Thanks so much


    Thursday, May 30, 2019 6:21 PM