Queries whose name begins with jpsyc. Primary contact is Colleen Moynihan moynihcm@jmu.edu Section last updated 3/20/2012.
Jpsyc001 (TS): cloned from jusds001f for Colleen Moynihan in psych. She loads the data from the query into qualtrics for course evaluations and needs the data in a specific format.
Psychology (Graduate)
Queries whose name begins with jgradpsyc. Primary contact is Carl Guerin guerinca@jmu.edu Section last updated 3/20/2012.
Jgradpsyc001 (TS): List of grad psych students. Returns a list of students active in 9 different academic programs which comprise the graduate psychology program.
Jgradpsyc002 (TS): List of grad psych applicants. Similar to jgradpsyc001, but deals with applicants vs. matriculated students.
Jgradpsyc003 (NTS): Schedule of classes GRPsyc. Returns a course list of all grad psych courses for a given term.
Jgradpsyc004 (TS): List of grad psych schedules. Returns course schedule information for students active in any of the 9 grad psyc programs.
Student Records
Office of the registrar. Documentation related to objects written specifically for the office of the registrar. Many of these queries have BI PUBLISHER reports built over them, some involve bursting. Primary contact is Martha Ringwald ringwama@jmu.edu Section last updated 2/13/2015.
History (more for Pete than anyone else) All of the RTF documents originated from the Office of the Registrar as did the original queries the reports are built over. Changes to the underlying RTF documents can be made by anyone with what is known as level 2 query write access. Martha Ringwald, Meghan Donley and Vanessa Breeden all have this level of access.
Special instructions when running jsr BI PUBLISHER reports which contain bursting
PRIOR to running any report which utilizes bursting, please send a courtesy email to is-sa@jmu.edu with some form of verbiage about the report you are about to run and the approximate number of emails expected. This email address receives ALL replies if the person who receives the email presses the reply button. Auto replies are received as well (the fact that an instructor is out of the office).
Each report utilizes a MS Word document of file type .rtf (Rich Text Format). Make sure the appropriate RTF document BODY has been updated to reflect the most recent dates, directions, etc. If you fail to check the contents of the MS Word template document ahead of time, the merged data may be in an old format and reference old dates, etc.
Once you’ve verified the body of the appropriate RTF document is accurate, proceed with the next step. Better to check twice than use a shell document with OLD dates. All BI PUBLISHER reports which include bursting have been modified to allow them to be run in a TEST mode. To test any such reports, run the report and enter your EID in the last prompt. Running a report using your eid as the last prompt will cause the report to send all email to the eid entered rather than the actual person (student and/or instructor). To run the report for real, run the report again and blank out the last prompt.
Verify the data to be used as part of the burst process. Navigate to the Reporting Tools and run the underlying query with the appropriate values for the prompts. ALL data used by the BI PUBLISHER report comes from these underlying queries. If the data returned by the query is WRONG, the bursted reports will be using this inaccurate data. The following BI PUBLISHER reports include bursting (send email as a result of the report being run vs. producing a hard copy output:
jsr_xml101: grade roster release; email is sent to the instructor of record reminding them that grade rosters are now available to enter. Includes a list of all courses they are teaching. Notifies faculty that the grade roster has been released and is ready for them to enter grades. Prompted by term, grade roster type, sessions and status. Also has userid option to test or leave blank to run. Notes: 2/1/2011: cloned from jsr5029, see rtf doc with same name. Per Martha Ringwald, is used to email (via bursting) the list of courses that have grades missing from the grade roster. 2/21/2011 is also used for Mid-term grade reminder emails to faculty with freshmen in the class. 7/22/2011: modified to allow user to run in test mode by entering their eid as last parm. Run by Lindsey Mitchell
jsr_xml01a: mid term grade reminder. Same population as jsr_xml101, but verbiage is related to mid term grades. Run by Lindsey Mitchell
jsr_xml02: instructor confirmation. Similar to jsr_xml101 in that it is sent to the instructor of record, this version tells them when final grades must be entered. Identifies the primary instructor who is responsible for entering grades uses query JSR_INSTR_ENROLLED to select by term and session code. 2/1/2011: cloned from JSR_INSTR_ENROLLED, see rtf doc with same name. 7/22/2011: modified to allow user to run in test mode by entering their eid as last parm. See issue and op ITI05347 for additional details. Run by Lindsey Mitchell
jsr_xml03: incomplete grades reminder to instructors. Sends a list of student names and courses to the instructor for those with incomplete grades. Notifies faculty that they have students with outstanding “I” grades prompted by term. 2/1/2011: cloned from JSR_INC_GRADES_ALL_INSTR_EMAIL, see rtf with same name. 7/22/2011: modified to allow user to run in test mode by entering their eid as last parm. See issue and op ITI05347 for additional details Run by Lindsey Mitchell
jsr_xml05: UG grad apps and total possible. Email is sent to the student based upon the fact they have applied to graduate, but are lacking requirements such as total hours, etc. Identifies UG students that have applied to graduate but do not have a COMP row on program action. Prompts by term and expected grad term excludes students that have transcript text=”Began Second Degree”. 9/2/2011: Cloned from JSR_DFD_UG_APP_TO_GRAD for Vanessa Breeden to send emails to students who need additional courses in order to graduate. Run by Vanessa Breeden
jsr_xml06: Emails students who have a pending associates degree. Identifies students with a pending Associate degree from VCCS and to send an official transcript that lists the approved Associate Degree conferral to the Office of Admissions. Need to update admit term at each run and enter term prompt. 9/5/2011: created for Martha Ringwald in office of the registrar. Used to email students who have a pending associate’s degree. Learned after sending initial email that UG admissions does NOT mark the 803 (pending associates degree) as complete, rather, they enter a NEW external degree record. So changed the query to perform a does not exist subquery looking for this second record in the same table. The subquery SHOULD be set to state DOES NOT EXIST. For a one time run, set it just the opposite so we could email a retraction to 155 students who received the email in error because we did not know to check for the 2nd external degree record. Run by Kaitlyn Quinley
jsr_xml07: Unused permission, time ovrd=N. Emails the student they have an override for a course which has not yet been used. Identifies unused departmental over-ride permissons and prompts by term and expire date. 9/2/2011: cloned from jusds003 for Martha Ringwald. On class_prmsn is a field named SSR_OVRD_TIME_PERD. Y in this field indicates the student has been granted a time period override. A N in this field indicates it is a normal add override. There are students who have both types of overrides, so if we follow Kurts initial wishes, students like 105795322 will receive two separate emails. One for the unused overrides they have with NOs in the time field, and another email for the unused overrides with a YES in the time field. If you want to combine them into one email, we could add the field to the email so the students would know which courses had time overrides. 1/15/15: Updated query so that all students are selected that have any override. Run by Arin Hawse
jsr_xml08: Unused permission, time ovrd=Y; same as above, except for the time override value. Identifies unused departmental permission during the late add period but have yet to do so. 9/2/2011: cloned from jusds003 for Martha Ringwald. On class_prmsn is a field named SSR_OVRD_TIME_PERD. A Y in this field indicates the student has been granted a time period override. A N in this field indicates it is a normal add override. There are students who have both types of overrides, so if we follow Kurts initial wishes, students like 105795322 will receive two separate emails. One for the unused overrides they have with NO¿s in the time field, and another email for the unused overrides with a YES in the time field. If you want to combine them into one email, we could add the field to the email so the students would know which courses had time overrides. Run by Arin Hawse
JSR_XML09/09a VA Veteran Benefit Data: VA benefit debits/credits created by desmitpa 9/16/2011: originally cloned from the query JSR_VA_FINAID so could maintain the item type list. 9/16/2011 cloned from jsr_va_hours for use with xml version of report. 12/9/2013: changed sort back to be by group descry, then name after receiving email from Kelly Burch the sort had changed although I do not know why, it had been changed to be group descry then emplids. Run by Kelly Burch
JSR_XML10 VA Hours Changes: 10/3/2011: part of a connected query by same name which also reuses another query named jsr_xml09. This version written for Trudy Ham to return VA students who have had changes between prompted for date range. In this case, a change is defined as one of two things ocurred. Either they changed their primary major OR their enrollment changed which MAY affect the 10 columns of data being returned by the query which are all keyed into th eVA tracking system. Trudy understands thsi is the best the query tool can do. Cannot tell her which of the 10 columns of data MAY have changed, just that their enrollment has changed and she needs to research further. Run by Kelly Burch
JSR_XML11/A/B Request for Vet Educ Benefits: 3/16/2012: driving query to the connected query with same name. See also children with A and B suffix. Used for the xml report using bursting to email the student their request for veterans educational benefits data. 3/15/2012: child of connected query jsr_xml11. Returns course data for the request for veterans educational benefits xml bursted report named jsr_xml11. 3/15/2012: used with connected query jsr_xml11. This query returns student group data. Run by Kelly Burch
JSR_XML14: Applied to graduate email. Identifies students that have applied to graduate with a program action of DATA and Program Reason of GRAD. It sends them information regarding their name, address, graduation term and currently declared majors/minors. Run by Martha Ringwald (scheduled on recurrence every day)
JSR_XML14a: Applied to graduate email to advisor. Sends email to the advisor notifying them of their advisees that have applied to graduate. Run by Martha Ringwald (scheduled on recurrence every day)
JSR_XML15: Class permissions bursting report. Aggregate data regarding permissions generated for classes for which you are a course administrator. # of total permission for class section, # of permissions granted to students and stay in class, # of permissions granted to students and drop out of class, # of permissions generated but not used, # of permissions granted during the Add with Permission time period, enrollment capacity set by dept., # of students enrolled in class. Run by Arin Hawse
JSR_XML17: Go Army ed bill master. 6/24/2013: cloned from jsr_xml11. Refer to ITR07399 for additional details. Emplids to use for testing: 100542255.
JSR_XML18: DISC and LEAV Not returning. Identifies students that have a DISC or LEAV row on acad prog and is not returning. Run by Arin Hawse
JSR_XML19: Adjust probation sched memo. Identifies students on PRO that are enrolled in more than 12 hours and do not have permission (max units). Run by Lindsey Mitchell
JSR_XML20: PRO Stdents GT 13 Assoc. Dean. Sends notification to Associate Dean regarding students in their college that are on academic probation and enrolled in thirteen or more credits. Run by Lindsey Mitchell
JSR_XML22: RLOA ACTV RADM entered. Identifies students that have been RADM, RLOA or ACTV in the last 24 hours and sends email to helpdesk@jmu.edu Run by Martha Ringwald (scheduled on recurrence to run every evening)
JSR_XML25: Holds preventing registration. Notifies students that have a hold on their record that prevents registration. Run by Arin Hawse
JSR_XML26/A/JSR_XML26_DATE/JSR_XML26ADATE: Summer Enrl for Degree studnts. Notifies students based on the sessions they are enrolled in what the enrollment dates and deadlines. See notes in XML folder regarding differences between four queries. Run by Martha Ringwald/Arin Hawse
JSR_XML_DUP: Duplicate Transfer Test Credit. Notifies students that are enrolled in a class in which they have already earned test credit. Run by Kaitlyn Quinley
JUSDS005F/A: Instructor Schedule w/rosters BIP. 9/5/2014: this version for use with the BIP report. See ITI14762 for additional details. Strange header row in RTF due to how customer desired layout of data and desire for course and instructor data to be "repeated" when data spanned multiple pages. Looks funky, but works and customer says OK. Connected query by same name required due to those courses with multiple meeting patterns, locations, etc. Added psroleuser as a subquery for the sole purpose of security. The dept heads and dept secretaries do NOT have access to this record, so they cannot wander into this BIP report or query and run it by accident.
JSR5918_GRADE_AUDIT_XML: Grade audit query for XML rpt. 10/21/2010: cloned from jsr5918_grade_audit. Replaces crystal report by same name. use xml so can use bursting by instructor. 9/13/2011: modified criteria to only join from a to b using the 10 characters of the change date not the time as it was causing records to be left off. 1/4/2013: added criteria to eliminate rows where the before and after grade data was exactly the same as this was causing confusion for instructors. Run by Lindsey Mitchell
Registrar BI PUBLISHER reports which do NOT use bursting
jsr_xml09: VA veteran benefit data. Uses a connected query by the same name which is built over the two queries jsr_xml09a and jsr_xml09. Includes data about veterans based upon the student groups provided Pete DeSmit. Report can be run to either PDF or Excel. Report was designed for Trudy Ham.
jsr_xml10: VA benefit data changes only. Exact same LOOKING report as jsr_xml09 with the exception of the footer which includes the word “** CHANGES **” To be run by Judy Ham as she desires based upon a term and a date range of when changes may have taken place to a VA students records. In this case, there are two possible reasons that a student will appear on the report, and the report CANNOT indicate which reason. First, if the student changed their primary major during the date range. Second, if the student enrolled and/or dropped courses during the date range which MAY have caused one of the 10 reported columns in the report to change. The report CANNOT indicate which of the 10 columns may have changed. Trudy knows this and understands she must take the results of jsr_xml10 and manually compare them to that has been keyed into the VA system to see what has changed and if the student is still VA eligible.
Running BI PUBLISHER reports. Navigate to the BI publisher Query Report Scheduler:
Like any other object which runs thru the process scheduler, BI PUBLISHER reports require a run control. If you have already created a run control for a specific report, enter the name and press search. If this is the first time you’ve run the report, press the Add a New Value hyperlink. Run control value names can be anything that makes sense to YOU. As long as you can remember the names, use whatever naming convention you desire. In the following screen print, a run control named grade_audit was selected. Since all BI PUBLISHER reports use a PS query (or connected query) as the data source, and all of these queries contain prompts (aka parameters), you may need to update the values used to run the report by pressing the Update Parameters hyperlink and entering new values, then saving the new values. Failure to update the parameters will cause the report to use OLD data and may not create the desired results. Pleae double check the parameters being used before you continue.
Pressing Run will advance you to the process scheduler request screen. IF this is the first time you’ve run the report, you’ll need to make a few changes before the report can be run.
When you are done updating the distribution (make sure to check the email subject line and email body text as well) click OK and OK. Once it is completed, you will receive a copy of the email that was sent to each instructor to ringwama@jmu.edu which you can then save in a mail folder for future reference.
Other notes about the jsr BI PUBLISHER reports
Two of the reports (jsr_xml01 and jsr_xml01a) use the same query and the same data source.
Prior to running jsr_xml04 each Spring, which uses bursting to send a spreadsheet to the 6 main contacts at the different colleges, make sure to edit the query named jsr_xml04 and EDIT the expression used to resolve the eids. You need to make sure that the main contact for each of the colleges has not changed. If it has, all you need do is make a change to the expression or contact Pete DeSmit with the information and he will change the query prior to it being run.
List of where transfer credits are stored in SA system. Notes validated by the transfer credit team in registrars office, originally Liz Hash
Dual Enrollment credit. There will be a row for each course in trns_crse_dtl. To determine if it is a dual enrollment credit, join to trns_crse_sch and add the following three pieces of logic all OR’d together on the criteria tab:
instr(A.SRC_ORG_NAME,'(DE)') > 0
instr(A.SRC_ORG_NAME,'( DE )') > 0
EXT_ORG_ID is one of the following: ('111969229','111969238','111969247','111969265','111969283','111969308','111969317','111969335','111969353','111969362','111969405','111969423','111969432','111969478','111969487','111969502','111969511','111969520','111969548','111969557','111969566','111969575','111969584', 000003594,000003663, 000003721, 000003757, 000003807, 000003836, 000003919, 000003923, 000003924, 000003959, 000003984, 000004045, 000004050, 000004061, 000004089, 000004090, 000004093, 000004097, 000004127, 000004147, 000006723, 102405419)
Another expression I began to use after manually entering multiple criteria follows. You can then use this single character expression and set it to Y or N depending upon if you want dual enrollment credit returned by the query. case when instr(B.SRC_ORG_NAME,'(DE)',1) > 0 then 'Y' when instr(B.SRC_ORG_NAME,'( DE )',1) > 0 then 'Y' when B.EXT_ORG_ID IN ('111969229','111969238','111969247','111969265','111969283','111969308','111969317','111969335','111969353','111969362','111969405','111969423','111969432','111969478','111969487','111969502','111969511','111969520','111969548','111969557','111969566','111969575','111969584') then 'Y' else 'N' end
If you do not care about the courses they were provided credit FOR, use TRNS_CRSE_SCH where UNT_TRNSFR >0 which is the total number of credits transferred from that school. Unfortunately, trns_crse_sch does not contain data IF the student received their transfer credits from either IB or AP test scores. See also point #5 below regarding the grade.
IB credit is on TRNS_TEST_DTL where testid=INTBA OR tst_eqvlncy=INTBA and UNT_TRNSFR >0 and EARN_CREDIT=Y
AP credit is on TRNS_TEST_DTL where testid=AP OR tst_eqvlncy=AP and UNT_TRNSFR >0 and EARN_CREDIT=Y
CIE credit is on TRNS_TEST_DTL where testid=CIE and UNT_TRNSFR >0 and EARN_CREDIT=Y (as of 5/15/2013, only 9 rows of this data)
Checked all of the JIR records; they do NOT consistently agree with the raw data in the above records regarding transfer credits for a student. I looked at all fields in the JIR series whose field names included the word transfer (on campus, off campus, total, taken for progress, etc.) and they did not match the transfer credits in the trns_test_dtl and trns_crse_sch records.
When transfer course data comes from a VCCS, registrar does NOT key the course data in trns_crse_dtl, they key the grade into ext_course and use one of the following org_id values: 000003594,000003663, 000003721, 000003757, 000003807, 000003836, 000003919, 000003923, 000003924, 000003959, 000003984, 000004045, 000004050, 000004061, 000004089, 000004090, 000004093, 000004097, 000004127, 000004147, 000006723, 102405419
Might also be able to use the record r_description such as how it was used in the query jusds039. In that query, data from DP is used, and an expression is created to determine HOW the student received credit (transfer, test, JMU taken). This option might work instead of three separate queries for this data or a triple union query. This logic requires that the DP have been run for the student population which may or may not be true. Also, if you need to know where (the name of the organization) where the transfer credit came from, that info Is not available via the DP method.
See BI publisher report named jsr008 in sprd for example of a connected query and report which will include 4 types of course data. The baseline or parent query named jsr008 can be cloned, or ne created to return a baseline population. Then use a connected query to add your four children via jsr008a-d as appropriate. This method will require use of a BIP report, and note that IF the child query returns no data for the given baseline population, you will not have an opportunity to insert that type of transfer credit into the RTF document, since the XML data simply will not exist in the XML file.
10/2/2015: discovered possible replacement for needing to check how a course was credited without having to look at stdnt_enrl and trns_test_dtl and trns_crse_dtl. Use a record named SAA_ADB_COURSES AFTER you know the registrar has refreshed those series of records using the trunc feature first (currently performed by Vanessa Breeden). The field which indicates how they received credit is CLASS_ENRL_TYPE with these values: S = test credit, O = other transfer credit, I = internal transfer, T = course transfer credit, E = JMU enrollment. One reason to NOT use this record is IF you HAVE to know about dual enrollment credit, then use previous logic above. Also, do not use this record for anything other than active students. See jbio015 for example of this record. Remember to hard code the field named SAA_CRSE_TYPE=N else you will get courses which the student has added to their planner and to their what-if analysis.
Data anomalies by record name. This section intended for users who create data analysis objects.
stdnt_enrl and class_tbl.
When looking for “lab” related courses, most departments key the 4th character with an uppercase “L” to designate a course is a lab. Some departments (such as BIO courses) do not follow this standard. Instead of using the 4th character of the course number to determine if a course is a lab, use the class_tbl.instruction_mode field=LB.
Class_tbl.enrl_cap field is left as zero for approximately 25% of courses. Departments who key course data leave the value set to zero to force all students who wish to enroll to receive an override. This can cause an issue IF attempting to perform calculations on course data; specifically enrollment capacity planning. When the value is zero, you cannot accurately determine what the remaining capacity of a given course COULD have been which is defined as the enrollment capacity minus the enrollment total. One must therefore exclude such courses from analysis.
trns_crse_dtl.
Oftentimes, when users ask for transfer credit, they desire the data be broken out by those courses which transferred in as dual enrollment vs. those which were not dual enrollment. The data has been keyed by the transfer team in five ways. To determine if it is dual enrollment credit, join to trns_crse_sch and add numeric expressions: instr(A.SRC_ORG_NAME,'(DE)') and instr(A.SRC_ORG_NAME,'( DE )') and select the record if either of these >0 which is the indication it is a dual enrollment credit course. These are the two most prevalent ways the data is keyed, all five values: (DE), ( DE), ( DE ), (DE ) and (DUAL).
IF you need the grade for a transfer course, the query must be built as a union like jusds040a. This because when transfer course data comes from a VCCS, the registrar transfer team does NOT key the course grade in the field trns_crse_dtl.crse_grade_input. They key the grade into ext_course.crse_grade_input but only if the course is from a VACCS.
ressing Run will advance you to the process scheduler request screen. IF this is the first time you’ve run the report, you’ll need to make a few changes before the report can be run. 