report works in VS2003, but not in IE

I have a dirt-simple report that will render perfectly in the IDE, but when
it is deployed and run in the browser, I get the following error:
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Index was out of range. Must be non-negative and less than the size of the
collection. Parameter name: index
The report consists of a single textbox and two Line objects. The textbox
receives its value from a query that returns a single field. I have applied
the SSRS SP1.
Other reports work fine. For some reason this simple one fails. Why? I
can post the RDL upon request.Do you have any expressions in your report?
Andy Potter|||"Potter" wrote:
> Do you have any expressions in your report?
> Andy Potter
No, here's the only report item:
<ReportItems>
<Textbox Name="Notes">
<Style>
<PaddingLeft>0in</PaddingLeft>
<BorderColor>
<Default>#000000</Default>
</BorderColor>
<BorderStyle>
<Default>Dashed</Default>
</BorderStyle>
<FontSize>8pt</FontSize>
<LineHeight>13pt</LineHeight>
<Color>#000000</Color>
<PaddingBottom>0in</PaddingBottom>
<PaddingTop>0in</PaddingTop>
<PaddingRight>0in</PaddingRight>
</Style>
<Height>0.2083in</Height>
<Width>10in</Width>
<CanGrow>true</CanGrow>
<CanShrink>true</CanShrink>
<Value>=Fields!RSNotes.Value</Value>
</Textbox>
</ReportItems>
---
The field does need to wrap to multiple lines to display the "RSNotes" text
from the db. The above XML is nested inside <List></List> tags, which is the
only thing inside the Body tags.|||Any parameters or calculated fields?|||"Potter" wrote:
> Any parameters or calculated fields?
No calculated fields. There is one report parameter, "nRecordID", which is
used in the TSQL query as "...where RecordID = @.nRecordID"|||Unusual. Most of the references to this error revolve around exporting
to Excel. While the SP2 readme doesn't identify this as a resolved
issue, it might be worth bumping up to the move recent service pack.
Andy POtter|||"Potter" wrote:
> Unusual. Most of the references to this error revolve around exporting
> to Excel. While the SP2 readme doesn't identify this as a resolved
> issue, it might be worth bumping up to the move recent service pack.
I should have corrected my first post to state we are using SSRS2K SP2. It
is strange that this simplest report fails where much more complex ones are
OK. The report was imported from an Access2000 .mdb file. I will try
recreating it from scratch to see what happens. If all else fails I'll
install a trial of SSRS 05 on a different server and try it there.|||I've been able to reproduce the error. Starting from an empty report, the
body would deploy and print OK. When I added the pageheader from the
imported report, I got the "index out of range" error. Here is the
pageheader that was generated by the "Import report from Access" function:
<PageHeader>
<ReportItems>
<Line Name="Line8">
<Width>0in</Width>
<Style>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>#000000</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Line>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
<Style>
<BackgroundColor>#ffffff</BackgroundColor>
</Style>
<Height>0in</Height>
</PageHeader>
With this pageheader removed, the report works. It would seem the import
code does not always produce valid reports, even though the pageheader does
render without error in the IDE.sql

Report wont render

