can any one guide me regarding Drill Down report, as i was asked in one interview question that if we create a drill down report in SSRS and again and again if expand the detail section every time it hits the database. So can we optimize it , but as per my point of view the complete record according to Query is called once in Data set then how we are hitting DB again and again if expanding the details in Drilldown report. please guide me for clearing the concept thanks in advance Regards, Amitesh Srivastva Regards, Amitesh Srivastva

The Report Caching mechanism is provided for the efficient repeated execution of reports. BOL link: Report Caching in Reporting Services SQL Server 2000 A report server can keep a copy of a processed report and return that copy when a user opens the report. This practice is called caching. Caching can shorten the time required to retrieve a report if the report is large or is accessed frequently. If the server is rebooted, all cached instances are reinstated when the ReportServer service comes back online. Caching is a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed. If you require a more predictable caching strategy, you should create a snapshot. For more information, see Creating Snapshots for Report Execution. Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training

While drilldown reports present a nice view of summary and detail they cause severe performance bottle neck when dealing with larger datasets. From a design perspective you may want to consider designing a drillthrough report instead of a drill down report. Below link gives you some insight... http://blogs.msdn.com/b/robertbruckner/archive/2009/01/08/report-performance-optimization-tips-subreports-drilldown.aspx