Queries whose name begins with jass Primary contact is Leslie Purtlebaugh, purtlela@jmu.edu Asst Vice Provost Academic Student Services. Section last updated 2/14/2018.
1. JASS01_FIRST_GEN (NTS): This query returns student aggregated data keying upon field of study, term, and academic standing. Output includes counts of students based on academic year, sex, part-time or full-time, race/ethnicity, first generation student, veteran status, number of dual enrollees, and average and cumulative GPAs. Because the user does not have access to first gen data in (JAD_FYRTRN_VW) this query must be executed by IT and output sent to the user. Query requires values for begin term, end term, and aid year.
2. JASS01_NO_FIRST_GEN (NTS): This query keys upon the same data set above except that first generation data is excluded. This query hyperlinks to a drill down query (JASS01_NO_FIRST_GEN_DD) to show the listing of individual IDs and the pertinent data, including the major for each student. Clicking the hyperlink will open a new window showing the detailed report. User must remember to check pop up blocker when drilling down to the detailed listing.
Advisors
Queries whose name begins with jadvisor. Primary contact is Mary Morsch morschmd@jmu.edu. This series created for use by advisors. All queries in this series make use of row level security via the psopralias record being added to the base query which will only return data based upon the oprid running the query. In other words, jadvisor01, jadvisor02 and jadvisor03 will only return data for users who have been assigned advisees as they are meant to be run by the advisors themselves. Section last updated 6/29/2017.
Steps to request run only query access in the Student Administration System with the role specific to advisors
Navigate to the system used to track requests and authenticate using your e-ID and password. https://remedy.jmu.edu/isaccess.asp
Most of employee information should default to values in the HRMS system. Enter all required fields in the employee section. The employee ID # is from the HRMS system. It can be found on your JAC card or within MyMadison (employee tab, View Payslip). The MSC and title, although required fields, do not have to match data in the HRMS system.
Enter the employee supervisor data. The e-ID is the important part as it drives required authorization.
Unless you already have some form of core access in the SA system, leave the default radio button selected as New for question #3.
Question #4, select Student Administration from the drop down list. Several new fields should appear.
Select the radio button which reads Academic under the Access Type field.
Select the radio button Other
In the Please Specify field enter the following value: Request run only query access with the role named jsaadvisrqryaccgrp
In the dialogue box whose description begins with Please provide specific duties or responsibilities. Enter the following value: I am an advisor and desire the ability to run the ad-hoc queries designed for the stated role listed above.
Press the Continue button. On the confirmation page, read the verbiage at the bottom of the page related to policies and procedures. If you agree, press the I agree button. You should be taken to a page which states your request has been submitted. You may close your browser.
Running the queries
Log into the Student Administration production system: https://sa.jmu.edu/psp/sa/?cmd=login&languageCd=ENG& authenticating using your e-ID and password
Navigate to the query viewer via the path: Reporting tools, Query Viewer. On the query viewer dialogue screen, enter the letters “jadvisor” in the parameter labeled “begins with” and press enter or search. The three queries named jadvisor03, jadvisor02 and jadvisor03 should appear in the results.
To obtain results, follow the hyperlink for one of the three queries labeled “run to HTML”. A new window will open requesting that you enter a term. When prompted for term, you can determine the values according to the following details. The first digit indicates the century (0 prior to the year 2000, 1 for the year 2000 and beyond). Digits two and three are the last two digits of the year; 14 would be for 2014, 10 for 2010, 11 for 2011 etc. The fourth/last digit is the month of the year in which the term begins; 1 for Spring term, 5 for any/all Summer terms and 8 for the Fall term. So a term of 1148 is the Fall of 2014.
List of advisor specific queries
jadvisor01 (TS): List of advisees by advisor. Academic Term. The academic term prompt drives which term will be used when the query returns academic related results for your advisees. The second prompt labeled: This admit term (blank 4 all) can be left blank, and all assigned advisees will be returned. For advisors with upper class advisees, entering a value in this prompt will restrict the data returned to only those advisees with the provided admit term. For those with only Freshman advisees, you can leave this prompt blank. Entering 1148 for the academic term prompt, and 1128 for the admit term prompt, will return academic data for your assigned advisees for the Fall 2014 term, but only for those whose admit term was the Fall 2012 (assumable referred to as Juniors now). Apologies for the cryptic prompt label, but they are restricted to 24 characters Returns one row of data for each advisee assigned to the advisor running the query. Data is sorted by the student’s admit term then by student name. In addition to the academic data returned, the following drill downs are available. Following the hyperlink underneath the respective field will open a new window with the given data. This new window should be closed when you’ve finished reviewing the data.
Current schedule. Follow this hyperlink to open a new window which will display the advisees current schedule of classes. In this context, current is defined as courses for the current term which has been hard coded.
All courses. . Follow this hyperlink to open a new window which will display the advisees courses ever taken @ JMU as well as all transfer courses.
Acad standing. Follow this hyperlink to open a new window which will display the advisees academic standing for all terms @ JMU.
Mid-term grades. Follow this hyperlink to open a new window which will display the advisees mid-term grades.
Holds. Follow this hyperlink to open a new window which will display the advisees holds.
Requirements. Follow this hyperlink to open a new window which will display the advisees requirements as of the last degree progress run. Each requirement has a status of either “Complete” or “Fail” which are the words used by the degree progress process.
Major/minor history. Follow this hyperlink to open a new window which will display the advisees history of all changes to their majors and minors since the begin of their academic career. Sub-plan (AKA concentrations) data is not included.
Test scores. Follow this hyperlink to open a new window which will display the advisees test scores.
Gened courses. Follow this hyperlink to open a new window which will display the advisees history of all their general education courses. The courses are broken out and sorted by the cluster then group and track data.
jadvisor02 (TS): Advisor term based data. Returns one row of data with multiple drill downs described below.
On prob/suspension. Follow this hyperlink to open a new window which will display the advisees who are (or had been) on academic probation or suspension for the most recent term for which grades have been posted. Academic action data such as probation and suspension as well as academic honors such as Dean’s and President’s list are time sensitive actions which do not occur until after grades are posted for a given term.
Holds. Follow this hyperlink to open a new window which will display the advisees holds which specifically prevent them from registering for any courses. In your SA system, what are referred to as holds are actually known as service indicators which can be either positive or negative. Negative service indicators contain flags which dictate whether or not a student is allowed to register for courses. This query only returns these negative service indicators.
Zero hours. Follow this hyperlink to open a new window which will display the advisees who are enrolled in zero credit hours for the current term.
Not full time. Follow this hyperlink to open a new window which will display the advisees whose enrollment credits for the current term fall between 1 and 11 hours.
Shopping cart courses. Follow this hyperlink to open a new window which will display the advisees who have courses left in their shopping cart for the current term. Courses which they ultimately were enrolled in are omitted.
Incomplete MREST/IIST. Follow this hyperlink to open a new window which will display the advisees who are missing the ISST (aka MREST) milestone.
Requirements. Follow tis hyperlink to open a new window which will display aggregate data for all advisees currently assigned to the advisor running the query. Data is aggregated for each degree progress requirement (such as cluster requirements) and how many advisees have failed and completed the specific requirement.
International. Follow this hyperlink to open a new window which will display the advisees who have a positive service indicator assigned flagging them as international temporary VISA holders.
Course balance. Follow this hyperlink to open a new window with one row for each currently assigned advisee and the percent of classes in two categories: MWF, and TT.
Waitlisted courses. Follow this hyperlink to open a new window with one row for each currently assigned advisee and any courses for which they are waitlisted for the term entered in the prompt.
Time conflicts. Follow this hyperlink to open a new window with one row for each currently assigned advisee and any course for which there MAY be a time conflict. In this case, a conflict is defined as any course whose begin/end times overlaps or for any course whose end time equals the begin time of the next course.
Majors. Follow this hyperlink to open a new window with one row for each currently assigned advisees and an aggregate of the number of students with the given major.
Ever taken this course. Follow this hyperlink to open a new window which will prompt you to enter a course (subject and catalog number, no spaces, such as MATH220). The query will then return one row of data for each of your advisees who have ever taken the given course, whether it was transferred or taken at JMU, and the grade.
Never taken this course. Follow this hyperlink to open a new window which will prompt you to enter a course (subject and catalog number, no spaces, such as MATH220). The query will then return one row of data for each of your advisees who have never taken the given course (transferred or taken at JMU).
Needs C1 writing. Follow this hyperlink to open a new window which will return your advisees who are missing the cluster one writing requirement.
Needs C1 critical thinking. Follow this hyperlink to open a new window which will return your advisees who are missing the cluster one critical thinking requirement.
Needs C1 human communication. Follow this hyperlink to open a new window which will return your advisees who are missing the cluster one human communication requirement.
FYR grades. Follow this hyperlink to open a new window which will allow either midterm (MID) or final (FIN) grades to be returned for your advisees for the given term.
BIO Course analysis. Returns a specific list of courses which have or have not been taken specifically written for Freshman Biology advisors.
Current schedules. Follow this hyperlink to open a new window which will return the class schedules for all of your advisees. Results can then be downloaded to Excel for further sorting and/or analysis.
ALL test scores. Follow this hyperlink to open a new window which will return ALL test scores for ALL of your advisees (may be a lot of data ) Results can then be downloaded to Excel for further sorting and/or analysis.
ALL Transfer/test credit. Follow this hyperlink to open a new window which will return ALL transfer and test credit for ALL of your advisees (may be a lot of data ) Results can then be downloaded to Excel for further sorting and/or analysis.
Study Group. Follow this hyperlink to open a new window which will return one row of data for each of your advisees if they have ever been associated with the international study group. A student may have more than one row in the results. There are fields present for both the academic program status as well as the status of the international study group.
Course Summary. Follow this hyperlink to open a new window which will return one row of data for each course to which you are assigned as an instructor for the term provided in the prompt. To view data from other terms, simply change the value entered in the term prompt.
Course Detail. Follow this hyperlink to open a new window which will return one row of data for each student enrolled in a course to which you are assigned as an instructor for the term provided in the prompt. To view data from other terms, simply change the value entered in the term prompt.
Course add/drops: Follow this hyperlink to open a new window which will prompt for a date range. Enter an appropriate date range (it can be entered in many different formats, the easiest is MMDDYY such as 072317). The query will then return a list of your advisees who have either added or dropped a course during that date range. You can then select their hyperlinked name to drill into their current schedule.
jadvisor03 (NTS): Advisor lifetime aggregates. Returns one row of data with multiple drill downs described below.
Credit hour prod. Follow this hyperlink to return aggregate data for each term the advisor was ever an instructor and their credit hour production (CHP) for each course. CHP is defined as the number of enrolled students x course credit hours.
Advisees by term. Follow this hyperlink to return aggregate data for each term advisees have been assigned to the advisor. Returns on row for each term and specific aggregate data about the advisees.
Historical list of advisees. Follow this hyperlink to return ALL ADVISEES EVER ASSIGNED TO YOU IN THE HISTORY OF YOUR EMPLOYMENT. The default sort is by the student ID#. And yes, if you’ve been here many years and had hundreds of advisees ever assigned, these results may not be of value to you.
jcompscixx. This series of queries was cloned from the jadvisor01 query specifically for Chris Mayfield, a Freshman advisor in Computer Science. Although they are named for computer science, they can be run by anyone with advisees assigned.
jcompsci01: 1 row per advisee test scores. Returns one row of data for each advisee and the following test scores: JMU Math placement calc, JMU Math placement statistics, SAT math, SAT verbal, ACT math, ACT English. The query also returns the advisees orientation date, timeslot and number of guests.
jcompsci02: All test scores for advisees. As the name implies; one row for each advisee and each of their test scores. ALL test scores.
jcompsci03: Transfer credit. Returns a row of data for each advisee and their test and transfer course credit accepted by the office of the registrar.
jcompsci04: Advisee schedules. Returns one row of data for each assigned advisee and each of the courses for which they are enrolled for the prompted term.
jcompsci05: Advisee schedules V2. Returns one row of data for each assigned advisee and each of the courses for which they are enrolled for the prompted term. This version includes additional fields, such as meeting pattern and meeting location. Was designed to be used for the creation of a advisee calendar.
Jadvr series.
Most of the jadvrxx series were written to support drill down features from the jadvisor01-03 queries. Exceptions to that logic follow.
Running the advising records report as an advisor. The office of Career and Academic Planning (CAP) run a report referenced by two names; yellow sheets and the advising records report. The results are received by advisors just before their orientation sessions. Advisors with access to run queries can run this report themselves by following the steps below. The steps to run this report the first time are, rather involved, subsequent runs are much fewer steps.
Running the advising records report the first time
You will need to be logged into the SA core application, which is the same system from which you run “the advisor queries”, this is NOT MyMadison
Navigate: Reporting Tools, BI Publisher, Query Report Scheduler.
Click the hyperlink labeled “Add a New Value”. You will be need to enter what is known as a run control ID. As I share with folks, a run control can be named anything you desire (zippity-do-dah). Since I run so many reports, I always name my run control the exact same name of the underlying reporting object. In this case, the report is named JCAP14. For this documentation, I will name my run control JCAP14, and press the Add button. You will advance to the Query Report Scheduler page.
Use the drop down of the Data Source Type, and select “Connected Query”
In the Report Name field, type “JAP14” and press the lookup/magnifying glass. Do not key any other data on the query report scheduler page, press the Save button in the bottom left, then the Run button in the top right. You will advance to the Process Scheduler Request page.
Use the Server Name drop down list to select the server named PSUNX.
Use the Type drop down list to select Email, leave the format as PDF.
Press the Distribution hyperlink. You will be advanced to the Distribution Detail page.
In the email subject line field, enter whatever text is meaningful TO YOU. The results will be coming via an email from the SA system, so whatever you enter in the Email Subject line is what you will see when the email is sent, to just you.
IF you so desire, you can also enter “something meaningful” in the Message Text” field. Leave all other fields blank, and press OK. *** A warning; IF you enter a valid email address in the Email Address Text box, IT WILL EMAIL YOUR ADVSEEE RECORDS TO THIS EMAIL ADDRESS. Apologies for the shouting.
Running the advising records report subsequent times
Navigate: Reporting Tools, BI Publisher, Query Report Scheduler
IF you have scheduled more than one report ever, you will need to press the Search button and select the named run control you entered above in step 1, otherwise the contents of your run control will appear.
Click the Run button to advance to the Process Scheduler Request page, then OK to submit the report request. The report will run and arrive shortly via email
