Learn to Build Queries Using Student Academic Data

Learn to Build Queries Using Student Academic Data MIDHEUG Alliance 2015 October 8, 2015

Brief NIU Overview & Presenter • Converted to People. Soft beginning with our Fall 2008 term • Campus Solutions 9. 0, People. Tools 8. 54 • Presenter – Dede (Deirdre) Young – Asst Mgr Operations Support, Office of Registration & Records – Tendency to speak fast, so please ask me to slow down – Working with student data in terms of reporting since 1998 • Super. Wylbur, Crystal Reports, i. Strategy, PS Query

Safe Harbor Statement • All aspects of this presentation are based on data from the student system at NIU used to report student data • Variables in the examples may be different than those used at your institution, depending on your academic structure • Names and IDs are coded, hidden, or changed • It is always best practice to begin writing a new query, especially complex queries, in a test environment

Get Ready! • Please take this time to connect to the Wi -Fi and log in to your People. Soft environment. – Login and Password in the front of the program

Experience? • No PS query experience? • Some PS query experience? • Lots of PS query experience? – Perhaps teach me a thing or two?

Workshop Exercises • Tasks (as many as time permits) – ‘Currently’ enrolled students in a college, or academic program – Subquery to exclude students with an attendeespecified service indicator (since institutions will have different codes) – Identify students in an attendee-specified student group – Calculate age using current date and term begin date – Subquery to identify first enrolled term in career – Drilling URLs • To another component/page • To an external website • To another query – Write drill-down queries to Student Groups and Milestones

Left-Outer Joins & Effective Dates • Effective-dated records can be left-outer joined, while maintaining the dateeffectiveness and not losing any records • Add a second piece of criteria on EFFDT – EFFDT is null – Use the ‘OR’ logical between the two EFFDT criteria and group

Left-Outer Joins & Effective Dates • Example

Subqueries – Overview • What is a subquery? – A query within a query – MIN or MAX value of a field, such as STRM – Exclusionary • Not in List – Inclusive • In List

Drilling URL • A Drilling URL is a type of expression that ‘drills down’ to something else – Drilling URLs can enhance your query results, giving additional information beyond those results

Drilling URLs • Five to Six types, depending on the People. Tools version – Query URL – Drills down to another existing query based on matching fields – External URL – Drills down to an external webpage • Options of identifying the Portal Name and Node Name – Free Form URL – Drills down to an external webpage – Component URL – Drills down to an internal component or page based on search fields – Attachment URL – Drills down to an attachment – Image URL – Drills down to an image • This is new to PT 8. 54

People. Soft Query Workshop • Thank you for attending! • On to the exercises….