- Create first query (Teaching Sched 1). Use the Schedule object. This query brings in information for (a) courses that are non cross-listed and for (b) the 400-level cross-listed courses.
- Create second query (Teaching Sched 2). Again, use the Schedule object. This query brings in information unique to the 500-level cross-listed courses.
- Run Teaching Sched 1 and then run Teaching Sched 2 by loading and submitting.
- Choose Combine Results (Join Columns) from the Results menu. Join on Cross List Code.
- Sort results (choose Filter, Sort from the Results menu). Sort by Subject Number, CRN. Save the query (it will be a super query).
- Choose Page Setup from the File menu and choose landscape orientation. You might want to change the report default font (choose Preferences from the Edit Menu).
- Choose Show as Report (GQL Standard) from the Results menu. Edit report, saving report specs every so often.
- Choose "Show as Report" from under the "Results" pull-down menu.
- Choose "GQL Standard".
- Choose "Reorder Columns..." from the "Reports" pull-down menu.
- Highlight "FML Name" and click on the "First" button. Click OK button.
- Select the "FML Name" header in the report.
- Select "Suppress Duplicates" from the "Report" pull-down menu.
- Select "Column Settings" from the "Report" pull-down menu.
- Choose the "Band Column" and "Before" options.
- To narrow the column widths, first adjust the titles of most columns, then click on the column and then drag the right column border to the left to make the column narrower.
- You can also click on the side of the "Title" column to re-size it by dragging.
- Double click on the "Query Results..." header, then click "Edit Text...".
- Enter a new title, then click "OK" twice.
- the sum of all grade points for selected courses divided by
- the sum of all GPA hours for selected courses
- Go to the All Terms (1) model.
- Open the Transcript (1) object.
- Now you need to decide where you want your new attribute to appear on the attribute list. Once you've decided, pick the attribute immediately above where you what the new attribute to appear and using its function box select the "calculation..." function. (After you have finished defining your new attribute, it's name will appear directly beneath the attribute you have selected.)
- You should now see the Edit Calculation window. Notice the Expressions box. This is where you build and edit your computed attribute. Click once on the box to begin (you should get a blinking cursor in the box). You can build an expression by a combination of typing, instating attributes, inserting functions, and/or using the keypad buttons.
- Now you need to put in the division operator. In the expression box move the cursor between the last two parentheses and press the "/" button.
- Now for the denominator. Make sure the cursor is blinking right after the / you just inserted. Select the GPA Hours attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button.
- Your new attribute has been defined. The expression should now look like this:
- Type in a name for your new attribute in the Name: field (e.g., "Grouped GPA"). Press the OK button and your new attribute will appear on the attribute list.
- Go to the All Terms (1) model.
- Select New Query from the Query menu.
- Open the Person (1) object.
- Select Student ID and click its group box.
- Select LFM Name.
- Close the Person (2) window and open the Transcripts (1) object.
- Select the new Grouped GPA attribute.
- Qualify Count in GPA Flag = "Y".
- Qualify SUBJECT CODE = "PS"
- Qualify GPA Hours > 0 (GPA Hours is in the denominator and we can't divide by zero).
- You want only UO courses to be counted in the GPA, so qualify Inst Transfer Flag = "I".
- Qualify Course Level = UG (just undergrad courses).
- Close the Transcript (1) window and open the Current Students object.
- Qualify Hours Registered > 0.
- Qualify All Majors contains |PS|.
- Qualify Level Code = "UG" (just undergrad students).
- Run the query.
- Go to the All Terms (2) Model.
- Select New Query from the Query menu.
- Open the Degree (2) object.
- Select Warehouse ID.
- Qualify Inst Transfer Flag = I.
- Qualify Degree Status = AW.
- Qualify Level Code = UG.
- Select Save Query from the Query menu.
- Type a descriptive name for the query (like "UG Degrees Awarded") in the Query Name field.
- Click save.
- Select New Query from the Query menu.
- Open the All Students object.
- Select Warehouse ID.
- Qualify Hours Registered > 0.
- Qualify TERM = the current term (e.g., 199602).
- Qualify Level Code = UG.
- Select Save Query from the Query menu.
- Type a descriptive name for the query (like "Current UGs") in the Query Name field.
- Click save.
- Select New Query from the Query menu.
- Open the Person (2) object.
- Select STUDENT ID and LFM Name.
- Close the Person (2) window and open the All Students object.
- Qualify TERM = last term (e.g., 199601).
- Qualify Student Level Code = "UG".
- Qualify Hours Registered > 0.
- Qualify All Majors contains |ANTH|.
- Now you need to use your first subquery to eliminate the students who have a degree. To do this first, qualify on WAREHOUSE ID and choose the NOT IN qualifier.
- Select Insert Subquery from the Edit menu.
- In the Saved Queries list, select the subquery "UG Degrees Awarded" that we created above. Press the Load button and the query name will appear in the qualification box.
- Next you need to use your second subquery to eliminate currently enrolled students. Qualify WAREHOUSE ID again and choose the NOT IN qualifier.
- Select Insert Subquery from the Edit menu.
- In the Saved Queries list, select the subquery "Current UGs" that you created above. Press the Load button and the query name will appear in the qualification box.
- Run the query.
Useful Queries
The following queries provide step-by-step instructions for some commonly needed Student Data Warehouse information.
Table of Contents
CLASS ROSTERAdditional info...
LIST OF MAJORS AND ADVISERS
DEGREE LISTS
DEGREE LIST FOR COMMENCEMENT CEREMONIES
SCHEDULE OF CLASSES
ENROLLMENT STATISTICS
COURSE SUMMARY
TEACHING SCHEDULE REPORT
UNOFFICIAL TRANSCRIPT
CALCULATING A DEPARTMENTAL GPA
Download these for use in GQL
You can download these queries and they will automatically be available for use when you select from the GQL menu Query->Load Query. They are identified in the list of queries with a pad-lock icon and a leading underscore ("_") character. If you have problems, consult the appropriate contact person for your operating system (Mac or PC).
Function: To duplicate the class roster provided by the registrars office.
ALL TERMS (2) MODEL
Person Object
Select Last Name (sort 1)All Students Object
LFM Name
Student ID-Function: Count Distinct
Confidentiality Flag
Qualify Term = xRegistration Object DRIVING TABLE
Select Class Standing
All Majors
Qualify Include in Enrollment = Y
Qualify & Select CRN = x
Select Grading Option
Registration Status Code
LIST OF MAJORS AND ADVISERS
Function: To count majors, to set up office advising files, to check adviser names.
ALL TERMS (1) MODEL
Person Object
Select Last Name (sort 1)Current Students Object DRIVING TABLE
LFM Name
Student ID
Confidentiality flag
Qualify All Majors contains | x |
Student Level Code = UG
Hours Registered > 0
Select Class Standing
Major1, Major2, Major3, Major4
Primary Adviser NameNote:
For minors, switch majors to minors and drop adviser name
For email addresses, also select email address in the Person Object
DEGREE LISTS
Function: To verify awarded degrees.
ALL TERMS (2) MODEL
Person Object
Select Last Name (sort 1)Degree Object DRIVING TABLE
LFM Name
Student ID
Qualify Degree Term = 199xxx
Degree Status = AW
Degree Level = UG
Qualify & Select
All Degree Majors contains | x |
Select Degree Code
Degree Department Honor Description
Degree Date
Confidentiality FlagFor all graduate degrees:
Same except Last Name = sort 3
Degree Date = sort 1
Degree Code = sort 2
Degree Level = GR
Degree Term qualify (equal to or greater than 199301)No need to select All Majors or any Honors info
DEGREE LIST FOR COMMENCEMENT CEREMONIES
Function: To identify students eligible to participate in June ceremony, and to create list and labels. To be run spring term, after degree application deadline.
ALL TERMS (1) MODEL
Person Object
Select Last Name (sort 1)Address Object
LFM Name
Student ID
Qualify Address codeDegrees Object DRIVING TABLE
Select Address Line 1
Address Line 2
Address Line 3
Address Line 4
Fall:Qualify Degree status = AWWinter:
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x01Qualify Degree status = AWSpring:
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x02Qualify Degree status in AP,CP,AWQualifying Address Code: Use RD or PR address for fall and winter grads, MA for spring applican'ts.
Select and Qualify All Degree Majors contains | X |
Degree Term = 199x03
SCHEDULE OF CLASSES
Function: For schedule planning. To supplement the Course Edit Report.
ALL TERMS MODEL (2)
Schedule Object DRIVING TABLE
Qualify Dept Code
Term
Section Status Code = A
Select in order
Subj Code
Course Numb (sort 1)
Title
CRN
Spec Approval Code
Credit Hours Min
Credit Hours Max
UO Grading Mode Code
Major Grading Mode Code
Begin Time
End Time
Room Number
Building Code
Weekdays
Instructor Names
Fee Amounts
Enroll Max
Enroll Actual
ENROLLMENT STATISTICS
Function: For up to date enrollment statistics. Easier to print than SSASECQ.
ALL TERMS MODEL (1)
Schedule Object DRIVING TABLE
Qualify Dept Code
Term
Section Status Code = A
Select Cross List (sort 1)
Subj Code
Course Numb (sort 2)
Title
Instructor (pipes removed)
CRN
Spec Approval Code
Enroll Max
Enroll Actual
Seats Available
Waitlist CountResults: Show as report.
Subtotal the cross list codes--"none" for all columns except "Enroll Actual"
COURSE SUMMARY
Function: To compare various characteristics (time, days, room, actual enrollment, fee amounts) for a course or courses over several terms.
ALL TERMS (2) MODEL
Schedule Object DRIVING TABLE
Qualify Department Code
Term = x
Select Term (sort 2)
Course Number (sort 1)
Title
Begin Time
End Time
Days
Room Number
Building Code
Instructor Name
Actual Enrollment
Fee Detail Descriptions
Fee Amounts
TEACHING SCHEDULE REPORT
Combining Results
Schedule object
- SUBJ CODE
Course Number
Title
CRN
Credit Hours Min
Credit Hours Max
UO Grading Mode Code
Begin Time
End Time
Weekdays
Building Codes
Room Number
Instructor Names
Enroll Proj
Section Status Code
Cross List Code (Sort 1)
Enroll Actual
- TERM = term prompt AND
Credit Subject = subject code prompt AND
((Course number < 500 AND Cross List Code IS NOT NULL) OR (Cross List Code IS NULL))
Schedule object
- Select:
- Course Number
CRN
Enroll Proj
Cross List Code
Enroll Actual
- Cross List Code
- TERM = term prompt AND
Credit Subject = subject code prompt AND
Course number >= 500 AND
Cross List Code IS NOT NULL
Join Columns:
- Left Results Set Right Results Set
Query Results 2 Query Results 1
- Column to Join Column to Join
Cross List Code Cross List Code
--reorder columns (1st un-order all)
--resize, rename and combine columns
--rename, title
Note: You can't edit super queries. Thats why we saved the first two simple queries so that if we needed to change the super query, we can at least deconstruct it by editing the simple queries and then recombine and save as a new super query.
UNOFFICIAL TRANSCRIPT
Select the All Terms (2) Model.
Transcript Object
Select Transcript Term (sort)Person Object
Subj Code
Course Numb
Title
Credit Hours
Grade
Transfer Subj Code
Transfer Course Numb
Transfer Title
Institution Desc
Select FML NameSubmit query.
Qualify Student ID (you may insert prompt, if desired)
CALCULATING A DEPARTMENTAL GPA
BY BUILDING A COMPUTED ATTRIBUTE
In addition to the attributes offered by the Student Data Warehouse in each of its objects, you can add your own custom-built attributes based on the attributes already available. You can save these "computed attributes" so you can use them for future queries. When you create an attribute it will be available to you but not to other users.
In this example, let's say you want to create a computed attribute that will calculate a GPA based on selected courses (say, all undergraduate courses with a "PS" subject code). Your plan for calculating the GPA is to use this fraction:
To build your computed attribute:
Sort by building the numerator (see our formula above). Select the Grade Points attribute from the scrolling Table/Attributes window by clicking on it once. Press the Insert Function button and select SUM from the list on the left. Press the Insert button. (We could type in the attribute and function instead of inserting them, but its much easier to just pick from a list and press insert).
The expression
(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS))
should now appear in the expression box. That's the numerator. If it
doesn't look right, just delete everything in the Edit Calculation window
and do step 4 over again.
The expression should now look like this:
(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS)/)
(SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GRADE_POINTS) / SUM(DATAMGR.DWS_TRANSCRIPT_ENRLSTD_VIEW.GPA_HOURS))
If you've made a mistake, edit the expression in the Edit Calculation
window until it looks exactly like the expression above.
How do you use your new attribute? Let's say you want to produce a report of all current political science majors including their names, ids, and GPAs based just on their political science courses.Here's how you do it:
Note that you grouped on ID because you wanted a separate GPA for each student. You also made sure that the course counted in the GPA and that only PS courses were used in the calculation. And you made sure that GQL wouldn't have to divide by zero. You qualified on Course Level because GPAs should always be calculated by level. And finally, you used the Current Student object to only bring back GPAs for currently enrolled political science majors.
By changing the selection, you can calculate a GPA on other groups of courses making this a very handy attribute! The above query took about 30 minutes to execute.
SELECTING STOPOUTS USING SUBQUERIES
We'll define a "stopout" as an undergraduate student who attended last term but who is not attending this term and who did not graduate. In our example, our list will be limited to Anthropology majors.
You'll use one subquery to eliminate from our list students who have graduated and you'll use another to eliminate students who are attending this term.
In the first subquery, you need to identify every student who has had a degree awarded at the UO. Then you'll use a NOT IN qualifier in our main query.
To define the first subquery:
To define the second subquery:
To define the main query:
This page was last updated on February 3rd, 1999.
