Technique: Compressing Multiple Rows for a Student into a Single Row

Using the First Row for a Student to Compress Multiple Rows of a Students Data into One Row

In this example, multiple lines for each student have been condensed into a single row for the student showing all of the courses and a sum total of their credit load:

The technique discussed here illustrates how to collapse multiple rows into a single row.

Step 1:  Creating the First Row for Student and Last Row for Student Columns

Use the techniques described in the Finding the First and Last Rows for a Student article to create two columns: a "first row for the student" column and a "last row for the student" column.  These two will be used to do the rest of the work.

Step 2:  Condensing Multiple Columns of Data into a Single Column

First, let's run the report again making visible the columns that have been condensed - and hidden - in the previous example:

${  row.firstRowForAStudent == true ?  

              rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') )) :                                                        rw:concat3(prevRow.schedule,'

',rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') ))) }

Let's unpack this:

• Part A:  This is a test to determine if this row is the "first row for the student" or some other row for the student:                                                                 row.firstRowForAStudent == true ?  
• Part B:   This is what to do if this *is* the first row the student.    It consists of nested calls to the "concat" functions to collapse multiple fields into a single field:                                                                                                                                concat rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') )) 
• Part C:    This is what to do if this *is* the first row the student.    It consists of three pieces:  1) retrieving this same column form the previous row (thus copying it to this row) and then 2) nested calls to the "concat" functions to collapse multiple fields into a single field, and finally, concatenating the previous row's information with the current row - and making sure to stick a "newline" character in between:                                          rw:concat3(prevRow.schedule,'',rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') ))
  • Get the information from the previous row:   prevRow.schedule 
  • Build the information from the current row:   rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') 
  • Concatenate those two things together, with a newline in the middle:   rw:concat3(prevRow.schedule,'',rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') )) 
  • Note:  there is an "outer" concat3 - that attaches the current row to the previous row.  Then there is a "concat5" to build the first part of the current row.  Finally, there is a 'concat' nested into the concat5 call to build the final part of the current row's information.

This illustrates how the columns surrounded by the orange area are condensed and captured into the single column shown in red.  These are the rows for just one student:

Another Approach:  Putting the Concatenation in its own Columns

It might be easier to see what is going on if the concatenation - putting the course code, course description, semester code and period together - in its own column:

        ${ rw:concat5(row.course,'/',row.Sect,' ',rw:concat5(row.Description, ' (',row.Sem,',',rw:concat(row.Per,')') )) } 

To understand fully what is going on there, please read the "Concatenation" article.

Once the concatenation is in its own Column, let's say it's labled CourseInfo, the process in the "build a single column with all of the course info in it" may be clearer:

${  row.firstRowForAStudent == true ?  row.CourseInfo  : rw:concat3(prevRow.schedule,'

', row.CourseInfo) }

Note that the newline character is what is causing the move to the 2nd line above and that that newline character is required to achieve the nice separation in the final product:

And Then What?  Keeping Only the Last Row for the Student

After all of the information is condensed into a single row for each student, you can view how the process has accumulated data in one row at a time:

Once you have condensed multiple rows into a single row, the goal is to eliminate - that is, exclude from printing - all rows for the student except the last row.   This can be done with the "last row for the student" column.

If you have set up the "Last Row for a Student" Column (please read the Finding the First and Last Rows for a Student article), you can simply add a Filter to the Filters screen:

Note that on the Filters screen, a "Y" is used for true and an 'N' is used for false.

Once that filter has been added, only the last row for each student will show:

All of the rows you do not need to see can now be hidden and only the schedule will show in the "Schedule" column: