Spreadsheet Style Report from Related Tables

[htbaxter]

Hello - this is my first post so forgive me if I miss some kind of protocol - but I need some help.

I am trying to create a spreadsheet style report which shows which classes students have attended in a semester. The report needs to show along the left column the names of students (from table:Students). The columns needs to be the specific classes (from related table:Classes). This is a many to many relationship created by a join table (table: Student2Class) which stores information on whether they attended the class or not.

Obviously I could do this with a summary report of each student which then lists the classes he/she attended. However, the user wants to view all the classes and all the students at once in a spreadsheet style.

Any Ideas?

[Ender]

Welcome baxter.

In my opinion, this "spreadsheet style" is less useful than a sub-summary report because it repeats the Student data for every Enrollment (your Student2Class). By cluttering up the report like this with unnecessary (repeated) data, it tends to make it harder to read.

But in any case, this can be achieved by using putting Student, Class, and Enrollment fields in the Body part of a List View layout based on the Enrollment table (in other words, don't put the Student fields on a sub-summary part). Or for true spreadsheet ugliness, use a Table View. ;)

[htbaxter]

Thanks for the reply - you're right, I don't want the student data to repeat. What I'm trying to get is a single line for each student which puts the attendance information (contents of Student2Class:Hours_Attended) under a column heading of each class name.

Where I'm stuck is that the class name is within a field (Classes:Class_Name). So.. how do I get field contents to display as column headers?

[Ender]

It sounds like you're looking for cross-tab report functionality. This can be done, but it is not as simple as using a layout wizard or something. Usually cross-tab reports are done with scripts or calculated repeating fields. I haven't really worked with them, but you might search the forums for previous examples of cross-tab reports.

[htbaxter]

Thanks - I will look.

I was thinking that I would have to create new fields named Column1, Column2, etc. and have the contents of those fields generated by a script each time I view the report. That's the only way I have thought of doing it so far, but I was hoping someone might have found an easier way.

I appreciate your help!

[comment]

If you define a value list of classes (using all values from the field), you can then use a repeating calculation field =

Let ( [

i = Get ( CalculationRepetitionNumber ) ;

vList = ValueListItems ( Get (FileName) ; "yourValueList" )

] ;

GetValue( vList ; i )

)

Alternatively, you could define a relationship to the Classes table, using the x relational operator, and use GetNthRecord() in a similar fashion.