Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 21370
  • Last Modified:

Export to Excel from Reporting Services

I am using Reporting Services 2005 and SQL Server 2000.  I placed a reportviewer control on my ASP2.0 webpage to display a report.
 
When I tried to export my report to an Excel file, I get a lot of extra columns.  I cannot sort the result in an Excel file because of the header displayed on the report.  I tried to move header in the special table located in the body section of the report.  That got rid of the extra columns; however, I cannot sort selected columns because it includes a cell from this table-header.   A client DOES NOT WANT to select some section of the report and perform sorting, so sorting is performed for all data rows in the report by selecting one or more columns.   The same client also DOES NOT WANT to place header information in the footer section.
 
1.) Is it possible to exclude the header of the report when exporting it to Excel.  For some reason, when I export my report to Excel file I cannot see the footer section of the report.  Can I hide the header section of the report when it is displayed in an Excel file?  
 
2.) If I cannot implement the option #1, how to implement this option:
If the report is complex, you might want to consider a separate version designed for Excel. You could place your own 'psuedo' button on the proper report, the button will run the Excel version and automatically render it to Excel via URL access.
0
lina06
Asked:
lina06
  • 4
  • 4
  • 2
  • +2
1 Solution
 
PFrogCommented:
I believe it is not possible to exclude headers from Excel. Also Excel does not export footers by design - there is no workaround for this apart from putting your footer in the body of the report.

There are two options here
1) as you mentioned in option 2 - have a seperate report for Excel export. This will be the easiest, most flexible solution
2) you could try putting your header information within a subreport. The Excel export does not render subreports so this may solve the problem for you. (This is not a long term solution as SSRS 2008 will export subreports to Excel)
0
 
lina06Author Commented:
Whats the purpose of SimplePageHeaders?
0
 
lina06Author Commented:
PFrog, thank you for your response
I put header information within a subreport; however, this header was displayed in the Excel file.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PFrogCommented:
I'm not doing very well on this one am I?!

Ok, I've done a bit more playing around with this, it appears sub reports do get rendered but only if they are not within a table or matrix control. If they are within a table or matrix then they will not render. However you do get a nasty "within table/matrix are ignored" message.

As for SimplePageHeaders, it seems you've answered your own question. This does what I didn't think was possible - it renders the report header as an Excel header not as part of the body. Well found!

You can either specify this in the URL
   http://[server]/ReportServer?/Report1&rs:Format=EXCEL&rc:SimplePageHeaders=true
or you can add it to the rsreportserver.config file
<Render>
    <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
        <Configuration>
            <DeviceInfo>
                <SimplePageHeaders>True</SimplePageHeaders>
            </DeviceInfo>
        </Configuration>
    </Extension>
</Render>

I think you win your own points on this one...
0
 
M_Boy76Commented:
Hello

I have a report developed for end users, but when they export it into excel, three of the columns tend to merge, and create extra columns.

I have attached a sample of the excel export, and marked the columns in question 'A', 'B' and 'C'.

Column marked 'A ' in the spreadsheet, which is in column L has merged with column M, the same applies to B and C.

Can anyone help?
XXX-Report.xls
0
 
PFrogCommented:
This happens when there are items in the report that don't exactly align. In order for the report to render a column in Excel, everything on the report must be perfectly alligned to the pixel.

i.e. if you have two textboxes on the report, one above the other, their left and width properties must be identical, otherwise it will create multiple columns and merge them where necessary.
|  xxxx |
|  xxxx  |
will create two columns. the first row will look like
|          ||
and then merge the cells to make the second row the right size
|           |

does that make sense?
0
 
M_Boy76Commented:
Hello PFrog

How do i go about correcting the pixel? where in BIDS will i need to adjust this setting?
0
 
PFrogCommented:
I've just noticed, you've got two textboxes on row 1 causing the problem. The first is the report title. you should make the right of the title textbox align exactly with a column in your table. Also, there's another empty textbox covering coumns O and P on row 1. You should make this align properly with columns in the table as well.
0
 
M_Boy76Commented:
Hello PFrog

I have done as advised and it worked , but there is another set of empty fields in line 2 of the excel spreadsheet, do you have any idea how to get it removed so that the line 3 become line 2?
0
 
M_Boy76Commented:
Fixed!!!, had to readjust the alignment of the table a little closer to the text box which was acting as the report title.

Thanks for the Guide on the right path, we learn everyday!!
0
 
all2neatCommented:
Thank you all for this solution. It's exactly what we were looking for! THANK YOU!
0
 
venanciopCommented:
Perfect! Worked for me two.
I've aligned the title and a text box with one of the fields of the table.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now