Expand/collapse all Report Builder without using parameter

Question

Is it possible to expand collapse all grouped items on SSRS Report builder like in example on the page below: https://www.sqlchick.com/entries/2010/9/19/expand-or-collapse-all-grouped-items-on-ssrs-report.html but without using parameters and without go to report action with this new parameter?

I was trying with variable in report but I couldn't find a way how to make a button from textbox so I could for example toggle this variable by =Variables!MyVariable.SetValue("1") when the button is clicked.

Answer 1

There are few ways to do this depending on if you want to be able to expand/collapse a single column at a time or all columns.

In this example, using some sample city geography data, I created a simple table with cities, then a country group and then a continent group, in both cases I chose to include a group header, all standard stuff.

To expand both the country and cities levels (Continent was set to visible and therefore always shows), I added a textbox, changed its name to ToggleAll so it's easier to identify.

The report design looked like this.

enter image description here

Then I right-clicked the CountryDesc group, then Properties then set the visibility to hidden and set the toggle object to ToggleAll

enter image description here

I then repeated this on the Details (Cities level) row group.

When the report first runs, it looks like this...

enter image description here

Notice how the "Toggle All" button has a + as SSRS recognises it controls visibility. With a single click of this textbox, the report expands to this..

enter image description here

If you wanted to expand single levels, you could do a similar thing but use the textbox that contains the column header to control visibility. It's easier if you rename the textboxes before you do this to something easy to find in drop downs lists (e.g. hdr_Country)

• Marta: But there is no possible to add additional textbox to "display can be toggled by..."? Taking example from this link sqlchick.com/entries/2010/9/19/… I want to have the functionality to expand all by the clicking on account type (or some different textbox that I create like "expand all") and both expand single row.
• Alan Schofield: Do you mean that, taking my example, if you clicked on Africa, it would expand all countries and cities in Africa? If not, then please EDIT your question and add some images that show what you expect to see.
• Marta: Yes exactly. So I want to have functionality both of toggle all and when clicking ,for example, Africa expand or hide the countries and cities. Just like in the example in the link but without using parameters
• Alan Schofield: A group can only have visibility toggled by one object. So you could have both country and city expand when you click Continent by setting both groups to use the Continent textbox as the toggle object, but you would then not be able to expand/collapse a single country.