7 Replies Latest reply on Aug 7, 2015 2:07 PM by philmodjunk

    Cross-Tab Attendance Report

    JohnWallace_1

      Title

      Cross-Tab Attendance Report

      Post

      Greetings. I am attempting to create an attendance report for a church that will list attendance for each student in a class during the last 6 weeks. I am attempting to do this using a cross-tab query listing dates on top and students in a left-hand column. One problem I'm encountering is that not all classes meet on the same days or with the same intervals, so the date header needs to be dynamic and needs to draw the dates from those related to each particular class.

      I've set up a table listing each class, a table listing each student, and a table relating to both:

      ATTENDANCE
      student_id
      class_id
      date
      attendance_code

      Recording the attendance for each class is not a problem. Producing this report is another matter. Is there anyone who can point me to a resource or give me some coaching regarding how to set this up. I'm assuming that I will be using scripts 
      and some kind of a loop to populate the query but am open to any approach.

      Thank you.

        • 1. Re: Cross-Tab Attendance Report
          SteveMartino

          Is this suppose to show dates across the top (every day), Students down the left side, and "X" where they attended, with the header being the class?  Then one report for each class?

          What is attendance code?

          Say you have an English class that meets in different ways-Daytime M,W, F, and nighttime T,R,Sat.  If that's the case, then I think you need another table of 'Sessions'.  A Class (unique) would consist of a subject, students at a particular unique time (Session).  Then the reporting would be easier

          • 2. Re: Cross-Tab Attendance Report
            philmodjunk

            It appears that your data model is:

            Students-----<Attendance>----------Class

            If so, then you can either combine a "use values from field" value list with the ValueListItems function or use ExecuteSQL to get a return Separated list of attendance dates for a given class.

            Then you could add a global field for recording the ID of the currently specified Class.

            That would allow you to set up a relationship like this:

            Students::StudentID = Attendance|ClassXtab::StudentID AND
            Students::gClassID = Attendance|ClassXtab::ClassID

            Attendance|ClassXtab would be an added table occurrence of Attendance.gClassID would be your global field for specifying a class.

            Now you can set up a list view layout based on the Students table with a row of One Row portals to Attendance|ClassXtab. The first would be set up with a portal filter expression such as:

            GetValue ( DateListHere ; 1 ) = Attendance|ClassXtab::date

            and you would put the attendance_code field into the portal row. You can then copy this portal and just change the portal filter expression. The next would be similar to this:

            GetValue ( DateListHere ; 2 ) = Attendance|ClassXtab::date

            The key detail that I have not specified is what to put in place of "DateListHere". This can be a return separated list of dates generated from an ExecuteSQL function call or a ValueListItems function call. I would define this in a separate unstored calculation field so that you don't have to evaluate the same list generating calculation repeatedly for each column of attendance codes in your report.

            • 3. Re: Cross-Tab Attendance Report
              JohnWallace_1

              Thank you, STEVE MARTINO; you are correct. My "classes" table is technically "sessions."

              Thank you, PhilModJunk; this looks like what I'm looking for. I'll work with it and let you know how it turns out.

              Blessings to you both.

               

               

              • 4. Re: Cross-Tab Attendance Report
                JohnWallace_1

                I understand the logic of what you are proposing, PhilModJunk, but am stuck on step one. I created the value list, Group_Meeting_Dates, using values from field, Attendance::Date. (The value list works fine in a dropdown menu.) Then I created a repeating calculation field to display the values using ValueListItems( Get( FileName ); "Group_Meeting_Dates" ). Unfortunately, it keeps repeating the first date in the list, even when I specify an array number. I've tried putting the field in several places but with no better result. Any suggestions? Thank you.

                • 5. Re: Cross-Tab Attendance Report
                  philmodjunk

                  I did not suggest using a repeating calculation field.

                  I suggested setting up a series of portal filter calculations, each referring to a different member of your attendance dates value list.

                  The first portal would have this filter expression:

                  GetValue ( DateListHere ; 1 ) = Attendance|ClassXtab::date

                  The second would have this:

                  GetValue ( DateListHere ; 2 ) = Attendance|ClassXtab::date

                  DateListHere is a list of values inside a single repetition field with each value separated by a return. ValueListItems is the simplest but least flexible way to get that list of values. ExecuteSQL offers options for generating the same list of values but with more criteria used to control which values appear in the list.

                  • 6. Re: Cross-Tab Attendance Report
                    JohnWallace_1

                    Ah, I found my error. I had formatted the output in the ValueListItems calculated field to "date", so it was only giving me the first date instead of the list of dates. Things are coming along. Is there a way to filter the dates in the ValueListItems list so that when I filter the layout by class, only the dates on which that class meets are in the list? Thanks.

                     

                    • 7. Re: Cross-Tab Attendance Report
                      philmodjunk

                      ValueListItems is the simplest but least flexible way to get that list of values. ExecuteSQL offers options for generating the same list of values but with more criteria used to control which values appear in the list.

                      So executeSQL is one option. With such a query, you can specify a WHERE clause that limits the list of values to those from records for a particular class.

                      You might also define a conditional value list that only lists values for the current record's class and then ValueListItems again will only list dates for the specified class.