While SSRS allows drill-down into individual groups in a Tablix, there is no built-in, direct support for an “expand all/collapse all” capability that would allow all groups to be simultaneously expanded or collapsed. No problem. We can create our own pretty easily.

Here’s what it will do: The user will interact with two radio buttons. Clicking one of them will put the report into Collapse-All mode and clicking the other will put it into Expand-All mode.

Here’s how we’ll do it: First, we’lll create a hidden parameter as a switch (we hide parameters when we don’t want users interacting directly with them). Our parameter will hold either a 1 or a zero. We will initialize the parameter to zero and tie the visibility property of the Details group to it so that when the parameter is 1, the Details group will display (Expand All) and when it’s zero it won’t (Collapse All).

All we need now is a way to flip the switch. We’ll use an action on the radio buttons to call our same report and pass either a 1 or a zero to the parameter depending on which radio button the user clicked.

Just one last thing: We also have to manage the appearance of our radio buttons. Each button will display an image indicating whether the button is selected or not. I’ll point this part out when we get to it.

Getting started

1.  Create a report called ExpandAllCollapseAll with a dataset that pulls data from any of the OLTP versions of the AdventureWorks database (AdventureWorks, AdventureWorks2008, AdventureWorks2008R2 or AdventureWorks2012). Use this query in the dataset:

SELECT soh.SalesOrderNumber
, sod.OrderQty
, sod.UnitPrice
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID

2.  Create a table data region that shows the OrderQty and UnitPrice columns and groups them by the SalesOrderNumber with a group footer row but no group header row. We’ll use block format, but you can also use the stepped format if you prefer; see blog article: Working with Stepped and Block Formats in SQL SSRS.

Create a hidden parameter

1. Right-click the Parameters folder in the Report Data pane and select Add Parameter…
   
   
2. Name the parameter, change its Data type to Integer, set its visibility to Hidden, and then select the Available Values page.
   
3. In the Available Values page, select Specify values, create the two values as shown (use the Add button to create the second value), and then select the Default Values page when done.
   
4. In the Default Values page, select Specify values, type zero for a default value, and then click OK. Zero represents Collapse All; if you want the default setting to be Expand All, set the default value to 1, instead.
   

Tie the visibility of the Details group to the parameter

This is where we tie the visibility of the Details group to the parameter so that the Details group is hidden or displayed as the report is called with either a 1 or a zero for the parameter value.

1. Select Group Properties… from the Detail group’s drop-down list in the Row Groups panel at the bottom of the Design screen.
   
2. In the Group Properties dialog, select the Visibility page, select Show or hide based on an expression, and click the fx button to open the Expression dialog.
   
3. In the Expression dialog, type =Parameters!prmExpCllpsAll.Value = 0 (remember, zero represents Collapse All) and click OK.The return value of the expression will be passed to the Hidden property of the group, so when the expression evaluates to True (Collapse All is selected), the Hidden property of the group will be set to True and the groups will be hidden. When it evaluates to false (Expand All is selected), the hidden property will also be False and the groups will be expanded.
4. While we’re here, this is also a good time to set the toggle to the textbox containing the sales order number if you also want to allow the user to expand and collapse individual groups (the Name property of the textbox was set to txtSON for this example).
   

Set up the buttons

Users will interact with the buttons to change the value of the parameter, causing all the groups to all expand or collapse.

We’re going to use images for radio buttons and textboxes for the labels. You’ll need two images, one that looks like a radio button that is selected, and the other that looks like one that is not selected.

1. Download the images.
   1.   Right-click on each image just below and selecting Copy image.
      
   2.   Paste each one into a Word document, right-click on each in the Word document, and select Save as picture… Save them in the same folder where the report is saved. Call the first one Radio_ON.jpg and the second one Radio_OFF.jpg.
2. Add the two images to the report.
   1.  For each of the two images, right-click the Images folder in the Report Data panel and select Add Image… then select the image from the location where you saved it, above.
      
   2.  When you’re done, the Images folder will list both images
      
3. Create the buttons in the report.
   We’ll also configure the buttons to display the correct radio-button image (Radio_ON or Radio_OFF) depending on the value of the parameter.
   1.  Create a page header in the report (Report menu, Add Page Header).
   2.  Drag an image control from the Toolbox and drop it into the page header. When you are prompted for its properties, type imgExpandAll for the name, type a tool tip if you want one, select Embedded for image source so the report will use the images you added to it earlier, and then click the fx button to enter an expression that will determine which image to display.
      
   3.   Type the expression as shown. It uses the Immediate-If function (IIF) to determine whether the prmExpCllpsAll parameter is set to 1, in which case the function returns Radio_ON; otherwise, it returns Radio_OFF.
      D.   Resize the image control so that it is only as big as the image icon and add a textbox as a label next to the image control. Then enter the text Expand All in the textbox.
      

Repeat steps b – d to create a second image control in the page header. Use imgCollapseAll for the name, Collapse All for the tool tip if you want one, and when creating the expression, have it test for a zero instead of a one.

When done, your report should look like this in the design tab:

Add actions to the buttons

This is where the action is. Literally. We will set the Action property of the image controls to run our report again but this time it will pass the appropriate value to our parameter, which, after our wiring up, causes the report to display either all expanded or all collapsed.

1. Right-click the first image control (Expand All) and select Image Properties…
   
   
2. In the Action page, select Go to report, select the same report from the Specify a report drop-down, then click the Add button and select the parameter in the Name column and type 1 in the Value column. We want the report to display in Expand-All mode when the user clicks the Expand All radio button. When you’re done, click OK.
   
3. Repeat steps 1 & 2 for the second image control, but this time use 0 instead of 1 for the parameter value in step 2.

And that’s it! You can also configure the same actions in the respective textboxes and use them together with the radio buttons or just use the textboxes by themselves without radio buttons if you prefer.

Enjoy!

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