I thought I'd put something here as there are lots of posts like this on forums (a lot of which seem to be copied & pasted from other sites) but they refer to SQL Server 2000 or SQL Server 2005.

This is how to do it in SQL Server 2008 and creating a report in Business Intelligence Development Studio (BIDS) or MS Report Builder.

Scenario
I have a report that specifies some room bookings for a particular week. When I open the report, each drilldown row is collapsed (as opposed to expanded). The user can specify the date as it's a report parameter.

What we want
The user will select a date. We want the report to open with the specified day expanded and the remaining days that week to be collapsed (hidden). The first time I did this, I couldn't expand the remaining days so don't forget to tick the option "display can be toggled by this report item" like I did. ahem...

How to do it