4

In SSRS 2008 R2, I was under the impression that a drill down to a sub-report would query the data on-demand. This isn't the case when my report gets rendered.

Specifically, I have a Tablix that initially loads rows grouped by Person. When someone clicks the drilldown icon on that Person, a subreport displays some more data pertinent to that Person. The problem here is that every subreport is being loaded initially when the main report is being rendered. This takes forever to load. But if I remove the subreport, just for testing purposes, the report loads almost instantly.

I've tried setting the visibility of the subreport to Hide initially, then Show when the user drills down, but it didn't change anything.

I read a similar question on stackoverflow, but the answer was to use Drill-Throughs instead. This unfortunately isn't an option in my case.

Any suggestions?

JJT
  • 387
  • 4
  • 11
  • What is taking forever? Your SQL queries or the rendering? – Dominic Goulet Jul 13 '12 at 14:07
  • It takes forever to load the report because the sub-report gets it's data from a stored procedure (which itself performs calculations). So if I have 1000 rows in my main report, the sub-report gets called 1000 times. This is what takes the longest. I'm ok with the sub-report taking about 10 seconds to run, but I want this to happen only when the drill down icon is clicked. "On-demand" style. – JJT Jul 13 '12 at 14:31

1 Answers1

3

The only way to defer the data loading is to actually use a new report with and access it via Drill-Through method. This is the way to product work, and is the way inteded by Microsoft.

You can either optimize your stored procedure, limit the number of results per page or use drill-through.

Dominic Goulet
  • 7,717
  • 7
  • 23
  • 54
  • Thanks Dominic. I was hoping there might be a workaround. Drill-throughs it is then. – JJT Jul 13 '12 at 14:38