Multiple groupings at the same level

Question

I pull from three tables: A, B and C.

B and C reference A using a foreign key, so there is a 1-n relationship between A and B, and between A and C. Furthermore, there can be multiple matching records in A. So the data could look something like this:

A(1) --> B(1), B(2), ... B(n)
       --> C(1), C(2), ... C(n)

A(2) --> B(1), B(2), B(3), ... B(n)
       --> C(1), C(2), C(3), ... C(n)
.
.
.
A (n) --> B(1), B(2), B(3), ... B(n)
       --> C(1), C(2), C(3), ... C(n)

I want records from A as the outermost group, then from B as inner groups at the same level, one after another. So the report should look something like this:

Table A Record 1

  (Data distributed out into several rows)

  Table B Record 1
  Table B Record 2 ...
  Table B Record n

  Table C Record 1
  Table C Record 2 ...
  Table C Record n

Creating three groups will not work because they are always nested, so I cannot display B and C at the same level. (C will get nested inside B.) I cannot create 2 subreports for B and C because the entire thing is a subreport

How can I proceed?

Answer 1

Join and union your tables and use groups on the second level that only print of needed.

TableA left outer join TableB
union all
TableA left outer join TableC

will result in records like

A(1), B(1)
A(1), B(2)
A(1), B(3)
A(1), C(1)
A(1), C(2)
A(2), B(1)
A(2), B(2)
A(2), C(1)

Create a your group for B and C on the same level and suppress them when no record is present (e.g. B.Id is null)

Group for A
    Group for B
    Group for C