Reporting Services seems to requery or refresh during drilldown

I have a report in reporting services with a single level of grouping:
Order
> Items
When I return the result the report is fine, but if I try to drill down into
an order, the screen flashes (as in a refresh), and then pushes the item I
opened to the top of the screen. It seems to be re-querying the database on
every click into the drilldown. Even though the result set is not large,
it's drawing it from a production database, and it takes several seconds to
complete the query.
Is it requerying, or is there a display issue? We're in the early stages of
using reporting services and it's currently running on an underpowered
machine for the server. Still, the result set has already been returned, and
shouldn't have to be drawn from the DB every time you drill down into a
single order.
Any ideas?I'm pretty sure it is a display issue. Just look at the HTML source code.
Every time you click into the drilldown, it does a postback to the server
and executing some "hide and show" code for the report.
The only time a query is send to the database is when you see the
"Generating Report" image.
"Ben Graham" <BenGraham@.discussions.microsoft.com> a écrit dans le message
de news:96761387-77EB-48C4-A40D-889ABD3521C9@.microsoft.com...
> I have a report in reporting services with a single level of grouping:
> Order
> > Items
> When I return the result the report is fine, but if I try to drill down
into
> an order, the screen flashes (as in a refresh), and then pushes the item I
> opened to the top of the screen. It seems to be re-querying the database
on
> every click into the drilldown. Even though the result set is not large,
> it's drawing it from a production database, and it takes several seconds
to
> complete the query.
> Is it requerying, or is there a display issue? We're in the early stages
of
> using reporting services and it's currently running on an underpowered
> machine for the server. Still, the result set has already been returned,
and
> shouldn't have to be drawn from the DB every time you drill down into a
> single order.
> Any ideas?
>

reporting services parameter problem

I have a report that uses as available values for a parameter a query. My problem is that the query return a list of available months, however, sometimes, i would like the user to choose null when they want to see all months in the report. but the query does not have a month null so null is not allowed.
Is there anything I can do? I cannot add a null record in the table itself.
thank you, dreyYou can either allow null for a parameter or use a UNION to add All Months to the available options. (Adding All Months tends to give a better end-user experience.) Note that the following query (because of the UNION) isn't valid in the graphical query designer. You'll be prompted to switch to the generic query designer. You'll have to modify the query to match your particular DB schema.

SELECT MonthNumber, MonthName
FROM Months
UNION
SELECT -1, 'All Months'

Now you've got your parameter values populated. If you're using the 'All Months' option, you'll want to uncheck "Allow null values".

You should now modify your dataset query to:

SELECT column1, column2, column3
FROM table1
WHERE (MonthNumber = @.MonthNumber OR @.MonthNumber = -1)

If you select a month, you will get the appropriate data back for that month. If you select All Months, then MonthNumber = -1 will always be false (since -1 isn't a valid month number), but the second part will evaluate as true for all months.

Hope that helps.

Reporting services fail to return large report

I have a huge report (approx. 120MB) needs to be returned from reporting
services. However, most of the time the request die before the report is
returned.
While I am debugging this problem, I realize that an exception is thrown
between the BeginRender() and EndRender() methods of Reporting Service.
Exception:
Unable to read data from the transport connection.
I am not sure if there is any limit in report size that can be
generated/transported from reporting service. Reporting service is called
from another ASP.Net web service. This web service also resides in the same
box as reporting service and SQL server 2000. This box is running Win 2003
and IIS 6(IIS 5 isolation mode).
Here is the code snippet:
----
ReportingService oRS = CreateReportService(sRSession);
IAsyncResult pWait = oRS.BeginRender(m_sReport, sFormatName, null,
sFormatInfo, aParams, null, null, null, null);
if (pWait.AsyncWaitHandle.WaitOne(new TimeSpan(ReportGenTimeout(), 0, 0),
false))
{
byte [] abResult = oRS.EndRender(pWait, out sEncoding, out sMime, out
aUsedParams, out aWarnings, out asStreams);
}
else
{
oRS.Abort();
throw new ApplicationException("Report Generation Timeout Expired");
}
Any help will be greatly appreciated. Thanks.
JocelynHave you tried the report from Report Manager to determine if it is an
integration issue or running a report issue?
What format are you rendering it? If it is not html then instead of report
manager test it using URL so you can specify the render type.
My guess is that it is too big. RS is not designed for this sort of thing. A
lot of rendering goes on in memory so something this large could be
problematic which I why I suggested the above tests.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jocelyn Duhaylungsod" <JocelynDuhaylungsod@.discussions.microsoft.com> wrote
in message news:E26E3D99-D157-4A9B-A164-99F75119969D@.microsoft.com...
> I have a huge report (approx. 120MB) needs to be returned from reporting
> services. However, most of the time the request die before the report is
> returned.
> While I am debugging this problem, I realize that an exception is thrown
> between the BeginRender() and EndRender() methods of Reporting Service.
> Exception:
> Unable to read data from the transport connection.
> I am not sure if there is any limit in report size that can be
> generated/transported from reporting service. Reporting service is called
> from another ASP.Net web service. This web service also resides in the
same
> box as reporting service and SQL server 2000. This box is running Win
2003
> and IIS 6(IIS 5 isolation mode).
>
> Here is the code snippet:
> ----
> ReportingService oRS = CreateReportService(sRSession);
> IAsyncResult pWait = oRS.BeginRender(m_sReport, sFormatName, null,
> sFormatInfo, aParams, null, null, null, null);
> if (pWait.AsyncWaitHandle.WaitOne(new TimeSpan(ReportGenTimeout(), 0, 0),
> false))
> {
> byte [] abResult = oRS.EndRender(pWait, out sEncoding, out sMime, out
> aUsedParams, out aWarnings, out asStreams);
> }
> else
> {
> oRS.Abort();
> throw new ApplicationException("Report Generation Timeout Expired");
> }
>
> Any help will be greatly appreciated. Thanks.
> Jocelyn|||A little over 100MB report was able to be generated and viewed from report
manager. The report is in html format.
All the smaller reports are able to be generated with the same codes. I am
not sure what kind of intergration issue may cause the huge report failed.|||That does tell you something (that it works from Report Manager). It could
be a timing out issue with IIS. Could be something different when using web
services that isn't there when using it from Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jocelyn Duhaylungsod" <JocelynDuhaylungsod@.discussions.microsoft.com> wrote
in message news:163991C3-AD80-4E2A-85FF-7C6443278558@.microsoft.com...
> A little over 100MB report was able to be generated and viewed from report
> manager. The report is in html format.
> All the smaller reports are able to be generated with the same codes. I
am
> not sure what kind of intergration issue may cause the huge report failed.
>|||It takes about 3-4 minutes before the request dies.