none
Reporting Services 2008R2 - Exporting to Excel without headers

    Question

  • Hi all,

    I am using SQL Server 2008R2.  In Reporting Services, when exporting to Excel, I want to suppress the report header.  So, I have searched the web and see that their is a parameter I can set in the rsreportserver.config file...  So, I have changed the Excel section to:

       <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
        <Configuration>
         <DeviceInfo>
          <SimplePageHeaders>TRUE</SimplePageHeaders>
         </DeviceInfo>
        </Configuration>
       </Extension>

    I went into SQL Server Configuration Manager and restarted the reporting services service and... it doesn't seem to work.

    I go to a standard report in myweb\reports.. choose the export to Excel option and the header shows up in the Excel document...

     

    I have rebooted the server, rebooted my desktop and have deleted my temporary files in IE...  I have goine into the ReportService logs and I don't see any errors... What have I missed?

     

    Thanks!

     

    Additional items attempted:

     

    In trying to validate that I had modified the right file, I made an additional modifiecation by adding logic for yet another export option. 

       <Extension Name="CSV (ASCII-No Header)" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
         <OverrideNames>
             <Name Language="en-US"> CSV (ASCII-no header)</Name>
         </OverrideNames>
         <Configuration>
              <DeviceInfo>
                 <NoHeader>True</NoHeader>
                 <Encoding>ASCII</Encoding>
              </DeviceInfo>
         </Configuration>
       </Extension>

    The good news is that this does show up on the export options... It works fine.  So, I am back to the question I posted at the top... Why is the header still showing...  What have  I missed?

     

    Many Thanks...



    • Edited by Sql Dude Tuesday, November 22, 2011 11:18 PM
    Monday, November 21, 2011 10:12 PM

Answers

  • I figured it out.

    The report was generated by a wizard in Report Builder.  The wizard places the header of the report in the body, not in the header.  So, if you move the header to the header section everything works as expected.

    Thanks..

    • Marked as answer by Sql Dude Tuesday, November 29, 2011 8:42 PM
    Tuesday, November 29, 2011 8:42 PM

