lina06
asked on
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.
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.
ASKER
Whats the purpose of SimplePageHeaders ?
ASKER
PFrog, thank you for your response
I put header information within a subreport; however, this header was displayed in the Excel file.
I put header information within a subreport; however, this header was displayed in the Excel file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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?
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?
Hello PFrog
How do i go about correcting the pixel? where in BIDS will i need to adjust this setting?
How do i go about correcting the pixel? where in BIDS will i need to adjust this setting?
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.
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?
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?
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!!
Thanks for the Guide on the right path, we learn everyday!!
Thank you all for this solution. It's exactly what we were looking for! THANK YOU!
Perfect! Worked for me two.
I've aligned the title and a text box with one of the fields of the table.
I've aligned the title and a text box with one of the fields of the table.
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)