Query Information
To access query viewer: Main Menu > Reporting Tools > Query > Query Viewer
Query shortcuts:
- % = Wildcard
- CU_ACAD% = most queries created for academic departments start in this manner
Query Categories
- [General](http://Query Information#general)
- Grading
- Advisors
- Course Enrollment
- Class Scheduling
Query Viewer 101 Presentation | Handout
BI Publisher Viewer is another reporting tool that can be used to access some data in a PDF format. There are a handful of BI Publisher reports available, listed at the bottom of this page. Go there now.
General Queries
| Query | Prompts and Fields | Description |
|---|---|---|
| CU_ACAD_STDNT_DATA_ALL_DEPTS | Prompt: Term Fields: Name, ID, Level, Major/Minors, Cumulative GPA, Advisor, Form of Study, Career, Degree Checkout Status, Expected Graduation Term, CU Email, CU Box, Local Phone, Advisor’s CU Email |
Returns a list of all students active for the prompted term |
| CU_ACAD_DEPT_STUDENT_GROUP | Prompt: Student Group Fields: Name, ID, Group, Comment, Acad Prog, Acad Plan, Subplan, CU Email |
Returns a list of all students with active student records, and with an active status in the specified student group |
| CU_ACAD_CLASSLIST | Prompts: Term, Subject, Catalog Number, Section Number Fields: Name, ID, Subject, Catalog, Section, CU Email, Campus Box, Acad Plan, Level, Grade in course |
Essentially, a class roster. Returns a list of students who have active student records, and are enrolled in the prompted course (including students who have "W' or "LW" grades in the class) |
| CU_ACAD_ORG_STDNT_DATA2 | Prompts: Term, Acad Org, Career (optional) Fields: Name, ID, Program, Plan, Current Enrollment Status, Term, Start Level, Earned Credits to date, In progress credits, Term GPA, Cumulative GPA, Form of Study, Student Email, Student Phone, Advisor Name |
Returns a list of students who have an Acad Plan within a particular Org. Career is an optional filter - leave blank to return all. Will return all students who began a term. Students who withdrew will be noted in the "Enrollment Status" column as Withdrawn. Caveats: Uses a custom table for filtering that does NOT include minors. |
| CU_ACAD_DEPT_ACTIVE_STUDENTS | Prompt: Major (optional) Fields: Name, ID, Major, Expected Grad Term, Email address, most recent Program Action, and Action Reason |
Returns a list of students who are ACTIVE in the prompted program. Caveat: Active in Program, does NOT mean actively taking courses. Students included in this query may be within 6 (completing off campus), on SPN, or a Leave of Absence. |
| CU_ACAD_DEPT_NEW_ADMIT_TERM | Prompts: Admit Term, Career Fields: ID, Name, Program, Plan, Credits, Admit term, Email |
Returns a list of students whose most recent action is MATR in the term/career specified |
| CU_SR_TRN_CREDIT_LOOKUP | Prompts (optional): Student ID, School ID, School Name, Articulation Term, External Subject Area, External Course Number, CU Course ID, External Title, CU Subject Area Fields: Student ID, Name, Career, Program, External Org ID, School Name, Status, Articulation Term, Date Posted, Posted by, External Subject, External Course Number, CU Course ID, CU Subject, CU Catalog Number, Credits Transferred, Repeat Code, External Title |
Use this query to search for transfer credit awarded. All prompts are OPTIONAL, therefore you could search by student, by school, by Clarkson course, or by some other school's course. Caveat: Does require some baseline understanding of how transfer credit is stored in the system. Does not search test credit. |
Grade Related Queries
| Query | Prompts and Fields | Description |
|---|---|---|
| CU_ACAD_GRD_MIDTERM_2US | Prompts: Term, Acad Org Fields: Name, ID, Course, Grade, Plan, Student Email, CU Box, Career, Acad Org, Term, Level |
Returns all students with 2 or more U grades within a term (midterm grades). Can run with a wildcard in the Acad Org prompt for all students, or narrow down to just those within a particular organization. Lists students and the course(s) in question, so each student will have at least 2 rows of data in the query results |
| CU_ACAD_GRD_MIDTERM_2US_COMM | Prompts: Term, Acad Org Fields: Name, ID, Plan, Student Email, CU Box, Career, Acad Org, Term |
Returns all students with 2 or more U grades within a term (midterm grades). Can run with a wildcard in the Acad Org prompt for all students, or narrow down to just those within a particular organization. Courses are NOT listed in this query, so the only duplicate rows will occur when a student is a double major or second degree. |
| CU_ACAD_GRD_MIDTERM_2US_SCHOOL | Prompts: Term, Acad Group Fields: Name, ID, Student Email, CU Box, Level, GPA, Term, Program, Majors |
Returns all students with 2 or more U grades within a term (midterm grades) with a major that belongs to the prompted academic group. Students with double majors will only display those majors that belong to the prompted academic group, for example: a student is a double major in MechE and History, and the user prompts for ENGR. The student will appear once, with MechE listed as their major, since History does not belong to the ENGR group. |
| CU_ACAD_GRD_MIDTERM_ALL_STDNT | Prompts: Term, Acad Org Fields: Name, ID, Grade, Course, Units, Career, Level |
Returns all students active in the prompted term, with a major within the prompted Acad Org, and all of their midterm grades. |
| CU_ACAD_GRD_MIDTERM_MAE | Prompts: Term Fields: Name, ID, Grade, Course, Units, Career, Level, Advisor |
Returns only students whose major belongs to the acad org 11200 (MAE), with all midterm grades, and the student's advisor. |
| CU_GROUP_ATHL_GRD_MIDTERM | Prompts: Term, Institution Fields: ID, Name, Grade, Course, Credits, Sport |
Midterm grades for all athletes. Caveat - student must have a record in ATHL_PART_SPORT in order to appear on this query. Missing student? Contact athletics. |
| CU_GROUP_ATHL_GRD_MIDTERM_2US | Prompts: Term Fields: ID, Name, Email, CU Box, Level, Cumulative GPA, Primary Program, Sport |
Returns those athletes with 2 or more U grades (midterms). Caveat - student must have a record in ATHL_PART_SPORT in order to appear on this query. Missing student? Contact athletics. |
| CU_SAS_GRD_MIDTERM_CHECK | Prompts: Term, Institution Fields: ID, Name, Grade, Course, Credits, Career |
Validation query. Returns midterm grades that are NOT S, U, N, W, or blank. Used by SAS to ensure incorrect midterm grades are not assigned to courses. |
| CU_SR_GROUP_GRD_MIDTERM | Prompts: Term, Institution, Group Fields: ID, Name, Grade, Course, Credits |
Returns midterm grades for students who are active in the prompted student group |
| CU_SR_GROUP_GRD_MIDTERM_2US | Prompts: Term, Institution, Group Fields: ID, Name, Email, CU Box, Level, Cumulative GPA, Primary Program, Group |
Returns list of students who have 2 or more U grades (midterm grades) and who are active in the prompted student group |
| CU_SR_MIDTERM_GRADES_BY_CRS | Prompts: Term, Subject, Catalog Number Fields: Term, ID, Name, Course, Grade, Instructor |
Essentially a midterm grade roster, by class. |
| CU_ACAD_DEPT_GRADE_REPORT_GRP | Returns grade data for a term, by student group. An excel version of the traditional Grade Report | |
| CU_ACAD_DEPT_GRADE_REPORT | Returns grade data for a term, by Acad_Plan. An excel version of the traditional Grade Report | |
| CU_ACAD_DEPT_DISMISSED | Prompts: Term, Acad Org Fields: ID, Name, Career, Program, Plan, Subplan, Status, Term GPA, Email, Cohort, Major Advisor |
Returns students who are subject to academic separation for the prompted term |
Advisor Related Queries
| Query | Prompts and Fields | Description |
|---|---|---|
| CU_ACAD_DEPT_ADVISOR_LIST | Prompts: Advisor's EMPLID, TERM Fields: Name, ID, Level, CU Email, Campus Box, Advisor Name, Form of Study, Appr Enrlmnt |
Returns a list of active students assigned to that advisor. A "Y" in the Appr Enrlmnt column indicates the advisor has access to remove the advising hold. Caveats: Students must be "active in program" and "active in term" (will not include students who have graduated, withdrawn, on a leave of absence, or "within 6" and not taking classes that term. |
| CU_ACAD_DEPT_ADVISOR_LIST_2 | Prompts: Advisor's EMPLID, TERM Fields: Name, ID, Level, CU Email, Advisor Name, Appr Enrlmnt |
Returns a list of ALL students assigned to that advisor, even those that withdrew during the term. A "Y" in the Appr Enrlmnt column indicates the advisor has access to remove the advising hold. Caveats: Students must have been active in the term at some point - may not include all within 6 students due to this. |
| CU_ACAD_DEPT_ADVISOR_LIST_3 | Prompts: Acad Org, Career (Optional) Fields: ID, Name, Program, Plan, Assigned Advisor, AGD, Checkout Status, Last active term, "Update Advisor" link, Appr Enrlmnt |
Returns a list of active students whose major falls within the acad org entered into the prompt, with all assigned advisors. Includes a link to update the advisor. Career filter optional. A "Y" in the Appr Enrlmnt column indicates the advisor has access to remove the advising hold. Caveats: The student's major on the advisor panel must match the student's registered major, otherwise they will appear in these results. Example, Joe Schmoe was a Chemistry MS, and has stayed on for his PhD. The academic department must update the Advisor panel to reflect that Joe is now pursuing his PhD, or his advisor will not be linked correctly to his current record. |
| CU_ACAD_DEPT_MISSING_ADVISOR | Prompts: Acad Org, Career (optional) Fields: Name, ID, Program, Plan, AGD, Checkout Status, Last active term, "Fix" link |
Returns a list of active students whose major falls within the acad org entered into the prompt, and who do NOT have a matching advisor assigned. Includes a link to update the advisor. Career filter optional. Caveats: The student's major on the advisor panel must match the student's registered major, otherwise they will appear in these results. Example, Joe Schmoe was a Chemistry MS, and has stayed on for his PhD. The academic department must update the Advisor panel to reflect that Joe is now pursuing his PhD, or his advisor will not be linked correctly to his current record. |
| CU_ACAD_DEPT_ADVISOR_NO_ACCESS | Prompts: None Fields: ID, Approve Enrollment (Y/N), Student Name, Acad Plan, Advisor Number, Advisor |
Returns advisors who do NOT have "Must Approve Enrollment" check-box checked on the Student Advisor panel. This query should be used to periodically review advisor assignments for data-entry errors. |
Course Enrollment Related Queries
| Query | Prompts and Fields | Description |
|---|---|---|
| CU_ACAD_DEPT_COURSE_RETENTION | Prompts: Term, Subject, Catalog Number Fields: ID, Name, Subject, Catalog Number, Section, Status, Grade, Drop Date, Term, Plan, Email, Campus Box, Withdrawal Code, Withdrawal Date |
Returns a list of students who are in "drop" or "withdrawn" status for a particular course (all sections) within a term. Does not return students who withdrew from the University before the term began. |
| CU_ACAD_ALL_GRAD_CRSES | Prompts: Term Fields: Term, Career, School, Department, Course ID, Subject, Catalog, Section, Title, Start Time, End Time, Meeting Days, Room, Notes, Instructor Name, Instructor Email, Course description |
Essentially, the class schedule of graduate level courses only. Caveat: Will only return a maximum of 2 meeting patterns from the class schedule. Most courses have only 1 meeting pattern (ex MWF 9-9:15AM in CB 268), however some courses may have more than one meeting pattern, such as M 8-8:50 in CB 268, and T 9-9:50 in CB 268. The query will only display Meeting Patterns 1 and 2. |
| CU_ACAD_ALL_TERM_CRSES_DIST | Prompts: Term Fields: Instruction Mode, Term, Career, School, Department, Course ID, Subject, Catalog, Section, Course Title, Start Time, End Time, Meeting Days, Room, Notes, Instructor Name, Instructor Email, Catalog description |
A repeat of CU_ACAD_ALL_GRAD_CRSES, except instead of all graduate-level courses, the query returns all courses with one of the "distance" modes of instruction at either the undergraduate or graduate level. Same caveat as above applies. |
| CU_ACAD_CEM_CERTS | Prompts: None Fields: ID, Name, Program, Plan, Expected Graduation Term, Degree Status, Completion Term |
Will return students who have the following courses passed or in progress, who have NOT been awarded a CEM certificate: CE 510, CE 506, CE 591 Contains a drill-down to view the individual courses and grades for each student. |
| CU_ACAD_CRS_ENROLLMENT | Prompts: Term Fields: Class Number, Subject, Catalog, Section, Title, Cap, Total Enrolled, (Cap minus Enrollment), Start, End, Days, Room, Instructor, Course Description |
Returns courses offered with enrollment information |
| CU_ACAD_CLASS_ENR_W_DROPS | Prompts: Term, Acad Group Fields: Subject, Catalog, Section, Title, Cap, Total Enrolled, Enrolled including Drops, Class Number, Acad Group, Instructor |
Returns courses offered by Acad Group (such as "ENGR" for all engineering courses) by term, including a count of total enrolled plus those who dropped the class. |
| CU_ACAD_COURSE_ROSTER | Prompts: Term, Subject, Catalog Fields: ID, Name, Term, Subject, Catalog, Section, Email, Major |
Returns all students enrolled in a course (all sections) for a term |
Class Schedule Related Queries
| Query | Prompts and Fields | Description |
|---|---|---|
| CU_ACAD_UNUSED_SEATS_25 | Prompts: Term, Academic Organization Fields: Subject, Catalog, Section, Room, Course Cap, Total Enrolled, Combined Section Enrollment, Unused Seats, % Unused Seats |
Returns sections of a class that had 25% or more vacant seats in a classroom. It includes the number and percentage of empty seats in the classroom. |
| CU_ACAD_CLOSE_TO_CAPACITY | Prompts: Term, Academic Organization Fields: Subject, Catalog, Section, Room, Course Cap, Total Enrolled, Combined Section Enrollment, Unused Seats, % Unused Seats |
Return sections of a class that were close to capacity (within 10 seats) during the term they were offered. |
| CU_ACAD_ENROLL_ROOM_CAP | Prompts: Term, Academic Organization Fields: Subject, Catalog, Section, Room, Course Cap, Total Enrolled, Combined Section Enrollment, Unused Seats, % Unused Seats |
Returns all courses offered by an academic organization, with the number and percentage of unused seats in the room. It includes a column for combined enrollment sections. This query should aid departments in making course offering decisions based on past enrollment. |
BI Publisher Viewer Reports
| Query | Prompts and Output | Additional Information |
|---|---|---|
| CU_TERM_PLAN | Prompts: Academic Career, Term, Academic Plan Output: A one-page per-student report for an entire major, which includs the student's primary major, academic level, course enrollment for the prompted term, and cumulative statistics (credits earned, and GPA) |
This report has been created to pull data based on majors only - it will not work on minors or concentrations. You can run this on any term - including terms that have already ended - but if you want information on courses completed and grades earned, I recommend one of the other reports listed on this page. |
| CU_SR776P | Prompts: Student ID, Term Output: A one-page report including the student's primary major, academic level, course enrollment with grades for the prompted term, academic honors, academic standing, term statistics, and cumulative statistics (credits earned, and GPA) |
This report will only return data if grades have been posted for the term in question. |
| CU_SR776PLAN | Prompts: Academic Career, Term, Academic Plan Output: A one-page per-student report for an entire major. The report includes the student's primary major, academic level, course enrollment with grades for the prompted term, academic honors, academic standing, term statistics, and cumulative statistics (credits earned, and GPA) |
This report will only return data if grades have been posted for the term in question. This report can only be run on majors - not minors or concentrations. |
| CU_SR776SGRP | Prompts: Academic Career, Term, Group Name Output: A one-page per-student report for all students active in the student group specified. The report includes the student's primary major, academic level, course enrollment with grades for the prompted term, academic honors, academic standing, term statistics, and cumulative statistics (credits earned, and GPA) |
This report will only return data if grades have been posted for the term in question. |