Handy SQL for Teacher Reports in sqlReports

It's very important that you code sql correctly with regards to teacher reports.  If the reports deal with students information, the majority of time you will want to limit that data to only students they have in class and you do that by coding the sql properly.  Failing to do so can lead to reports with too much information.   It's important too that you test out all of your reports before making them available to teachers.

Proper Drop-Down Choices - it's very important to use the proper drop-down for any reports you create for the teacher portal.  There are drop-down items that are specifically made for use with sections and students for teachers at the bottom of the drop-down list.  Be sure to use those instead of the drop-downs that show all sections or all students that are used with admin reports.

Basic Roster Reports
- free examples of roster reports can be found at the bottom of the Live Schedules Reports page.

Enrollments in a teacher's section are kept in the CC table, with the section number itself recorded in the cc.sectionid field.  Here is a building block to use when teachers select one of their sections (in the example below that will be Parameter 1) and get a roster of the active students in that section:

select s.lastfirst
from cc cc
inner join students s on s.id = cc.studentid
where cc.sectionid = %param1%

Active students will have a positive number in the cc.sectionid field and dropped students will have a negative number.  So if you want to see just the dropped students, use the following where instead which has a minus sign before the %param1% reference:

where cc.sectionid = -%param1%

If you want to want to include both active and dropped students, use the following where instead:

where abs(cc.sectionid) = %param1%

The abs command will treat all the sectionid values as a positive number in the sql.

Rosters Built Around Other Tables

There are times when you may want to build a roster based on information in another table.   For example, if you wanted to list attendance data for students in a section chosen from Parameter 1 drop-down, you can do a sub-query on an inner join to get those students from that section based on cc records:

from attendance att
inner join (select studentid stuid from cc where cc.sectionid = %param1%) cls on att.studentid = cls.stuid
inner join students s on s.id = cls.stuid

Reports Built Around All The Students A Teacher Has

There are times when you may want to list all the students a teacher has in any class.   In those cases, you'll want to use the ~[x:userid] variable to get the id of the teacher who's logged in and then use that in the sql.  The following will list all the students the teacher has for the current year:

select s.lastfirst
from cc cc
inner join students s on s.id = cc.studentid
where cc.teacherid = ~[x:userid] and cc.termid like '~(curyearid)%%'

Teacher Information Reports

If you're building reports to show teacher data rather than student data, there are two X codes you can use to identify teachers based on login data and thus have the report only pull information for only that teacher:

~[x:userid] - used with legacy tables, such as the CC table or Sections table.  For example, if you want to list all of the sections a teacher is teaching, the sql would look something like:

from sections
where teacher = ~[x:userid]

If you want to include sections where the teacher is a co-teacher, build around the SectionTeacher table instead, but note that it has teacherid for a field name instead of teacher:

from sectionteacher
where teacherid = ~[x:userid]

~[x:users_dcid] - used with newer tables, such as the Users table and any users extended table.  For example:

from users
where dcid = ~[x:users_dcid]

Teacher Schedules

The primary information for teacher schedules is kept in the Sections table.  Sections with co-teachers also have information in the SectionTeacher table.  Depending on the report, you can join the two tables together to pull information from both, or use a union command to pull data from each table and merge them together.

Testing Teacher Reports in the Admin Portal

You can test teacher reports in the admin portal by using the Sections drop-down menu for the report.  For example, if you have a report that uses the following:

select s.lastfirst
from cc cc
inner join students s on s.id = cc.studentid
where cc.sectionid = %param1%

and Parameter 1 is based on the PST - Current Sections drop-down, you can change that to the Sections drop-down instead for testing purposes.  Both drop-downs will pass the section id to the report, with the difference between them being how much data is displayed on the menu.  The PST - Current Sections one will look at the teacher id and only display sections he or she teaches.  The Sections drop-down will look at all active sections for the current school.

For reports that are teacher specific, you can use either the Staff (ID) or Staff (DCID) drop-downs on the admin side to test them out.


  Back to the List of Articles