RS2k5 Crashes exporting to Excel

We've got a clinet that has no issues exporting reports to excel about 95%
of the time. However, about once a month, they get the error page "For more
information abou this error navigate to the report server on the local
server machine, or enable remote errors."
In the app event logs, there is the following error: Report Server
(MSSQLSERVER) cannot connect to the report server database.
I'm not finding anything useful in the groups. Thanks for any help.Hi Jack,
One good place to look is in the actual SRS error logs. This can usually be
found at <SRS Install Directory>\Reporting Services\LogFiles. Check the
ReportServer logs for the day of the error. Often times, the stack trace of
the error is there and can help you diagnose the root cause of the issue.
Brian
--
Brian Knight
Quaere SQL Server Blog
http://dba.typepad.com
"Jack" wrote:
> We've got a clinet that has no issues exporting reports to excel about 95%
> of the time. However, about once a month, they get the error page "For more
> information abou this error navigate to the report server on the local
> server machine, or enable remote errors."
> In the app event logs, there is the following error: Report Server
> (MSSQLSERVER) cannot connect to the report server database.
> I'm not finding anything useful in the groups. Thanks for any help.
>
>

RS2005: Excel huge file size

I have an issue with exporting RS2005 reports to Excel:
1) The exported fie size is disproportional to the rowset size - ~7000
records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
2) RS exports to Excel with a redundant blank A column on the left and 4
rows that hold the report caption. Is there anyway to configure RS not to
export with these rows and columns, so it'll start directly with my data?
We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818Offirh wrote:
> I have an issue with exporting RS2005 reports to Excel:
> 1) The exported fie size is disproportional to the rowset size - ~7000
> records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
> 2) RS exports to Excel with a redundant blank A column on the left and 4
> rows that hold the report caption. Is there anyway to configure RS not to
> export with these rows and columns, so it'll start directly with my data?
> We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818
The best way to minimize file size when exporting to excel is to remove
as much formatting from the report design as possible. Remove any
borders, colors, etc. Still, SSRS will apply some formatting to the
resulting export and make it very difficult to match the more efficient
CSV export, (which is your best workaround IMO).
Ghost columns can be very difficult to remove completely, however,
paying attention to the left and right boundaries of your controls in
relation to other controls on your report can help.
For example, if you have a textbox at the top of your report holding a
report title, and then a table beneath which contains detail records,
making sure that the left and right sides of the textbox control align
vertically with the table control, or column boundaries in the table
control will minimize the occurrence of Ghost columns in the excel
export.
Basically, SSRS seems to evaluate each control boundary, and will
define all columns necessary to bound each control's right and left
side, (often merging columns as well which makes things a real mess
when you try to sort data in the spreadsheet).
SoftArtisans has a nice product call OfficeWriter that helps eliminate
the problem altogether. OfficeWriter allows you to define DataSets in
SSRS, and then use Excel to define where you want the data from the
datasets to land. This bypasses SSRS doing the formatting of the Excel
spreadsheet, which results in several desirable outcomes:
1) The memory drawdown on your SSRS box is drastically reduced when
the report is being run. Excel exports take a lot of memory to
complete!
2) The resulting file size is reduced because none of the formatting
codes are generated by SSRS which balloon the excel file size.
3) You bypass all the other formatting problems with merged
columns/rows
4) You get to use all the native Excel functionality in the resulting
report, including custom macros, etc.
But, it's not free!
Philip|||One other design point. You should not use textboxes at all. If you have a
textbox at the top of your report and then a table the user will not be able
to sort the data because of merged columns issue in Excel. I add extra
header rows to my table and put the report title there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PhilipL" <pal@.philiplee.com> wrote in message
news:1158092129.378116.36300@.b28g2000cwb.googlegroups.com...
> Offirh wrote:
>> I have an issue with exporting RS2005 reports to Excel:
>> 1) The exported fie size is disproportional to the rowset size - ~7000
>> records weigh 45 MB, whilst a CSV version of the same report weighs 7MB.
>> 2) RS exports to Excel with a redundant blank A column on the left and 4
>> rows that hold the report caption. Is there anyway to configure RS not to
>> export with these rows and columns, so it'll start directly with my data?
>> We have SSRS Version 9.00.1399.00 with SQL Server 2000 - 8.00.818
> The best way to minimize file size when exporting to excel is to remove
> as much formatting from the report design as possible. Remove any
> borders, colors, etc. Still, SSRS will apply some formatting to the
> resulting export and make it very difficult to match the more efficient
> CSV export, (which is your best workaround IMO).
> Ghost columns can be very difficult to remove completely, however,
> paying attention to the left and right boundaries of your controls in
> relation to other controls on your report can help.
> For example, if you have a textbox at the top of your report holding a
> report title, and then a table beneath which contains detail records,
> making sure that the left and right sides of the textbox control align
> vertically with the table control, or column boundaries in the table
> control will minimize the occurrence of Ghost columns in the excel
> export.
> Basically, SSRS seems to evaluate each control boundary, and will
> define all columns necessary to bound each control's right and left
> side, (often merging columns as well which makes things a real mess
> when you try to sort data in the spreadsheet).
> SoftArtisans has a nice product call OfficeWriter that helps eliminate
> the problem altogether. OfficeWriter allows you to define DataSets in
> SSRS, and then use Excel to define where you want the data from the
> datasets to land. This bypasses SSRS doing the formatting of the Excel
> spreadsheet, which results in several desirable outcomes:
> 1) The memory drawdown on your SSRS box is drastically reduced when
> the report is being run. Excel exports take a lot of memory to
> complete!
> 2) The resulting file size is reduced because none of the formatting
> codes are generated by SSRS which balloon the excel file size.
> 3) You bypass all the other formatting problems with merged
> columns/rows
> 4) You get to use all the native Excel functionality in the resulting
> report, including custom macros, etc.
> But, it's not free!
> Philip
>sql

rs exporting to excel problems

Good day,

I have a report designed in VS2005 deployed. The report opens fine through reporting services but when i try to export it to Excel format the numbers are being stored as text. I need the numbers to be exported in number format. How do I achieve this? Thanks

Hello,

You can use Format Property of that textbox(Set it to any Number format).