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

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.


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.

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.

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

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


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

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

Anchor
Schedule
Schedule
Class Schedule Related Queries

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

Anchor
BIP
BIP
BI Publisher Viewer Reports

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