Reporting objects whose name begins with jcap and jcorg. Primary contact for the jcap series is Chris Campbell campbecm@jmu.edu Primary contact for the jcorg series is Nina Stensby-Hurst stensbne@jmu.edu. Section last updated 6/13/2018.
Several of the reporting objects in this section include logic with hard coded references to specific first year (aka Freshman) advisors. The list of these advisors is provided to Pete DeSmit each Summer (early June) by the career and academic planning staff. The hard coded list is added as a student group named FYAD and referenced by the reporting objects. Reporting objects with this logic present are indicated with (FYAD) after the reporting object name below. These reporting objects will only return data for the listed advisors. To view a list of the current hard coded advisors, run a query named jcap09c.
jcap01 (FYAD): Green sheet 1787. Description assigned by the CAP staff as the destination of this BIP report was once upon a time physically printed on green paper for the 1787 program used during the freshman orientation process. This is meant to be run as a BIP report with bursting with the results emailed to the assigned advisor as an attached PDF file with one page per advisee. IF you desire to see the raw data which will be used by the BIP report, you can run the underlying query by the same name using appropriate values for the prompts. To run the actual BIP report, navigate: Reporting Tools, BI Publisher, Query Report Scheduler. If this is the first time the user has run this BIP report, a new run control will need to be added. If the report had been run before by this user, you may search for an existing run control. Run controls can be named anything as assigned by the user at creation time. My suggestion is to name them the exact same thing as the underlying query/report. If the user is unfamiliar with running a BIP report with bursting, you may want to refer to the documentation labeled Running an BI PUBLISHER report with bursting the first time in this document. Most of the jcap BIP reports which use bursting have been designed to be run in what is known as a test mode. To run in a test mode, you must enter YOUR EID in the second prompt, else leave that prompt blank to run normally. The report is hard coded to never return transfer students and requires the student be assigned an orientation session. Should NOT be scheduled to recur.
jcap02 (FYAD): Green sheet session changes. Similar to jcap01 in that this is meant to be run as a BIP report with results sent as a PDF attachment to the advisors. It includes the ability to be run for a specific orientation session number, or left blank to be run for all sessions. Jcap01 is normally run prior to orientation and includes one PAGE per advisee. Jcap02 is normally run as part of the orientation advising session and contains one ROW of data per advisee. Should NOT be scheduled to recur.
jcap03 (FYAD): Yellow sheet early test scores. Similar to jcap01 and jcap02 in that it is designed to be run as a BIP report with bursting with data being emailed to the advisors. The “early” refers to the student test score data. The underlying BIP report uses a connected query as it’s data source due to the complex nature of the report which needed parent and several child data present. When run, it will prompt for a term range, an orientation session and a test date range. An “appropriate” date range should be entered to ensure test scores for the students are returned in the report. The results are one page per student for the given advisor. Can be run for a single advisor, or left blank to run for all advisors. The child queries named jcap03a,b,c,d should NOT be run by themselves; they were designed specifically to be part of the connected query named jcap03. Should NOT be scheduled to recur.
jcap03e (FYAD): Yellow sheet: ADDS only. Similar in all ways to jcap03 in terms of how it should be run/scheduled as a BIP report. Returns only those advisees who were added as of the yesterdays system date. Is meant to be scheduled to recur daily at 4 AM via a recurrence definition named JCAP4AM. SHOULD be scheduled to recur with an appropriate date range.
jcap04 (TS): Yellow sheet test scores complete. Similar to other jcap reports in that this is also meant to be run as a BIP report with the bursted data emailed as an attachment to the advisor. This version was meant to be run to only include those students who have test scores received LATE (defined by the user who runs the report). If the student has no test scores in the given date range, they will NOT be returned in this report.
jcap05 (TS): Deferral and readmits. Designed to be run as a query. Prompts for a single admit term. Returns just those students who were previously deferred and have now been re-admitted.
jcap06 (TS): For use with VZ matching. Designed to be run as a simple query to allow matching of data to the Visual Zen orientation system. Prompts for just a term range.
jcap07 (FYAD): Advisor roster for BIP burst. Prompts for an academic term which is used to retrieve the term and total units earned fields present in the report. This is meant to be run as a BIP report whose ultimate destination are bursted emails sent to the Freshman advisors.
jcap08c (FYAD): Mid-term grades for advisors. Designed as a BIP report with bursting which means, when run with appropriate configured values, the end result will be emails sent to each Freshman advisor who has entered mid-term grades for their advisees. IF you desire to see the data which the report will use when sending these emails, run a query by the same name.
jcap08d (FYAD): Final grades for advisors. Designed as a BIP report with bursting which means, when run with appropriate configured values, the end result will be emails sent to each Freshman advisor who has entered final grades for their advisees. IF you desire to see the data which the report will use when sending these emails, run a query by the same name.
jcap09 (FYAD): Mass change of advisor data. There is both a query and a BIP report by this name. As is the case with all BIP reports, the data sent BY the BIP report via bursting is provided by the underlying query with this name. IF you desire to see the raw data which will be consumed and emailed by the BIP report when run, it is highly recommended that you run the query first, validate the data is appropriate, THEN run the BIP report using the bursting feature. The query includes three admit term prompts; depending upon the time of year run, you may need to provide all three for the Summer, Fall and Spring admit terms. Embedded within the query are hard coded values which cross references the 10 digit academic plan numbers (aka majors) to a single EID of the person responsible in a department to reassign the advisor ID#’s from the freshman advisor to the major advisor. The overall purpose of this BIP report is to get the raw data into the hands of the proper person in the department to reassign the advisor. Once the data in the query has been validated, schedule the BIP report using the following steps.
These steps ASSUMES the user running the report has already added an initial run control named jcap09. Navigate: Reporting Tools, BI Publisher, Query Report Scheduler. In the “Run Control ID begins with” field enter the value=jcap and press enter or search. A run control ID named jcap09 should be returned, select it. IF the values in the three prompts is correct, press OK to advance, else press the “Update Parameters” hyperlink, enter the proper admit terms and select OK, and Save. Press the “Run” button in the upper right portion on the Query Report Scheduler page.
On the Process Scheduler Request page.
Set the Server name to PSUNX
IF you want the underlying data EMAILED to the actual department users, select “Email” in the type drown box. Selecting anything other than Email in the Type field will NOT email the results. IF you want to test the report results, select a Type=Web and a format=XLS.
Enter appropriate verbiage in both the email subject and message text fields. This is the actual email subject and text the departments will receive when their data is emailed to them as an attached spreadsheet. It is suggested at a minimum that the email text field contain verbiage which refers to the attached spreadsheet which the users will see as being named “BIP Attachment.xlsx”
Press OK, and OK, the report will be queued to run.
Steps to follow when a spreadsheet is sent back from a department.
Open the spreadsheet, make sure the department contact did not modify the column headings, they should read: Current Freshman advisor, Student, EMPLID, Current Advisor ID#, NEW Advisor ID#1st Major Plan #, 1st major description, Career, Prog.
Verify the New Advisor ID# column has been entered for all rows.
Save the spreadsheet to your network drive overwriting the existing jcap09.xlsx file.
Open the MS Access database, run a query named “Validate new emplids”. The query should return NO data. IF any data is returned by the query, cut/paste those rows into an email, return it to the department contact and request they enter valid data.
Once the MS Access database query returns no rows, open the jcap09.xlsx file on the network drive, and save it as a CSV file format. Use that file as input for the mass advisor change process within the SA system. Use whatever naming and business process convention works for you to NOT lose track of which departments have been processed.
jcap09a (NTS): List of all advisor EMPLIDS. This query contains no prompts. It should be run once, just prior to you processing the results sent back to you from the various departments. It contains the SA EMPLIDS for all instructors/advisors. The results are used for a validation process query within the MS Access database on your shared network drive. Save the results of the query to the file named jcap09a.xlsx and overwrite the existing file. Make sure the file has been edited to remove any header rows which should leave a single field present named JCAP09A.
jcap09b (TS): Advisor translation data. Contains no prompts. Only purpose of the query is to render the list of translated contact information to enable you to see which users will receive email when the jcap09 BIP report is run.
jcap09c (FYAD): FYR advisors in student group. Contains no prompts. Only purpose is to return the hard coded list of advisors who were provided to Pete DeSmit. The returned list of advisors are the only ones which will be included for all reporting objects with (FYAD) listed after the reporting object name.
jcap09d (FYAD): Emplids to remove. Returns the SA emplids of those FYR students for the given admit terms who have had their FYR advisor replaced with the major advisor. For a few weeks each year, FYR's are allowed to change their major, and the automatic process has built in logic which replaces their FYR advisor with a MAJOR advisor BUT, CAP has a business process whereby they send spreadsheets to all of the departments to have them manually adjust a spreasheet to change the FYR advisor to a major advisor. CAP sends a single file the registrars office who then changes ALL of the advisors in mass. The emplids returned by this query represent those students who will need to be manually removed from this list before the mass reassignment is run, else the major advisor will be improperly overwritten.
jcap10 (TS): Students by admit term. Prompts for an admit term range and 1-4 academic plan numbers (aka majors). Returns a list of currently active undergraduate students with all assigned advisors and their primary major. Eliminates the student if they are an active study group student.
jcap11 (TS): Freshmen Undeclared. Prompts for an admit term. Returns a list of students whose primary major is undeclared.
jcap12 (NTS): FYR incoming transfer hours. Prompts for a range of admit terms. Returns true freshman (admit type=FYR) and aggregate counts of those whose transfer credit totals classify the students as first year (1-27 transfer credit), Sophomores (28-59 transfer credits), Juniors (60-89 transfer credits) or Seniors (90+ transfer credits). Note that transfer credit hours are entered by the transfer team within the office of the registrar. Contact this office to determine if they have finished keying transfer credits for the given admit term.
jcap13 (TS): COB majors w/o COB advisor. Prompts for two admit terms and an academic term (for enrollment information). Includes hard coded references provided by Molly Brown to return COB majors who have not been assigned a COB advisor. BOTH the list of plan numbers and advisor emplids are hard coded and were provided to Pete DeSmit by Molly Brown.
jcap14 (TS): Advisor self serve of jcap03. Can only be run by an advisor who has been granted access to run same. Is documented elsewhere. The BI Publisher report by the same name also uses the jcap14a query and a connected query named jcap14.
jcap15 (FYAD): BIP sending drops to advisors. Intended to be scheduled to recur daily. Will send email to FYR advisors if any of their students dropped a course.
jcap16 (TS): Handshake interface data. Prompts for a single academic term. Returns all active students whose career <> CE. Originally designed for Brady Schwendeman. Is a scheduled/recurring run of a query named jcap16 which is scheduled to run and recur daily by desmitpa and emails the results in a CSV file format to schwenwb@jmu.edu. As of 6/8/2018 desmitpa has no idea if the results of this scheduled query are being used.
jcap17a (FYAD). Advisee changes, drops. Prompts for an academic term. Returns those advisees who have been dropped as of yesterdays system date for all advisors included in the FYAD student group. Designed to be scheduled to recur daily as a BI Publisher report using a recurrence definition named JCAP4AM. As of 5/12/2016 is being scheduled by Pete DeSmit. See also Scheduled recurring objects section within this document.
jcap17b (FYAD). Advisee changes, adds. Prompts for an academic term. Returns those advisees who have been added as of yesterdays system date for all advisors included in the FYAD student group. Designed to be scheduled to recur daily as a BI Publisher report using a recurrence definition named JCAP4AM. As of 5/12/2016 is being scheduled by Pete DeSmit. See also Scheduled recurring objects section within this document.
jcorg01 (NTS). Degrees by date range. Must be run as a BI Publisher report to receive the desired format. Can also run the underlying query of which there are four named jcorg01,a,b,c. ONLY the query jcorg01 should be run as a query, and then only if it is desired to look at SOME of the raw data. To run the actual report, follow the steps below for either the first time or subsequent runs.
Running the report the first time. Navigate: Reporting Tools, BI Publisher, Query Report Scheduler. All scheduled BI Publisher reports require a run control. The first time you schedule the report, you will need to ADD a new run control. Press the hyperlink labeled “Add a New Value”. In the Run Control ID field, enter the value “JCORG01” and press the Add button. Use the drop down box to select Connected Query as the data source. In the report name field, enter the value “JCORG01” and press the TAB button. The three prompt fields for the report will appear. Enter appropriate values for the range of dates corresponding to the degree conferral date. Leave the career prompt blank unless you desire a single academic career; the values are CE, DR, GR and UG. Press OK. On the Query Report Scheduler page, press the Save button in the lower left corner, then press the Run button in the upper right. The server name should read PSUNX. Select Email in the type drop down box, leave the default format of XLS. Press the Distribution hyperlink. Enter “something appropriate” in the email subject field. This is the text which will appear in the subject of the email when the report results are emailed to you. Press OK, then OK again. The report will run and the results will arrive via email.
Running the report subsequent times. Navigate: Reporting Tools, BI Publisher, Query Report Scheduler. Press the Search button to return all reports which you had previously scheduled to run. Select the run control created in 17a above, it should be named jJCORG01. IF you desire to change the prompt values which are displayed, press the Update parameters hyperlink, else press the Run button, then OK. The report will run and the results will arrive via email.
jcorg02 (NTS) Graduates; further programs. This query was cloned from jusds007r1 for the sole purpose of having it available in the jcorg series of queries. It contains prompts for a date range of when an undergraduate received a degree. It is designed to return data only for the student population selected who now have an active GR/DR career.
