When you have a report that contains multi level / recursive toggling, As a report developer have you ever wondered why my report just shows up in one single page? Have you ever been in a situation where you are forced to think why neither the interactive size nor the page break taking effect on my report? Or as a report consumer, have you ever wondered why this report takes so long to come up? Why my browser seems to be hung?
Well for all of these, the answer just remains one. We’ll discuss about that in the below section.
Consider your report design goes like this. You’ve an inner most group named “Group 1”. The Group 1 has a parent group named “Group 2” and the Group 2 has a parent group named “Group 3”. So the hierarchy goes like this,
Group 3 -> Group2 -> Group 1 -> Contains the detail rows.
Now, you do not want your report consumers to view all the details in one stretch, and you’ve decided to implement toggling.
Typically, visibility toggles are used to initially display summary data and to provide the user with the option of seeing more details. For example, you can initially hide a table that displays values for a chart, or hide child groups for a table with nested row or column groups, as in a drilldown report. In the rendered report, the text box displays a plus (+) or minus (-) in addition to its contents. When the user clicks the toggle, the report display is refreshed to show the current visibility settings for items in the report.
Refer http://msdn.microsoft.com/en-us/library/ms156456(SQL.105).aspx on how to implement toggling.
In our report, you’ve Group 1 whose visibility is controlled by Group 2. Group 2 visibility is controlled by Group 3. Which means, an user has to first expand a row in Group 3 to view the rows under a specific value in Group 2. And has to further expand a row in Group 2 to view the rows under Group 1.
When you view this report in browser and you expand a toggle item. You’ll see the entire report contents displayed in a single page or it takes lot of resources and time to display the report, or it is even possible that the report is not even shown making the browser to be in hung state. When you check the task manager, the browser is consuming 100% CPU and the memory for the browser is increasing quite steadily.
The above behavior is By Design. The reason is, all the rows under the parent toggles are kept together. This is to ensure the pagination is consistent even if you expand / collapse multiple toggle members.
So, what is the option for you to ensure the report is paginated and served fast?
Well, there is no easy way around keeping all your toggles intact. The closest possible option is to disable toggling in your report and instead use Document Maps. This will not only make your report more readable but also the report will be served faster with less resource consumptions.
[All the posts are AS-IS with no warranty]