I am having an issue with a particular report not rendering when
displaying in the web browser from reporting services web site. The
report never displays and if you check under site settings the job is
showing that it is in an Action of "Render". Has anyone else ever seen
this? Is there something I should look for in one of the log files to
help me find out what is causing this error?
Thanks ahead of time.sfibich wrote:
> I am having an issue with a particular report not rendering when
> displaying in the web browser from reporting services web site. The
> report never displays and if you check under site settings the job is
> showing that it is in an Action of "Render". Has anyone else ever seen
> this? Is there something I should look for in one of the log files to
> help me find out what is causing this error?
> Thanks ahead of time.
A litte more infomation. I get the following message in the browser:
NOTE: I did not cancel the query.
Reporting Services Error
----
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot read the next data row for the data set pfg_dw.
(rsErrorReadingNextDataRow) Get Online Help
Operation cancelled by user.
----
Microsoft Reporting Services|||This looks like an IIS timeout issue. Try increasing the IIS timeout on the
report server: Computer Management - Internet Information Services - Web
Sites - Default Web Site: Right-click properties: Connection timeout.
IIS 6 has a default connection timeout of 120 seconds (while IIS 5 has 900
seconds).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:OdTAv8p8EHA.1400@.TK2MSFTNGP11.phx.gbl...
> sfibich wrote:
> > I am having an issue with a particular report not rendering when
> > displaying in the web browser from reporting services web site. The
> > report never displays and if you check under site settings the job is
> > showing that it is in an Action of "Render". Has anyone else ever seen
> > this? Is there something I should look for in one of the log files to
> > help me find out what is causing this error?
> >
> > Thanks ahead of time.
> A litte more infomation. I get the following message in the browser:
> NOTE: I did not cancel the query.
> Reporting Services Error
> ----
--
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot read the next data row for the data set pfg_dw.
> (rsErrorReadingNextDataRow) Get Online Help
> Operation cancelled by user.
> ----
--
> Microsoft Reporting Services
>sql

Report Title in Internet Explorer Tab

Is there a way to show the report title in an Internet Explorer tab instead
of having it render "Report Viewer"? Seems that users are opening many
reports and it is hard to find the one you want with many windows open. Any
Thoughts?
Thanks
RickOn Nov 2, 1:41 pm, Rick Spicknall
<RickSpickn...@.discussions.microsoft.com> wrote:
> Is there a way to show the report title in an Internet Explorer tab instead
> of having it render "Report Viewer"? Seems that users are opening many
> reports and it is hard to find the one you want with many windows open. Any
> Thoughts?
> Thanks
> Rick
As far as I know, there is not. The closest thing you will be able to
do is export the report(s) to Web Archive (MHTML) and the report name
itself will appear in the IE tab. Of course, if you are willing to
render the reports to web archive programmatically, you can build an
ASP.NET application that allows you to append HTML to the web archive
output (via possibly: Streamreader, Stringbuilder.Append and
Streamwriter) and try to create the IE tabs from the Report Title
information. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report taking forever to render

Hi All,

This question pertains to sql server reporting services 2005.

I have a report in which i am using a stored procedure to populate the dataset. However if i run that report in the IDE or the browser it takes forever to run and nothing happens even after 30 min.

I thought something was wrong with the stored procedure so i ran it separately in the SQL server management and it runs in about 25 secs.

I am clueless why it is behaving like this.

Any suggestion will be appreciated.

HI,abhi81 :

Here are a few things to check:
- Make sure that you're report project Datasource is setup correctly
(i.e., has the correct connection string, etc).
- Make sure that if you are passing parameters to your stored
procedure/query, that you tie them correctly in the dataset (via the
Data tab -> Edit Dataset button [...] -> Parameters tab).
- If you are using report parameters, make sure that your datasets are
not erroring out for those parameters.
- Lastly, make sure that you have the latest service pack for SQL
Server.

And you can try to use a simply store procedure and make sure it is not the problem related to your comlicated one.
Hope this helps.

Report taking a long time to render

I have a somewhat complex query that takes about 2 seconds to run in
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.

Report Snapshot creation fails on large result set

