Export to PDF

i'm experiencing the same problem with missing headers when exporting to pdf
and i'm using version 2000.
do you have a solution to this problem?Is it missing headers on just the first page? or all pages?

Export to Excel problem in RS (updated)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel - Rare bug with formulas?

Hi,

I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.

Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:

Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value

This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.

99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...

I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.

Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)

Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.

And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.

Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.

*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...

Thanks in advance for any advice

Bump Anyone have any ideas? Thanks.|||

Hmm, I dont know but I have a suggestion of what to maybe look at if you have not done so already.

Your expression has ReportItems.TextBox1, ReportItems.TextBox2, etc

You might want to check that these items exist and are not duplicated or being created in a different fashion, etc

If creating the reports in report designer or similar then the names for textboxes will get automatically generated so maybe they are getting generated differently for different reports?

Try giving the textboxes that you want to use names that the report designer does not use, like Column1, Column2, etc

Or even better that actual column names that matches your data

See if that has an affect, maybe an automatically created item is taking up that name and so therefore the values are coming from the wrong text box.

Just an idea?

|||

You are referring to textboxes for its values in the same row. I'm sure those textboxes have some expressions, say Fields!Field1.Value. So, why dont you use this expression in place of your report item value? This way, your report will be foolproof.

Shyam

|||

I have had the same problem. The only solution that i have found is to perform the calculation in a function into the code of report.

For example:

Public Function TitleSub(ByVal strValue As String) As String

Dim strRet As String

If Len(strValue) = 0 Then

strRet = "Employee"

ElseIf strValue = "Owner" Then

strRet = "President"

Else

strRet = strValue

End If

Return strRet

End Function

In the "Expression" invoke the function:

=Code.TitleSub(Fields!Title.Value)

Export to Excel - Rare bug with formulas?

Hi,

I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.

Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:

Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value

This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.

99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...

I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.

Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)

Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.

And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.

Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.

*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...

Thanks in advance for any advice

Bump Anyone have any ideas? Thanks.|||

Hmm, I dont know but I have a suggestion of what to maybe look at if you have not done so already.

Your expression has ReportItems.TextBox1, ReportItems.TextBox2, etc

You might want to check that these items exist and are not duplicated or being created in a different fashion, etc

If creating the reports in report designer or similar then the names for textboxes will get automatically generated so maybe they are getting generated differently for different reports?

Try giving the textboxes that you want to use names that the report designer does not use, like Column1, Column2, etc

Or even better that actual column names that matches your data

See if that has an affect, maybe an automatically created item is taking up that name and so therefore the values are coming from the wrong text box.

Just an idea?

|||

You are referring to textboxes for its values in the same row. I'm sure those textboxes have some expressions, say Fields!Field1.Value. So, why dont you use this expression in place of your report item value? This way, your report will be foolproof.

Shyam

|||

I have had the same problem. The only solution that i have found is to perform the calculation in a function into the code of report.

For example:

Public Function TitleSub(ByVal strValue As String) As String

Dim strRet As String

If Len(strValue) = 0 Then

strRet = "Employee"

ElseIf strValue = "Owner" Then

strRet = "President"

Else

strRet = strValue

End If

Return strRet

End Function

In the "Expression" invoke the function:

=Code.TitleSub(Fields!Title.Value)

export to excel

I'm experiencing an issue where when I export my report to

excel every number in my report exports as something other than a number. Every

number excel field (cell) has a green arrow in the upper left corner which will allow

me to manually convert to a number but this is a major hassle.

I've tried converting the fields in the report using cdbl and cdec but to no

avail.

Anyone have a suggestion on how to fix this?

Thanks

The issue seems to be caused by the formatnumber() function. Doe formatnumber by default change the data's type by default?|||No one else has experienced this issue?|||

The FormatNumber function always returns a String. See this doc page for reference:

http://msdn2.microsoft.com/en-us/library/xfta99yt.aspx

If you want to add number formatting and retain the original numeric data type, you should define the formatting in RDL. Open the properties window for the Textbox and use the Format tab.

-Chris

Export Slow from SQL Reporting Service

