A colleague of mine had a problem with a report he was working on. The customer wanted to put too much information on it. This would make the report cluttered and unreadable and therefore useless.
Much of the data on the main report did not need to be displayed all the time, the users only needed additional details on some of the rows. In order to solve the problem my collegue originally decided to create a sub-report with additional data, add it to a cell in the main report’s tablix and toggle visibility based on a textbox in the tablix.
Unfortunately, as so often happens with sub-reports, the performance was slow. All the additional data in the sub-report had to be queried and rendered for each row even if the user never needed to see most of it.
The solution was simple and effective, only run the query for the sub-report for those rows the user needs. My colleague converted the sub-report to a drill-through report. The genius part was simplifying the user’s navigation experience, the drill-through was opened in a pop-up window.
With a pop-up window the user can simultaneously view the main report and as many detail windows as needed. Also, when the user is done viewing details he can close the pop-up window and be immediately back the exact spot on the main report without any further navigation.
Change the MyReportServer, MyFolder, MyReport, etc to your report server and path to your drill-through report.
Using this technique can dramatically improve performance because the sub-report data is not being run for each row on the report and it also doesn’t need to be rendered/hidden either. It is only displayed when the user needs it
Also, the user’s UI experience is better owing to the additional flexibility of having multiple windows displaying multiple reports that can be compared and referenced to the main report easily. Additionally, the user does not need to navigate back to the main report when he’s done reviewing the drill-through report(s), he simply closes the pop-up window(s) and the main report is in the exact state he left it.