### Setup

Just to setup an example, let's start with the following data (you can enter this as a DataSet in SSRS)

```sql
;WITH Games (Division, Team, Wins, Loses) AS (
 SELECT 'North', 'Puffins', 2, 10 UNION
 SELECT 'North', 'Robbins', 6, 5 UNION
 SELECT 'South', 'Parrots', 9, 3
)
SELECT * FROM Games
```

We'd like to *group by* `Division` and then list out all the teams in each division, but in two different blocks. So loop through all of them once and display wins and *then* loop through all teams in each division and display losses - like this:

[![enter image description here][1]][1]


# Solution

1. Add a `List` control

 *Note*: By default, the List view should only have a single cell and that cell should have a `Rectangle` in it. The immediate child of a cell takes up the full cell size, so having a rectangle allows you to layout content within it

2. Add a dataset and apply it to the List

 You may be prompted when adding the list or right click and select properties > general > dataset

3. In the [grouping pane][2], under `Row Groups`, right click `Details` and select `Group Properties`.

 [![Group Properties][3]][3]

 *Note*: ***Do not*** *add* a new group here; instead we're adding a grouping expression to the existing details section

4. Add a group expression and select the field in your dataset you want to group by

 [![Group Expressions][4]][4]

5. Add tables to the list's cell / rectangle area. 

 The dataset should be pre-selected and read-only because we're inside of a details section:

 [![Frozen Dataset][5]][5]

6. Add any other columns or controls you'd like

 [![Design and Preview][6]][6]


**Further Reading**

* [Multi-detail reports using sub reports in SQL Server Reporting Services][21]
* [Previous version][22] of answer used subreports, but this current approach is cleaner and faster


 [1]: https://i.stack.imgur.com/40n5T.png
 [2]: https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/report-design-view-report-builder?view=sql-server-ver15#GroupPane
 [3]: https://i.stack.imgur.com/5rZ7U.png
 [4]: https://i.stack.imgur.com/igqcC.png
 [5]: https://i.stack.imgur.com/cwJL7.png
 [6]: https://i.stack.imgur.com/gBrj8.png
 [7]: https://i.stack.imgur.com/1TONF.png
 [8]: https://i.stack.imgur.com/B5cb5.png
 [9]: https://i.stack.imgur.com/RXvlB.png
 [10]: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/tables-matrices-and-lists-report-builder-and-ssrs?view=sql-server-2017
 [11]: https://stackoverflow.com/q/7800217/1366033
 [12]: https://i.stack.imgur.com/spGjD.png
 [13]: https://i.stack.imgur.com/HzjWs.png
 [14]: https://i.stack.imgur.com/bC75N.png
 [15]: https://i.stack.imgur.com/KsP4Z.png
 [16]: https://i.stack.imgur.com/y9vTA.png
 [17]: https://i.stack.imgur.com/xKXFK.png
 [18]: https://i.stack.imgur.com/ytM7E.png
 [19]: https://i.stack.imgur.com/I9dwl.png
 [20]: https://i.stack.imgur.com/lqAKM.png
 [21]: https://www.mssqltips.com/sqlservertip/5649/multidetail-reports-using-sub-reports-in-sql-server-reporting-services/
 [22]: https://stackoverflow.com/revisions/58349673/1

I have a set requirement to produce a report that looks like this (the detail grids are based on and group by a Hearing table ID, or HearingDate and CaseId):

```
Hearing Group (repeat for each hearing)

    ┌───────────────────────────────────────────────────────────────────┐
    │ Hearing Date: [HearingDate]     Case ID: [CaseId] etc...          │
    │                                                                   │
    │ | Start Time  | Hearing Type  | etc   |                           │
    │ | [StartTime] | [HearingType] | [etc] |                           │
    │                                                                   │
    │ Participant List:                                                 │
    │                                                                   │
    │ | Role   | Participant Name  | Participant End Date |             │
    │ | [Role] | [ParticipantName] | [ParticipantEndDate] |             │
    │                                                                   │
    │ | Violation Description   | Plea Description  | Person Charged  | │
    │ | [Violation Description] | [PleaDescription] | [PersonCharged] | │
    └───────────────────────────────────────────────────────────────────┘
```


I've researched this quite a bit and don't think I can use one dataset and one tablix and group it that way because of the multiple detail grids. If I had just one detail grid, sure, that's easy, but I've not seen a way to do with two levels of detail.

Apparently, SSRS limits you to one dataset if you nest two tables/tablixes, so that's out, plus there's limited flexibility with crossing columns. I'm not sure if I can accomplish this with a List (which I have no experience with), or maybe using the LookupSet function. I don't want to go down the wrong path either with my design or by choosing a route that hurts performance, because the parameters for this report allow a date range for hearing start and end dates and so this could potentially generate many records. 

Some ideas would be appreciated!
