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!

Setup

Just to setup an example, let's start with the following data (you can enter this as a DataSet in SSRS)

;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:

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, under Row Groups, right click Details and select Group Properties.

^{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

   

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:

   

6. Add any other columns or controls you'd like

   

Further Reading

• Multi-detail reports using sub reports in SQL Server Reporting Services

• Previous version of answer used subreports, but this current approach is cleaner and faster