Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


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 Manager Viewer 101 Presentation | Handout


Anchor
general
general

General Queries

QueryPrompts and FieldsDescription

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.

Anchor
grades
grades

Grade Related Queries

QueryPrompts and FieldsDescription
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


Anchor
advisor
advisor

Advisor Related Queries

QueryPrompts and FieldsDescription
CU_ACAD_DEPT_ADVISOR_LIST

Prompts: Advisor's EMPLID, TERM

Fields: Name, ID, Level, CU Email, Campus Box, Advisor Name, Form of Study

Returns a list of active students assigned to that advisor

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

Returns a list of ALL students assigned to that advisor, even those that withdrew during the term

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_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.

Anchor
Enrollment
Enrollment

Course Enrollment Related Queries

QueryPrompts and FieldsDescription
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.