How to collapse / expand a specific row group in a tablix in Microsoft Reporting Services 2008 / Report Builder 3.0

Let’s say you want to show costs on a parent child hierarchy in a Microsoft SQL Server Reporting Service 2008 R2 report and you want only to expand the children of parent "John":

 

image

 

Follow the steps below, to create this report:

 

Table design

image

 

Table contents

image

 

Add new report

Start Microsoft SQL Server 2008 R2 Report Builder 3.0 > New Report > Blank Report

image

 

Add a new data source to the report

Add a data source [Right click Data Sources > Add Data Source…]

image

 

The [Add Data Source Wizard]:

image

 

Add a new dataset to the report

Add a dataset [Right click Datasets > Add Dataset…]

image

 

The [Add Dataset Wizard]:

image

 

Add a tablix to the report

Add a tablix [Click in the ribbon on Insert > Table > Table Wizard…]

image

 

The [Add Table Wizard]

image

Add Parent and Child to Row groups and Cost to Values

image

Choose [Stepped subtotals above]

image

 

image

 

Row visibility

Right click on the row header of the [Child] row > Row Visibility…

image

Show or hide based on an expression: = IIF(Fields!Parent.Value = "John", False, True)

image

 

Group visibility

Make sure this expression is forwarded to the group visibility > right click on the row header of the [Child] row > Row Group > Group Properties…

image

image

 

InitialToggleState

Select row header of the [Parent] row > Properties > InitialToggleState > <Expression…>

=IIf(Fields!Parent.Value = "John", True, False)

image

 

image

 

Run the report

image