Queries whose name begins with jhs. Primary contacts are Dee Nilsen nilsenda@jmu.edu and Karen Sampson sampsokh@jmu.edu . Many of the queries in this series were developed by the functional users, and so no documentation exists.
JHS_CONTRACT_ETHNICITY (NTS): Prompts for a housing period. Returns one row of data for each student with an active housing contract for the housing period and each of their ethnic values which are self reported.
JHS_DEFERRED (NTS): prompts for a single admit term. Returns students who have deferred enrollment for the given admit term and who are no longer active in their academic program. Such students should NOT be housed due to their status.
JHS_DISCONTINUED_MONTHLY_COUNT (NTS): Prompts for a range of action dates. Returns aggregate counts of the major reason the undergraduate students discontinued their academic program in the given month for the stated reason.
JHS_EREZLIFE_DATA (TS): Prompts for a term and a housing period. This query is scheduled by Dee Nilsen to send results daily to the StarRez housing system (originally erezlife, hence the query name). See also the CRON job which sends the actual file to the database server where a script is then run to import the data. Dee uses a run control named eRezlife_Staff and a recurrence definition named erezlife 5:15am weekdays. When scheduled, the parms to use are: type=file, format=txt, output destination=/udata/sa90/. This will leave the file on the SA report server with the file name jhs_erezlife_data-999999.csv with the number at the end being the process instance number. The actual file is consumed by the daily process, so you will not find it on the server after the import process has run.
JHS_EREZLIFE_DATA1 (TS): Similar to jhs_erezlife_data documented above. Dee uses a run control named eRezLife.
JHS_EREZLIFE_DATA2 (TS): The email address field present in the two above queries needs to be translated from @DUKES to @JMU. The only purpose of this query is to maintain a list of the SA emplids which need to be translated. Embedded within each of the two queries above is an expression used to translate the returned JMU email address for this population. Those expressions MUST be updated each time Dee provides the list of SA emplids.
JHS_FIND_CREDIT_HOURS (TS): Prompts for an academic term and a housing period. Returns those students who have an active housing contract for the period who have fallen BELOW 4 credit hours for the term.
JHS_KEYCODE_LOOKUP (TS): Prompts for a housing period and an optional key code value. Returns active housing data including the searched for key code value. If the key code prompt is left blank, all housed students are returned.
JHS_LDAP_SEARCH (TS): Prompts for a single SA emplid. Returns the JMU unique ID as well as the HR Emplid for the provided SA emplid.
JHS_MATR_FYR_EARLY_ADMT (TS): Prompts for an admit term. Returns true freshman (admit type=FYR) whose application has matriculated and indicated an interest in housing.
JHS_ORIENTATION_EXTRACT (TS): Prompts for an admit term and a student type. Returns data whose original purpose was to allow reconciliation with the visual zen orientation system.
JHS_RCF01 (TS): Prompts for a housing period. Returns a list of students who have not completed a room condition form.
JHS_RCF02 (TS): Prompts for a housing period and an optional Y/N prompt to indicate IF the bill student=Y flag has been set for the given room condition form.
JHS_RCF03 (TS): Prompts for a housing period. Returns housed students who have matching key issued numbers for the same room.
IDLS
Queries whose name begins with jidls. Primary contact is Donna Garber garberdv@jmu.edu Section last updated 10/7/2013. This series consists of queries cloned from various jusds queries for IDLS (defined by two academic plan numbers 0000006500 and 0000064000).
Institutional Research
Queries whose name begins with jir. Primary contacts are Tina Grace and Lou Hedrick; although a number of the queries were written for Chuck Dehart. Section last updated 3/15/2012. This series cloned from various jusds queries for Institutional Research who has access to create/modify their own queries. Most of the series were either created or modified by IR staff who call occasionally for a tweak or advice.
Intercollegiate Athletics
Queries whose name begins with jia. Primary contact is Scott Arbogast arbogasw@jmu.edu Section last updated 11/29/2017.
JCC5025_OR_ATHLT_STDNTS (TS): Athletes Orient Assignments. Includes prompts for admit term and admit type (transfer or regular Freshmen). Returns orientation data (which session, etc.).
JHS_0001KHS (TS): Active Athletes by date for HS. Includes a prompt for as of date. Date applies to those who are student athletes as of the given date. Apparently used by the housing office to determine if a student athlete is eligible for housing.
JIA0001_ATHLETES (TS): Active athlete listing. Includes no prompts; returns all active athletes as of current date. Sorted by sport, then athlete name. Apparently the NCAA compliance person @ JMU (Jenn Philips) is responsible for activating and deactivating athletes.
JIA0002 (NTS): Ttl hours taking by career. Includes a prompt for a given term and a specific value to be used for undergraduate total enrolled hours and graduate enrolled hours. Returns active student athletes who are enrolled in less than the prompted for value for the given term. Includes a union in case there are students who do not have a single record in stdnt_enrl (i.e. never enrolled in a single course for the term).
JIA0003 (NTS): Active Athletes as of date. Almost identical to JHS_0001KHS. Includes an as of date, and returns all student athletes active as of that date.
JIA0003B (TS) replaces sqr jia003 and used with an rtf by the same name for BI publisher report. Four field labels in the RTF shell document need to be changed each term: EARNED=TOT_PASSD_GPA, ATTEMPTED=TOT_TAKEN_GPA, TOTAL=TOT_CUMULATIVE and CUM_GPA. Change the hard coded values for the three terms in this query. Make sure to check with Scott Arbogast in athletics BEFORE making the changes.
JIA0004 (NTS). Athlete term report. Includes a prompt for term and a sport (optional). Returns academic information about student athletes active for the given term; such as their term and cumulative gpa, number of hours taken, etc. Note: includes students enrolled in zero hours for the term, so you’ll need to watch the column labeled term taken progress to determine if you really want to include students with zero hours.
JIA0004B (TS): Student Athlete grade records. used with rtf by same name and BI publisher report to replace sqr named jia0004.sqr
JIA0005 (TS): FA data for athletes. Includes a prompt for the financial aid year (enter four digits). Returns financial aid award information for active athletes. Note: does NOT include any awards related to summer awards; it is hard coded against the word summer appearing in the description of the award.
JIA0006 (TS): Active athletes; passed requirements. Includes no prompts. For all active athletes, returns the total number of degree requirements they have completed, and not completed based upon the latest degree progress report. This would be similar to running a degree progress report on all athletes, and summarizing the total number of complete/incomplete degree requirements.
JIA006A (TS): Active athletes; gened rqrmnts. Prompts for the current term. Returns all active athletes and whether or not they have completed the general education cluster one requirements. A few notes about this query.
An athlete will NOT appear if they are brand new freshmen. That is because the query is based upon the degree progress report, which will NOT have been run for incoming Freshmen.
The three columns for the cluster areas return a value of 1 if the student has completed the requirement, a value of zero if not. It may take a little getting used to looking at the numeric data, but is pretty straight forward. It also has a side effect of allowing you to run numeric totals for the columns which if placed at the end of the results, will equal the number of athletes who have completed the specific requirement. You could also count the zeros to report the number who have not yet completed. IF it makes more sense to reverse that logic, I can do so. A value of 1 would then mean they have NOT completed the requirement, and you could total those values.
The degree progress report which the query is based upon is only run once a term. So the query results will lag behind that process by a few weeks. For example, IF you ran the query AFTER December 2010 grades were posted and BEORE the degree progress report was run for all undergrads, the results would not reflect their most recent grades. The running of degree progress for all undergrads normally lags about 2 weeks behind the posting of final grades.
JIA0007 (TS): Student Athletes Class Adjust. Includes prompts for a date range of when student athletes made courses changes (adds, drops, etc.) Returns appropriate data regarding who made what class change, and when they made it.
JSR_ATHL_DEPT_ATHLETES_W_HOLDS (TS): List of athletes with holds. Includes no prompts; returns a list of active athletes and the holds.
JIA0008 (TS). Student Athlete Honors. Includes a prompt for term; returns those active student athletes who have received academic honors for the given term.
JIA0009 (NTS): Student Athletes w/incompletes. Prompts for a term, returns a list of students and the courses they received an official grade of incomplete in.
JIA0010 (NTS) Athletes acad standing by term: includes a prompt for a term range. Returns active athletes AS OF THE TERM END DATE for the given term. Returns the student’s academic standing for each term included. Sorted by athlete name and term.
JIA0010A (NTS) Avg GPA by term and sport. Run it for any term range you desire. It returns one row of data for each sport and term (uses the end date of the provided term). Includes the average term GPA, average cumulative GPA and an athlete head count. The first row is for ALL undergraduate career students and subsequent rows for each sport. JIA0010A1 is the same, except the academic standing data is not included.
JIA0010B (NTS): Avg GPA by term. Includes a term range. Different from others in this series as it returns data based upon three different populations. It aggregates the average GPA by term for all athletes, all non athletes and then everyone together. The descriptions used for the “Population” is set the way that it is so that sorting is as desired. Allows IA to see how athlete GPA’s stack up against the other populations. JIA0010B1 eliminates undergraduates who are NOT full time for the given term (12 hours is considered full time for undergraduates). JIA0010B2 returns the same data, just by academic year range vs. a range of terms. It returns a distinct count of each population. JIA0010B3 adds the primary major of the undergraduate student as a comparison field.
JIA0011 (NTS) Class schedules 4 athletes dtl. Prompts for a term, returns the class schedules for all student athletes for the given term. Within excel, you can sort and select specific sports, courses etc. to view actual enrollment information.
JIA0011A (NTS) Class schedules 4 athletes sum. Similar to jia0011, includes only summary data by course (includes ALL sections) with a percentage of a course that is athletes.
JIA0011B (NTS): Courses taken by athletes sum. cloned from jia0011a for Scott Arbogast, wanted to know which courses were being taken by athletes for specific terms, need to know which sport.
JIA0011C (NTS): Class schedules 4 athletes dtl. Cloned from jia0003 for Scott Arbogast. Needed to know athletes in specific courses. Need a detail and summary version. 8/29/2011: needed a version which allowed users to determine which courses were scheduled after 6 PM
JIA0012 (NTS): Athlete graduates by date. cloned from jusds007b for Scott Arbogast. I think the current design of the jia0012 query will meet your needs. This may be more information than you need to know, but the query is designed to return everything except inactive athletes as of the day BEFORE the degree conferred date. What this really means to you, is you can use the query to find student athlete graduates from anytime in the past and the results will be accurate as of the date the athlete graduated.
JIA0013 (TS). Query originated a jus150 which Rick Deloney use to run for Elaine Buthe. Pete took over sending her the data on 2/15/2011, so renamed the query jia0013 to be part of the jia series of queries.
JIA0014 (TS): Athlete plan changes. Cloned from jusds025 and logic added for athletes. Returns a list of athletes and the plan changes made by them for the given date range.
JIA0015 (TS): Athlete appointment times. Prompts for a term and a sport (optional) and returns enrollment appointment times for student athletes.
JIA0020 (TS): GPA for athletes, all plans. Built over the degree progress “R” tables. Returns major GPA for athletes for a given term.
Running the two Athletics Admissions Reports. These two reports use to be crystal reports run from a local PC and viewed by the various coaches. As of 11/22/2010, the reports were rewritten using the BI PUBLISHER reporting tool. The reports may be run interactively or scheduled and the results viewed via report manager.
To run the individual student report, press the hyperlink next to the JAD_ATHLETE2 report labeled View Report. Enter a students emplid and press OK. The report results will appear in a new window. To view a different student’s data, repeat the process.
If you are logged out of the system due to inactivity, click the hyperlink to log in, then follow the steps to run the reports.
To view the long report, find the PDF file on the desktop and double click it. The file should be deleted at the end of the day and the report rerun to ensure the most recent results are available.
The athlete list report is a BI Publisher report scheduled to recur M-F at 7 AM and email the results to Suzann Meyerhoffer and Steve Henderson meyerhsl and hendersw. The recurring job is scheduled by Pete DeSmit. On 3/17/2017, the original report named jad_athlete1 was inactivated and rewritten as jad_athlete3. This was required due to the report being rewritten using a connected query as the data source per Suzanne’s desire to include a new field indicating which checklist items were missing for a given applicant. On 1/11/2017, changed the hard coded term to use term end date logic so would no longer have to modify the query.
Running the intercollegiate athletics advisor queries. Unlike major and freshman advisors, student athlete advisors are assigned by sport. Some student athlete advisors are assigned athletes from 8 different sports. Also, these advisors are NOT actually assigned as an advisor of record (no stdnt_advr_hist data). This series of queries was developed given this logic. Only active participating student athletes are returned by any/all queries in this series.
JIAAD01 (TS): List of athletes by sport. Prompts for a single (current) academic term, from 1-8 different sport values and a Y/N prompt for whether the user desires all sports be returned by the query. The last 9 prompts are universally present in all queries in this series. The prompt design allows the advisors who has advising responsibility for 1-8 sports to use the first 8 prompts, OR leave those 8 prompts blank and enter a Y in the all sports Y/N prompt which returns all active student athletes.
Current schedule (JUSDS024J). Select this hyperlink to open a new window which will return the current course schedule for the selected student athlete.
All course detail (JIADVR11). Select this hyperlink to open a new window which will return all courses ever taken or transferred for the selected student athlete. Courses may appear more than once if they were used to meet multiple degree requirements.
All course summary. Select this hyperlink to open a new window which will return a summary of all courses ever taken or transferred for the selected student athlete. Includes the academic standing for the given term.
Mid-term grades (JUSDS006_MIDTERMGRADES). Select this hyperlink to open a new window which will return any/all mid-term grades for the selected student athlete. Mid-term grades are not entered by all instructors, and only for the students first term.
Holds (JUSDS006_HOLDS). Select this hyperlink to open a new window which will return any/all holds currently assigned which prevent registration for the selected student athlete.
Requirements (JUSDS006_REQUIREMENTS_A). Select this hyperlink to open a new window which will return all degree requirements (completed or failed) for the selected student athlete.
Major/minor history (JUSDS006_PLANHISTORY). Select this hyperlink to open a new window which will return a history of all majors and minors for the selected student athlete.
Test scores (JUSDS006_TESTSCORES). Select this hyperlink to open a new window which will return a list of all test scores ever received for the selected student athlete.
Gened courses (JUSDS006_GENED). Select this hyperlink to open a new window which will return all general education courses ever taken by the selected student athlete.
JIAAD02 (TS) Details by term. Contains the same prompts as jiaad01. This query does NOT immediately return ANY usable data. After entering desired values for the prompts, press Enter or search and the query will return one row of data with a count of the student athlete population selected. Follow the desired hyperlink for a given “situation”. Like all other drill downs, following a hyperlink will launch a new window and return the described results.
On prob/suspension (JIADVR01). Select this hyperlink to open a new window which will return a list of all selected student athletes who were on academic probation or suspension for their last graded term .
Holds (JIADVR02). Select this hyperlink to open a new window which will return a list of all selected student athletes who have holds which will prevent them from registering for courses.
Not full time (JIADVR03). Select this hyperlink to open a new window which will return a list of all selected student athletes whose current course load is between 0-11 hours.
Time conflicts (JIADVR04). Select this hyperlink to open a new window which will return a list of all selected student athletes who have time conflicts with their current schedule.
Requirements (JIADVR05). Select this hyperlink to open a new window which will return a list of all selected student athletes and the status of ALL of their degree progress requirements. This may be a very long list.
C1 Writing (JIADVR06). Select this hyperlink to open a new window which will return a list of all selected student athletes who have NOT completed their cluster 1 writing general education requirement.
C1 Crit Think (JIADVR07). Select this hyperlink to open a new window which will return a list of all selected student athletes who have NOT completed their cluster 1 critical thinking general education requirement.
C1 Hum Comm (JIADVR08). Select this hyperlink to open a new window which will return a list of all selected student athletes who have NOT completed their cluster 1 human communication general education requirement.
Course Add/Drop (JIADVR09). Select this hyperlink to open a new window and require you enter a date range in the 2nd and 3rd prompts; leave all other prompt values alone. After entering a date range, press enter/search. The query will return a list of courses for the given student athletes selected who either added or dropped a course in the given date range.