We have users experiencing very slow to export SQL Reporting Service 2005
rendered data to EXCEL. Is there any way to improve the performance?
BrianOn Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian
Usually this is due to either high activity on the web server that the
reports are on -or- that the query or stored procedure sourcing the
report needs to be revised to improve performance. I would suggest
using the Database Engine Tuning Adviser against the stored procedure
or query to implement indexes, etc to improve the performance. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hello,
What about exporting to other formats like PDF or MHTML. Is it very slow
too?
How complicated are these reports and how much data do they contain?
Sometimes the reason of slow exporting to Excel is complex structure of
reports (a lot of cells, nested objects, etc.).
If reports contain a lot of hidden rows all of them are saving to Excel file
(even if they are unseen during exporting).
Maybe this is the reason.
Regards,
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian|||Thank you for the quick response.
The SQL Reporting 2005 report is a simple report which has no complex
structure in it. It is a single line type of report. However, it has the
company logo image on the title of the report. The report rendered within
one minute with 123 pages. When I export to EXCEL, it was ten times slower
then the web page rendered.
Does export require SQL to query the data again or it gathered the rendered
data to EXCEL?
Would increase SQL memory from dynamic to static with 3GB help? How about
caching the report?
Thanks,
Brian
"Radoslaw Lebkowski" wrote:
> Hello,
> What about exporting to other formats like PDF or MHTML. Is it very slow
> too?
> How complicated are these reports and how much data do they contain?
> Sometimes the reason of slow exporting to Excel is complex structure of
> reports (a lot of cells, nested objects, etc.).
> If reports contain a lot of hidden rows all of them are saving to Excel file
> (even if they are unseen during exporting).
> Maybe this is the reason.
>
> Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
>
>|||By changing the export from EXCEL to PDF, It does not make any difference.
When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
export the data to EXCEL and it is much slower.
Brian
"EMartinez" wrote:
> On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
> Usually this is due to either high activity on the web server that the
> reports are on -or- that the query or stored procedure sourcing the
> report needs to be revised to improve performance. I would suggest
> using the Database Engine Tuning Adviser against the stored procedure
> or query to implement indexes, etc to improve the performance. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 10, 11:25 am, Brian <B...@.discussions.microsoft.com> wrote:
> By changing the export from EXCEL to PDF, It does not make any difference.
> When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
> export the data to EXCEL and it is much slower.
> Brian
> "EMartinez" wrote:
> > On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > > We have users experiencing very slow to export SQL Reporting Service 2005
> > > rendered data to EXCEL. Is there any way to improve the performance?
> > > Brian
> > Usually this is due to either high activity on the web server that the
> > reports are on -or- that the query or stored procedure sourcing the
> > report needs to be revised to improve performance. I would suggest
> > using the Database Engine Tuning Adviser against the stored procedure
> > or query to implement indexes, etc to improve the performance. Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
If the image is large or there is a lot of report data to export, this
may be the reason. Have you tried the Database Engine Tuning Advisor
yet? I think that caching the report might improve the performance and
response time.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||If you render previous executed reports to other output format, it doesn't
require regathering data from datasources.
Rendering process uses intermediate format of these reports stored in RS
cache.
Rendering to output format uses SQL Server only for Report Processing
Extensions (not for executing SQL queries from datasources).
Rendering reports to PDF or Excel are the slowest operations whereas
exporting to HTML and CSV are the fastests methods.
I've heard a lot of complaints for slow rendering to PDF and Excel. It's
very common situation.
To improve speed of rendering reports try to find server's bottlenecks.
Maybe it's a CPU or server memory.
Try to use SQL Server Profiler to measure CPU usage during rendering to
different formats.
I hope it will be helpful.
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||If it is much data at all then what you should do is have your users export
via CSV. It will still open it up in Excel. One other point, however. The
default CSV format is Unicode which Excel doesn't know how to handle (at
least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
2005 you can change a configuration setting that causing CSV exports to be
in ASCII. CSV and HTML rendering is about the same performance wise.
As a test just try it. Excel will (I believe) put all the data in a single
column and then you have to use the menu in Excel to split the data out into
multiple columns.
I am gone next week to the MVP Summit in Seattle. All the newsgroups will
see very little MVP involvement for the next week.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||Exporting to CSV helps and improve the export time. Let's hope Microsoft
will improve the export process to EXCEL in the future.
Thanks,
Brian
"Bruce L-C [MVP]" wrote:
> If it is much data at all then what you should do is have your users export
> via CSV. It will still open it up in Excel. One other point, however. The
> default CSV format is Unicode which Excel doesn't know how to handle (at
> least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
> 2005 you can change a configuration setting that causing CSV exports to be
> in ASCII. CSV and HTML rendering is about the same performance wise.
> As a test just try it. Excel will (I believe) put all the data in a single
> column and then you have to use the menu in Excel to split the data out into
> multiple columns.
> I am gone next week to the MVP Summit in Seattle. All the newsgroups will
> see very little MVP involvement for the next week.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> > Thank you for the quick response.
> >
> > The SQL Reporting 2005 report is a simple report which has no complex
> > structure in it. It is a single line type of report. However, it has the
> > company logo image on the title of the report. The report rendered within
> > one minute with 123 pages. When I export to EXCEL, it was ten times
> > slower
> > then the web page rendered.
> >
> > Does export require SQL to query the data again or it gathered the
> > rendered
> > data to EXCEL?
> >
> > Would increase SQL memory from dynamic to static with 3GB help? How about
> > caching the report?
> >
> > Thanks,
> > Brian
> >
> >
> >
> > "Radoslaw Lebkowski" wrote:
> >
> >> Hello,
> >> What about exporting to other formats like PDF or MHTML. Is it very slow
> >> too?
> >> How complicated are these reports and how much data do they contain?
> >> Sometimes the reason of slow exporting to Excel is complex structure of
> >> reports (a lot of cells, nested objects, etc.).
> >> If reports contain a lot of hidden rows all of them are saving to Excel
> >> file
> >> (even if they are unseen during exporting).
> >> Maybe this is the reason.
> >>
> >>
> >> Regards,
> >> Radoslaw Lebkowski
> >>
> >>
> >>
> >> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> >> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> >> > We have users experiencing very slow to export SQL Reporting Service
> >> > 2005
> >> > rendered data to EXCEL. Is there any way to improve the performance?
> >> >
> >> > Brian
> >>
> >>
> >>
>
>