Queries whose name begins with jc1-5 indicating they belong to the respective cluster areas. Primary contact for cluster one is Gretchen Hazard hazardga@jmu.edu . Primary contact for cluster one is Scott Paulson paulsosa@jmu.edu primary contact for cluster 2 is Dennis Beck (beckdc@jmu.edu). See also the jgenedxxx series of queries which, although originally written for Gretchen Hazard, were designed so they may return data for all cluster areas. Section last updated 5/9/2016.
JC1001 – List of Cluster One courses by term, listing the number of students enrolled, and the seat cap per session. Results include: Term, Class#, Subject, Catalog#, Section#, Total Enrollment, and Cap Enrollment. Prompts include the term and a subject/catalog#. If the second prompt is left blank, all cluster one course data is returned.
JC1002 – List of Cluster 1 courses by Time of Day, Instructor, Building, Room, # Enrolled, and Seats Available. Results include: Term, Subject, Catalog#, Section#, Instructor, Location, Days, Start Time, End Time, Total Enrollment, Cap Enrollment. There are two prompts: Term Subject and Catalog# (i.e. BUS160, PHIL120, etc.) Note that if you only specify the term, all cluster one courses will be listed. Note that in some cases, a course is listed twice. For example, all 5 sections of MAD150, in term 1078, meet in two different locations. Because of this, they are each listed twice. The query returns data even if there is no instructor assigned and/or room assigned.
JC1003 series serves as a list of admitted students who are coming to JMU and have not met the cluster one requirements in each of three areas (hence the reason for the A, B and C queries). Of special note is the fact we can NOT use the degree progress records for this series due to the fact these students are brand new to JMU (having just matriculated for the given term) and hence will not have had their degree progress report run for them. 3/30/2009 note added: It should be noted that these three queries will return LOW numbers (relative to the incoming Freshmen class) if run a long time (relative term) before the beginning of a Fall term. They will return the most accurate information if run AFTER Summer orientation has completed.
JC1003A .students who have not met the cluster one requirement for writing. Query includes all of the “normal” exclusions for BIS, 2nd degree seeking etc. Also includes exclusions for having transferred in a course which meets the criteria. 9/21/2009: Gretchen needed to run the queries, and said that the registrar’s office changed the course for WRIT103 to WRTC103. I added this to the 5th and 6th does not exist sub query so it checks for both courses.
JC1003B . Students who have not met the cluster one requirement for critical thinking. Query includes all of the “normal” exclusions for BIS, 2nd degree seeking etc. Also includes exclusions for having transferred in a course which meets the criteria. Does NOT look at test results.
JC1003C . Students who have not met the cluster one requirement for human communications. Query includes all of the “normal” exclusions for BIS, 2nd degree seeking etc. Also includes exclusions for having transferred in a course which meets the criteria.
JC1004 – Summary count of students enrolled in each of the nine cluster one courses per term and count of sections offered per cluster one course. Query has two prompts for a term range.
JC1005: Grade analysis by type for C1. Includes hard coded references to all cluster 1 courses, and returns aggregate data based upon the student’s admit type.
JC1005A: Grade analysis by major for C1. Includes hard coded references to all cluster 1 courses, and returns aggregate data based upon the student’s primary major.
JC1006 List of freshman who have not taken the ISST or TECH1 test. Assumes all of the standard exclusions. Note that JC1007 was deleted/consolidated with JC1006 which now includes a prompt for the testid.
JC1007: WRTC103 analysis. 11/11/2011: cloned from jusds039 for Gretchen Hazard. Second request is a little more complicated: WRTC would like some research done to evaluate whether or not placement in WRTC 103 is appropriate or needs to be changed. What they would like to know is this the intersections of the following information: identify all students (from the last 5 years) who scored 680 or above on the SAT VERBAL portion. The score they made on the SAT WRITING portion An indication if they earned AP/IB/dual enrollment (transfer) CREDIT or exemption for WRTC 103 If they took WRTC and the grade earned Their overall GPA (as an indication of their success in downstream coursework) with the number of credit hours earned (I think) They do not need any identifying information for individual students there will be no need to contact the students. 1/26/2012: removed dependence on hard coded rpt_date since the degree progress feature run by the registrar’s office is the only feature which inserts data in the "R" tables.
JC1008. MANDU test data results. List of freshman who are eligible to take the MANDU test, whether they have, and their score. Assumes all of the standard exclusions. Note, several queries were combined into one, so the old suffixed A, B and C versions no longer exist. ALL returned rows are eligible for the MANDU test based upon their SAT1/VERB score being > 679. If the score is blank; the student has never taken the test; otherwise their score is returned. JC1008A does NOT take into account if the student has an associate’s degree or is seeking a 2nd degree. JC1008_revised is owned by Catherine Crummett.
JC1008A: MANDU test results by term. Needed mandu test results by term regardless of other details.
JC1009 Cluster 1 data. Identify which Cluster One classes transfer students (no BIS, AA or second degree students) typically take in their first semester at JMU. All TRN since 2005 Cluster One classes taken at JMU: WRIT103, BUS160, HIST150, ISAT160, SMAD150, PHIL120, COM121, COM122, COM123.
JC1010 Cluster 1 failure series. This series has been rewritten based upon new specs; original series was named jc1005 which no longer exist because they are no longer valid. Results returned by these queries are dependent upon the Office of the Registrar running the degree progress report for all Undergraduates active in their program. Something they do twice a year. Please contact Pete DeSmit approximately 1 week prior to running this query so he can ensure none of the specs have changed. On/about 3/30/2009, these queries were run and returned inaccurate results. Inaccurate because the Office of the Registrar had changed the actual requirement line numbers used for the cluster one requirements. 9/21/2009, Gretchen emailed asking if the two series (jc1003 and jc1010) were ready to run. Had to tweak jc1003a (see below) and thought the jc1010 series was WRONG because it was returning true freshmen and transfers for the current term. Gretchen says she knew this, and simply deletes them out of her results, looking only at upper classmen (aka continuing students). Pete was going to change the jc0101 series of queries to eliminate the rows whose admit term=current term, but Gretchen says she was fine deleting them herself, and preferred that method.
ALL four jc1010 queries share the following logic for which students are NOT included even though their degree progress report may indicate they have not met one/more of the cluster one requirements:
1st exists makes sure they are either a TRN/FYR student. To return only TRN students, enter TRN in the prompt. To return only FYR students, enter FYR in the prompt. To return all students regardless of admit type, leave the prompt blank.
1st does not exist eliminates them if they are a BIS (formerly known as BGS but uses the same plan number 000005950) major. Also eliminates them if they are a transfer student seeking a BSN degree (plan # 6400). Also eliminates them if they are part of the study group folks (plan # 0009999999)
2nd does not exist eliminates them if they ever received a degree from JMU.
3rd does not exists eliminates them if they ever received a degree (UG or AA) from an institution other than JMU.
4th does not exist eliminates them if a transcript note has been added stated they have begun a second degree. May appear like a strange way to check on this logic, but office of the Registrar validates that they indeed key data in the TSCRPT_TEXT record for students who may not have data in the acad_degree or ext_degree records. ** 3/30/2009, according to the office of the registrar, they no longer key such data onto a student’s transcript, so this does not exist portion of the query was deleted. Instead, Kelly Downey says they enter the actual degree into the external degree table in PS, and this series of queries is already checking for that data.
5th exists ensures they have failed one/more cluster one requirements as of the last time the degree progress report was run by the Office of the Registrar.
6th does not exist eliminates them if the student and their career was ever in the study group via data in the STDNT_ATTR_DTL record. May seem redundant to the previous logic based upon the plan number, but leave this logic.
Pete verified (and tested) with Kelly Downey in the office of the registrar that logic exists in the degree progress report to allow a requirement to be passed IF the student is registered for a course which satisfies that requirement in a future term. Pete tested registering a student for one and two terms in the future, and verified the requirement was set to COMP when a degree progress report was run. This means that none of the queries need to look at specific stdnt_enrl data, ever!
This series of queries can be run for transfer only students (admit type=TRN), true freshmen (admit type=FYR) or both (leave admit type blank).
Update on 4/2/2012: the base query jc1010 has been rewritten as a BI publisher report which uses a connected query named jc1010. The connected query uses a parent query named jc1010 and a child named jc1010_missingc1data which returns the rows of which cluster one requirements are missing. To run this report in test mode, enter your userid in the last parameter. To run the report for real, leave the last parameter blank. Doing so will cause one email to be sent to each student who is missing one/more of the cluster one requirements. To view which students will receive the emails, run the query named jc1010 by itself to view the results.
jc1010 returns students who have not completed their cluster one requirements, regardless of area. Cluster one=requirement 000001012.
jc1010A returns students who have not completed their cluster one writing area (requirement 1012, requirement line 0030). Has special logic per Gretchen to eliminate the few (there were 16 as of 4/3/2008) students who should be eliminated based upon the following test scores even if their degree progress report states they have failed the writing area:
Testid=AP, with a test component of ENGLG or ENGLI and a test score >3
Testid=INTBA, with a component of ENGAH and a test score >4
Original jc1010 query returned data for all three areas where any of the three areas were failed. Had to abandon that query due to the test score logic stated above. Ended up breaking the cluster one failure queries into four separate queries in the JC1010 series.
jc1010B returns students who have not completed their cluster one Critical Thinking area (requirement line=0040) which includes these classes: BUS160, HIST150, ISAT160, MAD150, PHIL120, PHIL150.
jc1010C returns students who have not completed their cluster one Human Communications area (requirement line=0050) which includes these classes: COM121, COM122, COM123.
jc1010d C1 fail by area summary. Returns aggregate/summary data of those students who have not completed (aka failed) any of the specific cluster one requirement areas with the stated above populations removed.
jc1010e. C1 fail information literacy. Returns student identifiable data for those who have not met the cluster 1 literacy requirement.
jc1010f Information literacy aggregate. Same as jc1010e, but returns aggregate data vs. student identifiable data.
jc1010g. Need any gened by cluster. Prompts for an optional admit type (FYR/TRN) and the current academic term. Has the 5 requirement numbers for the 5 clusters hard coded. C1: 000001012, C2: 000001013, C3: 000002489, C4: 000001025 and C5: 000001026. Returns aggregate data of all active students who are missing one/more of all general education cluster requirements. Includes the list of standard exclusions as defined by Gretchen Hazard.
jc1011: Come to JMU with WRIT103 cred. Cloned from jus0010, then tweaked to meet Gretchen Hazard's needs. Wants a count of students (by admit type) who come to JMU with credit for WRIT103, in three categories. 1) test scores, 2) transferred the course in, 3) previously taken the course @ JMU prior to their admit term
jc1012: Crit thinking invite. cloned from jc1004 for Gretchen Hazard. I have an experimental class, UNST 390, that will be running in Spring 2016. We are trying to increase enrollment. I would LOVE to be able to reach out to students who are at the sophomore and above level already registered for a critical thinking class for Spring 2016 to ask if they might be willing to move to this new version of the course. Would it be possible to do a query that could accomplish that?Desired information : admit term, email, name, level, hours enrolled this semester admit type, major and REGISTERED IN A CRITICAL THINKING class for SPRING 2016. Separated by course?
jc302 (TS): Cluster 3 fail by area. Prompts for a term. Returns aggregate data of all active students who have not completed a cluster three requirement by each of the respective areas.
jc303 (TS): Fail C3: mass email. Prompts for a term and e-ID value. This query is used with a BI publisher report by the same name. This version is run each term to send students who have not completed specific cluster three requirements, and who have courses which meet the stated requirements which will fit within their current schedules. The BI PUBLISHER report uses bursting to send each student an attached PDF file which contains a list of courses by cluster three area which fit within their current schedule. This report is built over degree progress data. Prior to running the report, check with the office of the registrar to make sure the “R” tables have recently been refreshed.
jc303A (TS): The only difference from jc303 is this report includes prompts for 1-20 SA emplids. It is meant to be run as part of a business process by the cluster three coordinator.
jc304 (TS): Cluster 3: not enough credits. This query is used with a BI publisher report by the same name. This version is run each term to send students who have requested an override via a qualtrics survey, but who do NOT have enough credits to qualify per the business process of the cluster three coordinator. This version is very different than jc303 and jc303a and jc305 in that it is NOT based upon looking at failed requirement data.
jc305 (TS): Fail C3: no courses available. The first select returns a row of data if there are courses that fit in the students schedule for a given failed requirement. I label this as status "Good". The union selects everyone who lacks a C3 requirement, I label those rows as "Fail". Obviously, by how the union is written, everyone will have a fail row returned. Because I sort by the status, the fail row appears first. So a FAIL row NOT followed by a GOOD row means that student, for that requirement/line has NO courses that meet their schedule.
jc5001: Was cloned on 10/19/2009 from jc1001a at the request of Jeanne McAllister who sent Pete the following email:
Hello Pete, I am Cluster Five Coordinator for GenEd. Catherine Crummet referred me to you for a query on students who have not yet taken their Cluster Five Wellness requirement. My goal is to contact these students with some course information. I am not well-versed in this, so let me know if I'm using the right language. She indicated that you should have all the basic information from queries written for Cluster 1, such as eliminating the second degree students, students with associate degrees, etc. The course IDs for the C5 Wellness courses are: Geic101 – 007925, Ghth100 – 003133, Gkin100 – 008686 Please let me know if I have submitted this request properly and if anything else is necessary.
The only change made to this query from jc1010a was to change the hard coded values for the requirement number (cluster 5=000001026) and requirement line number (0010=wellness). Also suggested she look at jusds0039 which returns data from the degree progress report on which courses were used to complete specific degree requirements. Requirement group=001001
Jeanne did not have access to run queries in SA, so sent the following email to her department secretary, Amanda Tusing:
Amanda, The data Jeanne describes below can be found in a query in the srpt instance named jc5001. She does not have access to the query tool, and needs for you to run the query and provide her the results. The only prompt is the current term, which=1098. Also, I've attached results from a query named jusds039. It requires three prompts, as follows: Requirement group=001001 Requirement #=000001026 (this is the requirement # for cluster 5) Requirement line#=0010 (this is the requirement line # for wellness) Jusds039 returns aggregate data of the courses used to complete a specific degree requirement; in this case the cluster 5 wellness requirement. It returns data for all students active in their respective career (Undergraduate in this case). If you ever have a need to run it in the future, you'll need to weed out/ignore/delete some extraneous data returned by the query. In short, the degree progress process is extremely complex and this query "does the best it can" to return aggregate course data. If/when you run the query, you'll see what I mean. Jeanne, the reason I can send this data to you w/o you having access is because it is aggregate data and contains no student identifiable field. If you ever desire access to the query tool in the SA system, feel free to contact me; the training takes less than an hour. If you're ever having trouble sleeping, you can read all about the "department queries" in the following document I maintain: http://isapps.jmu.edu/prod/sa8queryfaq.doc Let me know if there are any questions, and thanks for the opportunity, Pete DeSmit, Information Systems
