How to group multiple detail grids in reporting services

Question

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!

I think your best bet here is using a List, which essentially acts as a container which is repeated by hearing in your case. You'll have to use one dataset, but you can use multiple tablix. — C Bell, Jun 30 '17 at 14:59
Do you have a good example/link of how to group by a given field and set this up? Do I literally just write my query to include all the fields, save it as a dataset, then drop three tablixes in the list and bind cells as required and it groups everything for you automatically? — sfors says reinstate Monica, Jun 30 '17 at 15:06

score 2 · Accepted Answer · edited Oct 10 '19 at 20:45

2

Here's a link to a blog with a pretty decent example of how to use a list. As you can see, this person has a lot going on within the list, so I think you should be able to accomplish what you need. To answer your questions, yes, it is essentially that simple. One dataset with all the relevant/group-able fields. Create a list grouped by hearing, then drop your tablix(es) in as needed.

http://www.bidn.com/blogs/timmurphy/ssas/683/using-a-list-object-in-ssrs

edited Oct 10 '19 at 20:45

KyleMit

30,350
66
462
664

answered Jun 30 '17 at 15:18

C Bell

347
1
8

Okay, this sort of worked. I think my scenario with two detail grids is just a difficult use case. Since it requires one dataset, with repeating detail lines for both participants and violations, using the Previous function I can get rid of duplication in one grid but not the other. If I try to get rid of duplication in say the Violations grid, then I get duplicating sets of data in the Participants grid, and vice versa. Since the data doesn't repeat one duplicate after another, the Previous fcn won't delete it. And using the Hide Duplicates property results in blank rows, which I can't remove – sfors says reinstate Monica Jul 20 '17 at 13:58

KyleMit · Answer 2 · 2019-10-17T15:50:00.147

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

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}
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}
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}
Add a group expression and select the field in your dataset you want to group by
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:
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

Thanks for the follow up - looks like a nice solution! – sfors says reinstate Monica Oct 15 '19 at 15:46 — sfors says reinstate Monica, Oct 15 '19 at 15:46

How to group multiple detail grids in reporting services

2 Answers2

Setup

Solution