Honor Roll

Below are examples of Pass-through queries for Honor Roll. Queries may need to be adjusted to fit the PSUs specific honor roll requirements. A good practice would be to create one pass-through query for each term. Please note that the following queries will include all students, active and inactive.

NOTES ABOUT ADJUSTING HONOR ROLL QUERY STATEMENTS

• You must add any score that is valid for honor roll to the "IN statements." For instance, if the PSU uses the E, S, N, U scale along with a traditional numeric grading scale, you will want to include E and S scores in the IN statements. If the E and S scores are not included, any student that receives a posted E and S will not meet the honor roll criteria listed in the statement. Add the E and S scores with a comma and single quote in the g.score and gx.score IN statements. 

Example: The g.score statement would update to:

AND g.score IN ('100','99','98','97','96','95','94','93','92','91','90','P','E','S') 

The gx.score statement would update to:

AND gx.score IN ('100','99','98','97','96','95','94','93','92','91','90','P','E','S') 

Do not alter the g3.score statement for the A/B Honor Roll Query.

• Terms are identified by their sequence number in Scheduling & Courses > Calendar Setup > Term Setup. 
This statement is an example using the Term 1 sequence: Term t on t.termScheduleID = ts.termScheduleID and t.seq = 1 

• Make sure the Grading Task name matches the Grading & Standards > Grading Setup > Grading Task Setup task name the PSU uses to determine Honor Roll. This statement is an example using a grading task name from the screenshot below:  INNER JOIN GradingTask k on k.name = 'Term Grade'. If you need to use a different grading task to determine honor roll, replace the name between the single quotes.
• To exclude inactive students add the statement
  •  AND student.enddate IS NULL in the second box after the AND gx.scoreID IS NULL statement. 

A Honor Roll Pass Through SQL

Navigation: Menu > Reporting > Ad Hoc Reporting > Filter Designer

1. Create a new Query by selecting the Pass-through SQL Query filter and the Student data type. 

 2. Enter a Filter name (include the term) and brief description.  

3. Modify the following criteria to meet honor roll requirements for your PSU as mentioned above in the notes, then 

Copy and paste in box 1:

INNER JOIN TermSchedule ts on ts.structureID = student.structureID 
INNER JOIN Term t on t.termScheduleID = ts.termScheduleID 
AND t.seq = 1 
INNER JOIN GradingTask k on k.name = 'Term Grade' 
LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID 
AND tx.seq = 1 
INNER JOIN GradingScore g on g.calendarID = student.calendarID 
AND g.termID = t.termID 
AND g.taskID = k.taskID 
AND g.score IN ('100','99','98','97','96','95','94','93','92','91','90','P','S') 
AND g.personID = student.personID 
LEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID 
AND gx.termID = g.termID 
AND gx.taskID = g.taskID 
AND gx.personID = g.personID 
AND NOT gx.score IN ('100','99','98','97','96','95','94','93','92','91','90','P','S')

AND gx.scoreID IS NULL

4. Test the query to ensure information is returned. Grades must be posted for the term sequence listed to return results.

5. Save the query in your user account or a folder for a group of users.

A/B Honor Roll Pass Through SQL

This query will exclude students that have All A's and return those with A's and B's. 

Navigation: Menu > Reporting > Ad Hoc Reporting > Filter Designer

1. Create a new Query by selecting Pass-through SQL Query filter and Student data type.

 2. Enter a Filter name (include the term) and brief description.  

3. Modify the following criteria to meet honor roll requirements for your PSU as mentioned above in the notes, then 

Copy and paste in box 1:

INNER JOIN TermSchedule ts on ts.structureID = student.structureID 
INNER JOIN Term t on t.termScheduleID = ts.termScheduleID 
AND t.seq = 1 
INNER JOIN GradingTask k on k.name = 'Term Grade' 
LEFT OUTER JOIN Term tx on tx.termScheduleID = t.termScheduleID 
AND tx.seq = 1 
INNER JOIN GradingScore g on g.calendarID = student.calendarID 
AND g.termID = t.termID 
AND g.taskID = k.taskID 
AND (g.score IN ('100','99','98','97','96','95','94','93','92','91','90') 
OR g.score IN ('89','88','87','86','85','84','83','82','81','80','P','S'))
AND g.personID = student.personID
LEFT OUTER JOIN GradingScore gx on gx.calendarID = g.calendarID
AND gx.termID = g.termID
AND gx.taskID = g.taskID
AND gx.personID = g.personID
AND NOT (gx.score IN ('100','99','98','97','96','95','94','93','92','91','90')
OR gx.score IN ('89','88','87','86','85','84','83','82','81','80','P','S'))
INNER JOIN GradingScore g3 ON g3.calendarID = g.calendarID
AND g3.termID = g.termID
AND g3.taskID = g.taskID
AND g3.personID = g.personID
AND g3.score IN ('89','88','87','86','85','84','83','82','81','80')

AND gx.scoreID IS NULL

4. Test the query to ensure information is returned. Grades must be posted for the term sequence listed to return results.

5. Save the query in your user account or a folder for a group of users.