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

QueryPrompts and FieldsDescription
CU_ACAD_STDNT_DATA_ALL_DEPTSPrompt: 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_GROUPPrompt: 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_CLASSLISTPrompts: 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_DATA2Prompts: 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_STUDENTSPrompt: 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_TERMPrompts: 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_LOOKUPPrompts (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.
QueryPrompts and FieldsDescription
CU_ACAD_GRD_MIDTERM_2USPrompts: 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_COMMPrompts: 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_SCHOOLPrompts: 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_STDNTPrompts: 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_MAEPrompts: 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_MIDTERMPrompts: 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_2USPrompts: 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_CHECKPrompts: 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_MIDTERMPrompts: 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_2USPrompts: 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_CRSPrompts: 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_DISMISSEDPrompts: 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
QueryPrompts and FieldsDescription
CU_ACAD_DEPT_ADVISOR_LISTPrompts: 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_2Prompts: 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_3Prompts: 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_ADVISORPrompts: 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_ACCESSPrompts: 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

QueryPrompts and FieldsDescription
CU_ACAD_DEPT_COURSE_RETENTIONPrompts: 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_CRSESPrompts: 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_DISTPrompts: 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_CERTSPrompts: 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_ENROLLMENTPrompts: 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_DROPSPrompts: 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_ROSTERPrompts: 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

QueryPrompts and FieldsDescription
CU_ACAD_UNUSED_SEATS_25Prompts: 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_CAPACITYPrompts: 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_CAPPrompts: 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

QueryPrompts and OutputAdditional Information
CU_TERM_PLANPrompts: 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_SR776PPrompts: 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_SR776PLANPrompts: 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_SR776SGRPPrompts: 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.