Posted by beakersoft | Posted in SQL Reporting Services | Posted on 17-07-2009
Recently quite a few of our users have been asking if they can show all of the report details at runtime, instead of having to drill down all the groups, opening and closing as they see fit. Well, the answer is yes, and its very easy to implement.
Parameters are you friend
The first thing to do is create yourself a new parameter in the report, and call it something like par_expand_groups. Give it the following options:
Now, you need to make this parameter control weather or not the group is expanded as the report is ran, you do this by putting an expression into the hidden property of the row.
Normally if you have a group, when you run the report it will probably be hidden and the user has the option of showing the details, so the hidden property will be set to true. We are going to set it to the following expression:
=IIF(Parameters!par_expand_groups.Value = “True”, False, True)
This means that if the parameter is set to yes, the details will be shown, if its set to no they will remain hidden. Add this to each level of grouping on your report, and the user now has control over what they see
Slight Problem with the show/hide icons
Once you have added this to your report, you might notice a slight problem. If you run the report with the option of showing the group details, the little show/hide icon on the left used to toggle the details will be wrong. It will be showing the ‘+’ even though the details are already showing, and if you press it to hide the details it will change to a ‘-‘. They are the wrong way round!
Obviously, this is going to confuse your users, and needs sorting. Well, thanks to a tip from http://www.lukehayler.com/2009/07/custom-visibility-toggling-in-ssrs its easy.
All you need to do is open up the properties of the text box that is the trigger for the show hide, and go into the Visibility tab. At the bottom is an option to set an expression for the toggle image. just enter:
=IIF(Parameters!par_expand_groups.Value = “True”, True, False)
As before, they is just looking at your parameter and setting the value of the toggle image. Now, when you run the report with the option to display the details, they icons for toggling the details should be correct.