Tricky grouped-data report with multiple joins

[Robert Lauriston]

For a class-enrollment application, I've got a set of classes that sometimes have multiple teachers and always have multiple students, i.e. two many-to-many relationships with join tables. One of the requirements is for a grouped-data report that's supposed to look basically like this:

sub-summary by teacher

- sub-summary by class (each teacher has four)

-- list of enrolled students (5-20)

The goal is to get a big report that can be broken out into a printout for each teacher listing the students in each of their four classes. (If classes had only one teacher, this would be easy.)

When I do the report with "Show records from" set to the teacher-class join table the report shows only one student per class. If I change "Show records from" to the student-class join table, I get only one class per teacher. Makes no difference whether I use portals.

I spent a couple of weeks going over this with Framemaker tech support and they say I need to create a denormalized table for the report.

I've never had to do this before and I don't know where to start. Any advice?

[RalphL]

I generally do not recommend that you use portals in reports, this looks a place to do it. I downloaded your file to take a look at it. I made a new blank layout using the InstructorAssignment TO. I put the fields InstructorID and Class ID from this TO. Then I added the Instructor Name from TO Instructor. I added Class Name and Period from TO Class. I made a portal to TO ClassEnrollment and included the fields Camper ID & Instrument from that TO and Full Name & Age from Camper TO. I made the portal have 20 rows. This should give you a page for each class for each instructor.

[bruceR]

This was already answered complete with modified file as an attachment in your original thread.

[Robert Lauriston]

Thanks, Ralph, that works perfectly. FM tech support sure dropped the ball on this one.

I guess the key is basing the report on the instructors-classes join table and making the body of the report a portal to the classes-campers join table (even though the actual fields of the report mostly aren't in either of those tables).

Bruce, thanks but the modified file you posted in the other thread had just one sub-summary by class, which showed both instructors.