1 Reply Latest reply: Apr 21, 2014 2:37 AM by Sunil Bhatia RSS

    Report with dynamic number of checkbox columns.

    odysam

      Hello all,

      I have a schema with a table for students:

       

      create table student(
        id number not null,
        name varchar2,
        surname varchar2,
        primary key(id)
      );
      
      

       

      a table for classes:

       

      create table class(
        id number not null,
        subject varchar2,
        primary key(id)
      );
      
      

       

      Each class is completed in a number of meetings so there is a table for that:

       

      create table class_meeting(
        id number not null,
        class_id number not null,
        meeting_number number not null,
        primary key(id),
        foreign key(class_id) references class(id)
      );
      
      

       

      Also there is a table which tracks attendance of students in class meetings:

       

      create table attendance(
        id number not null,
        student_id number not null,
        class_meeting_id number not null,
        present number,
        primary key(id),
        foreign key(student_id) references student(id),
        foreign key(class_meeting_id) references class_meeting(id),
      constraint present_ck check present in(0, 1)
      );
      
      

       

      I am trying to build a report for every class which shows a list of all students and there are checkbox columns for every class meeting.

      The checkboxes are checked is the student was present in that meeting and unchecked if not.

      I also want to save checkbox state with an on submit process.

      Here is a table of what the report would look like (imagine 'x' is a ticked checkbox)

       

      NameSurnameMeeting 1Meeting 2Meeting 3
      JohnSmithxx
      TomBrownxx

       

      From googling around I figured I should use the PIVOT function, but I have a hard time understanding exactly how it works and how to adjust it to this particular case.

      So far I have written this query:

       

      select * from (
        select student.name, student.surname
        from student, class
        where class.id = :PX_CLASS_ID
      ) pivot (
        attendance.present
        for class_meeting.id in(select class_meeting.id from class_meeting, class where class_meeting.class_id = class.id)
      )
      where attendance.student_id = student.id
      
      

       

      But I'm pretty sure this is completely wrong, I am not even sure if what I'm trying to do is possible. Also I have no idea where the APEX_ITEM.CHECKBOX2 statement would go or how I would assing the G_Fxx variable.

      I am an inexperienced oracle developer, so any suggestions for solving the above problem, or any direction to the right documentation, is very appreciated.

        • 1. Re: Report with dynamic number of checkbox columns.
          Sunil Bhatia

          Hi,

           

          If you are experienced enough in PL SQL, develop your PL SQL and create a SQL Report in APEX page.

           

          For example the values of your retrieved checkboxes is 0 for 'N' and 1 for 'Y'

           

          Then go into column attributes of SQL report, select the column and change the 'Display As' setting to 'Simple Checkbox' and provide LOV values as customizable.

           

          Its pretty straight forward. Do it for all the 3 columns.

           

          P.S. If you dont want to do any future action on checkboxes, there will be no need of using APEX_UTIL.CHECKBOX2 utlitity.

           

          Sunil Bhatia

           

          Message was edited by: Sunil Bhatia