Hello world,
I have some reports that run fine when I use the "Render this report with the
most recent data" selected, but when I want to create a Snapshot it fails to
produce a snapshot. It also depends on how much data I select via the
parameters. If I want a small result set it works, but if I select a large
set it displayes several (3) times the windows login popup (asking my
password each time) and in the end it shows a "You are not authorized to view
this page" error.
The large selection runs for about 7 minutes, the smaller about 2 and does
NOT show the login popup.
The server is in another domain than my login but it is in the "Local
Intranet" in the IE security setup and uses "Automatic logon with current
user credentials"
I suspect it is a recource problem, but I have no idea what causes this
problem.
Can you please help me?
Regards, JanHi,
to answer my own question after wasting a lot of time chasing wrong
leads ;-)
It was the IIS connection-timeout of the report manager; default 120
sec. If the query ran longer than that, it failed. I set it to 15
minutes and now all snapshots are created just fine.
Regards,
Jan Lubbers via SQLMonster.com schreef:
> Hello world,
> I have some reports that run fine when I use the "Render this report with the
> most recent data" selected, but when I want to create a Snapshot it fails to
> produce a snapshot. It also depends on how much data I select via the
> parameters. If I want a small result set it works, but if I select a large
> set it displayes several (3) times the windows login popup (asking my
> password each time) and in the end it shows a "You are not authorized to view
> this page" error.
> The large selection runs for about 7 minutes, the smaller about 2 and does
> NOT show the login popup.
> The server is in another domain than my login but it is in the "Local
> Intranet" in the IE security setup and uses "Automatic logon with current
> user credentials"
> I suspect it is a recource problem, but I have no idea what causes this
> problem.
> Can you please help me?
> Regards, Jan|||Hi,
to answer my own question after wasting a lot of time chasing wrong
leads ;-)
It was the IIS connection-timeout of the report manager; default 120
sec. If the query ran longer than that, it failed. I set it to 15
minutes and now all snapshots are created just fine.
Regards,
Jan Lubbers via SQLMonster.com schreef:
> Hello world,
> I have some reports that run fine when I use the "Render this report with the
> most recent data" selected, but when I want to create a Snapshot it fails to
> produce a snapshot. It also depends on how much data I select via the
> parameters. If I want a small result set it works, but if I select a large
> set it displayes several (3) times the windows login popup (asking my
> password each time) and in the end it shows a "You are not authorized to view
> this page" error.
> The large selection runs for about 7 minutes, the smaller about 2 and does
> NOT show the login popup.
> The server is in another domain than my login but it is in the "Local
> Intranet" in the IE security setup and uses "Automatic logon with current
> user credentials"
> I suspect it is a recource problem, but I have no idea what causes this
> problem.
> Can you please help me?
> Regards, Jan

