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":
Follow the steps below, to create this report:
Table design
Table contents
Add new report
Start Microsoft SQL Server 2008 R2 Report Builder 3.0 > New Report > Blank Report
Add a new data source to the report
Add a data source [Right click Data Sources > Add Data Source…]
The [Add Data Source Wizard]:
Add a new dataset to the report
Add a dataset [Right click Datasets > Add Dataset…]
The [Add Dataset Wizard]:
Add a tablix to the report
Add a tablix [Click in the ribbon on Insert > Table > Table Wizard…]
The [Add Table Wizard]
Add Parent and Child to Row groups and Cost to Values
Choose [Stepped subtotals above]
Row visibility
Right click on the row header of the [Child] row > Row Visibility…
Show or hide based on an expression: = IIF(Fields!Parent.Value = "John", False, True)
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…
InitialToggleState
Select row header of the [Parent] row > Properties > InitialToggleState > <Expression…>
=IIf(Fields!Parent.Value = "John", True, False)
Run the report
