Skip to main content

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

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.
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
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.
Back to top