Report Server Execution Log (Retrieve, Process, Render

The online help states under ther Report Server Execution Log the following
description for the Retrieve, Process, Render counters:
"Percentage of time spent retrieving the data, processing the report, and
rendering the report."
Shouldn't the three numbers added together match to 100%? What units are
these values depicted in?
In order to calculate duration in seconds I have taken the datediff in
seconds between starttime and endtime. I would assume there is a way to
calculate percent of time for retrieval, processing and rendering from this
value. Is this correct?
--
Thanks in Advance,
John KThis is a bug in the documentation. Thanks for pointing this out.
The values represent milli seconds and not percentage.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:2CF19C95-BCCA-454D-BF31-F624215B4A6D@.microsoft.com...
> The online help states under ther Report Server Execution Log the
> following
> description for the Retrieve, Process, Render counters:
> "Percentage of time spent retrieving the data, processing the report, and
> rendering the report."
> Shouldn't the three numbers added together match to 100%? What units are
> these values depicted in?
> In order to calculate duration in seconds I have taken the datediff in
> seconds between starttime and endtime. I would assume there is a way to
> calculate percent of time for retrieval, processing and rendering from
> this
> value. Is this correct?
>
> --
> Thanks in Advance,
> John K

report renders manually; subscription doesn't work

I can manually render and view a report, but my subscription to the same
report fails with this error in the log file:
ReportingServicesService!library!1210!07/19/2005-15:20:55:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
ReportingServicesService!library!1210!07/19/2005-15:20:55:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!emailextension!1210!07/19/2005-15:20:55:: Error
sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
The Report Server has encountered a configuration error; more details in the
log files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String
userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext
reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32&
executionOption, String& savedParametersXml, ReportSnapshot&
compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime&
historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum
type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters,
String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!notification!1210!07/19/2005-15:20:55::
Notification e07ed229-a912-4843-b66f-fb15c2af2596 completed. Success: False,
Status: Failure sending mail: The Report Server has encountered a
configuration error; more details in the log files, DeliveryExtension: Report
Server Email, Report: <report name>, Attempt 0
I have verified that the SMTP service is working, and there is no
undelivered mail in the mailroot.
Any ideas?
--
Berry Morgan
JSOHi Berry,
Based on my experience, the KB article 834859 probably applies to the issue
related to email delivery on your computer. As mentioined in the KB 834859,
there is a known issue that AuthzInitializeContextFromSid function passes a
non-null-terminated string to the DsGetDcName function. Apparently this
issue potentially cause problems when Reporting Services tries to use this
function.
To resolve this problem immediately, please contact Microsoft Product
Support Services to obtain the hotfix. For a complete list of Microsoft
Product Support Services phone numbers and information about support costs,
visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Note In special cases, charges that are ordinarily incurred for support
calls may be canceled if a Microsoft Support Professional determines that a
specific update will resolve your problem. The usual support costs will
apply to additional support questions and issues that do not qualify for
the specific update in question.
You may also want to apply Windows 2003 SP1 which includes this hotfix.
If the issue persists, I suggest that you refer to the following article to
troubleshoot the issue:
842423 A call to the AuthzInitializeContextFromSid API function may fail
during
http://support.microsoft.com/?id=842423
If anything is unclear, feel free to let us know.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Report renderer as class library?

For those of us who want to integrate reports in to an existing web
application, it would be very convenient if one could render reports
using a class library rather than via a web service. Does anyone know
if there are any plans to enable this in future releases of Reporting
Services?Visual Studio 2005 will contain an RS Webforms control which should address
your requirement. The control can process and render reports locally without
the need to connect to a report server.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Damian Cugley" <pdc@.oxfordcc.co.uk> wrote in message
news:eZOJ2DhNFHA.3420@.tk2msftngp13.phx.gbl...
> For those of us who want to integrate reports in to an existing web
> application, it would be very convenient if one could render reports using
> a class library rather than via a web service. Does anyone know if there
> are any plans to enable this in future releases of Reporting Services?

Report render to PDF hangs

I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.

I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.

Is anyone else finding problems like this? They don't seem to be code related.

Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?

Thanks for any info

There is no easy to define limit to how "much" of a report can be rendered to PDF. It depends on size and complexity of the report and muscle of the server(s). Can you describe the hardware a bit? If you capture a perfmon while the hang is occuring, how does CPU, memory, disk, and network look? Also, is this reproducible? If you revert the change, does the report export?

Thank you.

|||

The machines running are 4 processor 3.2Ghz zeon's with 3GB Ram.

We didn't run perfmon, but normally get paged if there are performance issues.

I am able to reproduce this problem. I narrowed the recordset down to a particular agency, a subset of the full recordset The subset recordset contains 59 rows for the main report and 20 rows for the subreport, so it's not a large amount of data. We have agencies that are many times more complicated that print fine.

Strangely enough, I got it to work, when I pushed off the totals section to a subreport. That section had 2 running sums in it and some expressions based on data fields. I passed those over to the subreport as parameters and it worked fine.

This is not the only time, we've had this hang and had to look for a workaround. I also had a problem like this when I merged some columns in a table. The problem was happening to a completely different agency than before. Now when I develop in this report, I do one thing at a time, then deploy and run to be sure I didn't introduce another hang situation.

Is this behavior common to reporting services?

|||

If the dataset is large, the report is complex, the hardware is underpowered, or some combination of the three, exporting to PDF can get bogged down, even in SQL Server 2005 SP2. One way to figure out what's going on is to look at the ExecutionLog table in the ReportServer database.

Execute the report and then look at the last 5 columns. If RowCount is large, consider pushing off some data filtering into the TSQL - this should also decrease TimeDataRetrieval. If TimeProcessing is large, see if there is anything you're doing in the report that can be better handled by SQL server - like, sorting or aggregation. Also, try and reduce the complexity of any expressions you have in the report. If TimeRendering is large, it could be you're running into inefficiencies in the rendering engine. We've made good progress and improved perf in RS 2005 but we're still not where I'd like us to be so have more work to do. If ByteCount is large, consider breaking the report up so users don't have to deal with 50M PDF files. If archival is your goal, you might consider a different format like HTML that is much easier to grep later than PDF. Tudor has some suggestions as well on http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx.

"Hanging" is not common, though slow rendering when there's a lot to do or not enough hardware to do it on is more common.

I don't understand why merging columns would make that much of an impact, however. If you have a repro, please open a bug so I can take a look. If you can't get to http://connect.microsoft.com, you can email me the details directly (click on my name to the left and you'll find my email address).

Thank you.

|||

Hi Donovan,

Thanks for your help.

I've got a question still, I'm not sure that this is bug ticket worthy so I figured I'll run it by you here first.

This is from the ExecutionLog table.

This report was successful. This record is the full recordset and produced. I moved a totals section to a sub report to get it to work.

RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount

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

0 PDF 2007-03-09 10:14:42.723 2007-03-09 10:22:30.500 19070 269112 179356 1 rsSuccess 8969543 8569

This recordset here, is one that didn't work. This code is prior to me moving the totals section out to a subreport. Also, this recordset is a subset, just one agency and it's associated records. After a half hour I cancelled the job.

The TimeRendering is very large and the TimeDataRetrieval also seems large for that recordset.

RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount

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

0 PDF 2007-03-08 10:47:09.303 2007-03-08 11:15:53.803 20642 2690 1700930 1 rsJobWasCanceled 0 131

The difference between the 2 versions of code, is that the one that's failing has 3 footer rows for the same grouping, 4 of the text boxes contain =FormatCurrency(Fields!XYZ.Value) type values and one contains an =IIF(Fields!CompType.Value = "SA", "XYZ Based Compensation", "ABC Based Compensation") + " Totals For This Period:"

I replaced those 3 footers, with one footer that calls a subreport, ironically, the sub reports parameters contains the same formatcurrency items and the iif.

I'm not sure what to think, it looks like a bug, but wouldn't TimeDataRetrieval indicate database side inefficiency?

What do you think?

Scott

|||

That doesn't sound right - time rendering seems too high. Would you please email me a copy of the rdls so I can see what's happening in the code?

The TIFF and PDF renderer share a lot of the same code. Is TIFF also that slow in exporting that report?

Regarding the TimeDataRetrieval column, if you take the queries out of the reports and run them against the server manually, how do the times compare?

Thank you.

Report render to PDF hangs

I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.

I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.

Is anyone else finding problems like this? They don't seem to be code related.

Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?

Thanks for any info

There is no easy to define limit to how "much" of a report can be rendered to PDF. It depends on size and complexity of the report and muscle of the server(s). Can you describe the hardware a bit? If you capture a perfmon while the hang is occuring, how does CPU, memory, disk, and network look? Also, is this reproducible? If you revert the change, does the report export?

Thank you.

|||

The machines running are 4 processor 3.2Ghz zeon's with 3GB Ram.

We didn't run perfmon, but normally get paged if there are performance issues.

I am able to reproduce this problem. I narrowed the recordset down to a particular agency, a subset of the full recordset The subset recordset contains 59 rows for the main report and 20 rows for the subreport, so it's not a large amount of data. We have agencies that are many times more complicated that print fine.

Strangely enough, I got it to work, when I pushed off the totals section to a subreport. That section had 2 running sums in it and some expressions based on data fields. I passed those over to the subreport as parameters and it worked fine.

This is not the only time, we've had this hang and had to look for a workaround. I also had a problem like this when I merged some columns in a table. The problem was happening to a completely different agency than before. Now when I develop in this report, I do one thing at a time, then deploy and run to be sure I didn't introduce another hang situation.

Is this behavior common to reporting services?

|||

If the dataset is large, the report is complex, the hardware is underpowered, or some combination of the three, exporting to PDF can get bogged down, even in SQL Server 2005 SP2. One way to figure out what's going on is to look at the ExecutionLog table in the ReportServer database.

Execute the report and then look at the last 5 columns. If RowCount is large, consider pushing off some data filtering into the TSQL - this should also decrease TimeDataRetrieval. If TimeProcessing is large, see if there is anything you're doing in the report that can be better handled by SQL server - like, sorting or aggregation. Also, try and reduce the complexity of any expressions you have in the report. If TimeRendering is large, it could be you're running into inefficiencies in the rendering engine. We've made good progress and improved perf in RS 2005 but we're still not where I'd like us to be so have more work to do. If ByteCount is large, consider breaking the report up so users don't have to deal with 50M PDF files. If archival is your goal, you might consider a different format like HTML that is much easier to grep later than PDF. Tudor has some suggestions as well on http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx.

"Hanging" is not common, though slow rendering when there's a lot to do or not enough hardware to do it on is more common.

I don't understand why merging columns would make that much of an impact, however. If you have a repro, please open a bug so I can take a look. If you can't get to http://connect.microsoft.com, you can email me the details directly (click on my name to the left and you'll find my email address).

Thank you.

|||

Hi Donovan,

Thanks for your help.

I've got a question still, I'm not sure that this is bug ticket worthy so I figured I'll run it by you here first.

This is from the ExecutionLog table.

This report was successful. This record is the full recordset and produced. I moved a totals section to a sub report to get it to work.

RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount -- -- -- -- -- - -- 0 PDF 2007-03-09 10:14:42.723 2007-03-09 10:22:30.500 19070 269112 179356 1 rsSuccess 8969543 8569

This recordset here, is one that didn't work. This code is prior to me moving the totals section out to a subreport. Also, this recordset is a subset, just one agency and it's associated records. After a half hour I cancelled the job.

The TimeRendering is very large and the TimeDataRetrieval also seems large for that recordset.

RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount -- - -- -- -- -- - - -- 0 PDF 2007-03-08 10:47:09.303 2007-03-08 11:15:53.803 20642 2690 1700930 1 rsJobWasCanceled 0 131

The difference between the 2 versions of code, is that the one that's failing has 3 footer rows for the same grouping, 4 of the text boxes contain =FormatCurrency(Fields!XYZ.Value) type values and one contains an =IIF(Fields!CompType.Value = "SA", "XYZ Based Compensation", "ABC Based Compensation") + " Totals For This Period:"

I replaced those 3 footers, with one footer that calls a subreport, ironically, the sub reports parameters contains the same formatcurrency items and the iif.

I'm not sure what to think, it looks like a bug, but wouldn't TimeDataRetrieval indicate database side inefficiency?

What do you think?

Scott

|||

That doesn't sound right - time rendering seems too high. Would you please email me a copy of the rdls so I can see what's happening in the code?

The TIFF and PDF renderer share a lot of the same code. Is TIFF also that slow in exporting that report?

Regarding the TimeDataRetrieval column, if you take the queries out of the reports and run them against the server manually, how do the times compare?

Thank you.

Report Render in VS2005 slower than stored proc

I have searched many forums and found some cases of people also reporting slower rendering in RS than in Management Studio / Query Analyzer. However, none of the other solution suggestions seem to make a difference for me.

I'm a VS/VB developer and have got multiple reports built -- all using stored procedures on the backend -- that all take many times longer to run than if executed via Management Studio (SSMS). My simplest proc takes a couple of parameters (no defaults included) and does a simple select against one table with a few joins. Nothing complicated. It runs in 8 secs for 6867 rows via SSMS. Through RS (running locally through Visual Studio 2005 at this point) it takes around 25-28 secs. Yet, when I'm in the report on the DATA tab (not the PREVIEW tab) the run takes the expected 8 secs ?!?!

All reports are behaving this way.

I am not using cursors.

I have no default values on parameters.

I have added the "WITH RECOMPILE" to the proc statement.

I have "SET NOCOUNT ON" as the first line of the proc.

I hate to say this, but I even connected the proc to Crystal Reports to see how it behaved. It ran in the expected 8 secs.

I've seen some mention by someone that perhaps this is a known issue of RS that it reads the proc twice. Any truth to this?

Also a couple posts have traced and demonstrated that the report is generating significantly more data "reads" via RS than through SSMS.

We're a shop that is considering a switch from Crystal to RS, but we do everything through stored procedures. I need to clear up this issue before I can go forward recommending a switch. I'm including a copy of a typical proc below for review... What am I missing? What's the deal here with RS?

IF OBJECT_ID('dbo.rpt_InactiveAccounts') IS NOT NULL

DROP PROCEDURE dbo.rpt_InactiveAccounts

GO

CREATE PROCEDURE dbo.rpt_InactiveAccounts

(@.pRunDate datetime

,@.pSalesperson varchar(5000)

,@.pIncludeOpen char(1)

)

WITH RECOMPILE

AS

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- CREATE/SETUP TEMP TABLE FOR USE IN PARSING MULTI-VALUED STRING INPUTS

DECLARE @.NumberPivot TABLE (NumberID INT PRIMARY KEY)

DECLARE @.intLoopCounter INT

SELECT @.intLoopCounter =0

WHILE @.intLoopCounter <=4999 BEGIN

INSERT INTO @.NumberPivot

VALUES (@.intLoopCounter)

SELECT @.intLoopCounter = @.intLoopCounter +1

END

-- CREATE TEMP TABLES TO HOLD PARSED VALUES FROM MULTI-VALUE STRING INPUT PARAMETERS

DECLARE @.SalespersonTable TABLE

(tmpSalesperson varchar(30))

-- PARSE OUT @.pSALESPERSON PARAMETER AND STORE VALUES IN TEMP TABLE

INSERT INTO @.SalespersonTable

SELECT SUBSTRING(',' + @.pSalesperson + ',', NumberID + 1,

CHARINDEX(',', ',' + @.pSalesperson + ',', NumberID + 1) - NumberID -1)

FROM @.NumberPivot

WHERE NumberID <= LEN(',' + @.pSalesperson + ',') - 1

AND SUBSTRING(',' + @.pSalesperson + ',', NumberID, 1) = ','

SELECT DISTINCT

CASE

WHEN s.Name IS NULL THEN '<< OPEN >>'

ELSE s.Name

END As SalespersonName

,c.ClassId

,c.CustId

,c.Name

,c.Addr1

,c.Addr2

,c.Addr3

,c.City

,State

,CASE

WHEN Len(c.Zip) = 9 And CharIndex(' ', c.Zip, 0) = 0 THEN Left(c.Zip, 5) + '-' + Right(c.Zip, 4)

ELSE c.Zip

END As Zip

,ac1.descr As Terms

,ac2.descr As Status

FROM

ACTCustomer c (NOLOCK)

LEFT OUTER JOIN CustSales cs ON c.CustId = cs.CustId

LEFT OUTER JOIN ACTSalesperson s ON cs.SlsId = s.SalesId

INNER JOIN @.SalespersonTable st ON s.Name = st.tmpSalesperson OR (s.Name IS NULL AND @.pIncludeOpen = 'Y')

INNER JOIN ACTCode ac1 ON ac1.Code = c.Terms And ac1.FieldId = 'CustTerms'

INNER JOIN ACTCode ac2 ON ac2.Code = c.Status And ac2.FieldId = 'Status'

WHERE

c.LastInvcDate <= @.pRunDate

And c.ClassId <> 'TR'

ORDER BY

SalespersonName, Name, CustId

Hi Dave,

I don't think that this will solve you performance issue but I have a recommendation for you, since you are passing some comma separated list to your reports as parameter, maybe you should create a user-defined function that will convert you list into a table. This would allow you to reuse this same function in all of you reports.

Sample Here:

Here is an example of a function that would convert comma separated list into a table:

http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html

Then you could modify your SQL statement in the stored procedure tu use this function, something like this should do:

SELECT <Fields>

FROM <Table> a

JOIN <Function> (@.CommaSeparatedList) b ON b.uid = a.id

For the performance issue, did you try to profile and compare your SSMS execution and the Report execution?

HTH,

Report Render Format Excel: name on each Sheet

Hello;

There is any way to put a name to each sheet when using Render Format to Excel file?

Thanks

Luis

Quick answer: this is currently not supported.

You might want to review the following related threads:

* http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1713177&SiteID=1

* http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=309746&SiteID=1

-- Robert