All replies

  • Hi Sql Dude,

    In Reporting Services, the ??implePageHeaders??setting indicates whether the page header of the report is rendered to the Excel page header. If you set the value to "TRUE", it means to display the page header in the Header/Footer section of Excel, rather than hide the header:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/84893b1b-8f41-4fcf-aab9-72d2d5f60544/

    In SQL Server Reporting Services 2008R2(SSRS 2008R2), we can hide the object based on current format:
    =(Globals!RenderFormat.IsInteractive = "EXCEL")

    For the details, you can refer to:

    http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/globals-renderformat-aka-renderer-dependent-report-layout.aspx

    Thanks,
    Lola


    Please remember to mark the replies as answers if they help.

    Wednesday, November 23, 2011 6:26 AM
    Moderator
  • Lola,

    Thank you for your reply...

    I am puzzled...  I set up both options and can't see the difference in 2008R2.  I am trying to replicate what we are doing in SSRS 2005.

    Here is my config in 2008R2....

    -------------------------------------------------------------------------

       <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
        <Configuration>
         <DeviceInfo>
          <SimplePageHeaders>False</SimplePageHeaders>
         </DeviceInfo>
        </Configuration>
       </Extension>
       <Extension Name="EXCEL (Simple Hdrs)" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
         <OverrideNames>
             <Name Language="en-US"> EXCEL (Simple Hdrs)</Name>
         </OverrideNames>
        <Configuration>
         <DeviceInfo>
          <SimplePageHeaders>True</SimplePageHeaders>
         </DeviceInfo>
        </Configuration>
       </Extension>

    -------------------------------------------------------------------------

    -------------------------------------------------------------------------

    Here is my Config in SSRS 2005

       <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
        <Configuration>
         <DeviceInfo>
          <SimplePageHeaders>True</SimplePageHeaders>
         </DeviceInfo>
        </Configuration>
       </Extension>

    -------------------------------------------------------------------------

    In SSRS 2005 - When I run the report with Simple Headers = True -----

     

      In Progress Waiting on Client Waiting on Vendor Prioritized/Pending Request On-going Complete
    Jones, John 8 3 3 8 3 30
    Smith, Jane 10 2 4 5 14
    Doe, Sam 1 3

     

    -------------------------------------------------------------------------

    In SSRS 2008R2 - When I run the report with Simple Headers = True -----

    Advancement - Demo Report            
                               
      PLU ID LAST NAME FIRST NAME AGE GENDER MRTL CODE ALUMNUS IND PREF COLL CODE PREF CLAS YEAR CAMPAIGN NAME DESIGNATION NAME Total GIFT AMT

    -------------------------------------------------------------------------

    In SSRS 2008R2 - When I run the report with Simple Headers = False -----

    Advancement - Demo Report            
                               
      PLU ID LAST NAME FIRST NAME AGE GENDER MRTL CODE ALUMNUS IND PREF COLL CODE PREF CLAS YEAR CAMPAIGN NAME DESIGNATION NAME

    Total GIFT AMT


    I also investigated the option suggested by Robert Bruchner's article on hiding headers.  There are two problems with this...  First, it leaves a blank row in row 1 (Sorry it doesn't show that very well... but it is blank in row 1)... I want the column headers in row 1.  Also, I have 2000+ reports that I would need to change...

    Here is what I added to the visibility property of the header text box... =(Globals!RenderFormat.Name="EXCEL").

     Here is the output from SSRS 2008R2 using simple headers set to false...

                             
      PLU ID LAST NAME FIRST NAME AGE GENDER MRTL CODE ALUMNUS IND PREF COLL CODE PREF CLAS YEAR CAMPAIGN NAME DESIGNATION NAME Total GIFT AMT

     

     So, in reading more on the msdn site... http://msdn.microsoft.com/en-us/library/dd255234.aspx

    Page Headers and Footers


    Depending on the Device Information SimplePageHeaders setting, the page header can be rendered in two ways: the page header can be rendered at the top of each worksheet cell grid, or in the actual Excel worksheet header section. By default, the header is rendered to the cell grid on the Excel worksheet.

    The page footer is always rendered to the actual Excel worksheet footer section, regardless of the value of the SimplePageHeaders setting.

    Excel header and footer sections support a maximum of 256 characters, including markup. If this limit is exceeded, the Excel renderer removes markup characters starting at the end of the header and/or footer string to reduce the number of total characters. If all markup characters are removed and the length still exceeds the maximum, the string is truncated starting from the right.

     

    So, I think that what I want is for the SimplePageHeader is to be set to true...  So, that the header is stored in the Excel header, not in row 1...

    In SSRS 2005, when I set simplepageheaders to true, the page header is not stored in column 1 of the spreadsheet, it is stored in the header of the spreadsheet.  I am not certain how to send an image, but I can do a a screen print that will show the 2005 spreadsheet example above with no page header in row 1, the header is stored in the header section... You can see the header in View>Page Layout... but in normal it doesn't show.

    Thanks again for your help..

     

    SQLDude

     



    • Edited by Sql Dude Wednesday, November 23, 2011 7:43 PM
    • Marked as answer by Sql Dude Tuesday, November 29, 2011 8:22 PM
    • Unmarked as answer by Sql Dude Tuesday, November 29, 2011 8:22 PM
    • Marked as answer by Sql Dude Tuesday, November 29, 2011 8:23 PM
    • Unmarked as answer by Sql Dude Tuesday, November 29, 2011 8:24 PM
    Wednesday, November 23, 2011 6:34 PM
  • I figured it out.

    The report was generated by a wizard in Report Builder.  The wizard places the header of the report in the body, not in the header.  So, if you move the header to the header section everything works as expected.

    Thanks..

    • Marked as answer by Sql Dude Tuesday, November 29, 2011 8:42 PM
    Tuesday, November 29, 2011 8:42 PM