Jump to content

Tricky grouped-data report with multiple joins


Robert Lauriston
 Share

This topic is 6640 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6640 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.