If new access (to one/more records) is required of a user who can already create and/or run queries/reports
Anytime a reporting object includes one/more highly confidential (defined in data stewardship policy) fields
A new query is created, and not cloned as defined below
Data analysis results are to be sent to/saved for a user that does not have access to obtain the results themselves. Note, there are a few of these scenarios already in place which were previously authorized. They include:
employee appreciation day data is provided to IS web (John Sewall now) once a year
credit hour and graduation expectations are provided to Debby Boyle twice a year for emplids she provides to determine if her employees are graduating
An BI PUBLISHER report includes bursting
If a query is being modified for a purpose other than was originally intended. Examples:
The series of queries built to feed the TK20 system are modified in a manner to allow the query results to feed a system other than TK20
ANY query is modified in ANY manner whose data is to be used outside of JMU where before it had not been. In other words, take a regular old day to day query used for internal use only, and modify it to feed data to an agency outside of JMU.
A issue and op is required for anything not covered in the PIQ section above. This includes, but is not limited to the following scenarios
A data analysis object is cloned. Cloning is defined as taking an existing object (query, crystal, BI publisher report, connected query, etc.) saving it as a different name, then altering the new object in any manner at all. Cloning cannot be used if the intent and/or purpose of the data analysis object is not the same as the original object.
Adding/changing/deleting any of the following query attributes
Title, name, long description, folder, owner, distinct parameter
An issue and op is NOT required for the following scenarios
Research for a user which results in less than 15 minutes time on task
Assisting a user who can create their own data analysis objects which results in less than 15 minutes time on task
One time use objects with aggregate data (no student identifiable data) which results in less than 15 minutes time on task.
Special considerations for auditors (if it matters, state the level of auditor the practice applies to; JMU, state, etc.)
Troubleshooting queries
This section was added as a result of frequently asked questions. If you have any questions about these or any queries within your PeopleSoft SA system, feel free to contact Pete DeSmit (desmitpa@jmu.edu or 83605). You are also welcome to contact Pete if you have suggestions for new queries or proposed changes to an existing query.
It appears I do not have access to students who major in our five languages: French, German, Italian, Russian, and Spanish. Our major is listed as Modern Foreign Language with the five languages being concentrations. Can I use the query jusds002 to locate students in these respective concentrations? Answer: Run the email addresses by academic plan query using the following values (the two values for plan #1 and #2 were provided by the registrars office):term: 1081, academic institution: JMDSN, academic program: UGEN, plan #1: 0000008800 plan #2: 0000068900 leave plans 3-4 blank, academic level #1: 10, academic level #2: 20, academic level #3: 30, academic level #4: 40. With the results displayed, look at column I, if it contains the value "CON" it means the next two columns refer to the student's first concentration. Column K contains a short description of this concentration. I believe if you sort by column I, and then column K, you will have the data you desire; a list of foreign language majors, sorted by their primary sub-plan (concentration) which in this case is the language.
We have had several questions about second majors not being included in the queries you now have access to run. The reason for this is that second majors use a different plan number. There is also a different plan number for each different degree a student can select for each major. For example, a student majoring in economics can choose between a BS, BA or BBA degree. There is a different plan number for each degree: Plan Number. 0000001234 - Economics BS, 0000001235 - Economics BA, 0000001236 - Economics BBA... and yet another plan number if economics is the second, third, fourth... major (note that this one does not have a degree attached to it): 0000001237 - Economics (second major) When you run the query, in order to get a complete list of majors you must include all plan numbers (in the case of economics you would have to enter all four). I realize this is complicated, so if you have any questions, please feel free to call me at x86279. Michelle White, assistant registrar.
How can I obtain a list of students with a specific minor? The query labeled "Email by academic program" prompts for 1-4 academic plan numbers. There are 476 active plan numbers; a breakdown indicates that 302 of these plans are majors, 148 are minors, 15 are preparation plans and the rest belong to certificates and honors. The point; if you want to retrieve a list of students who have a specific minor, all you need do is find the plan number which indicates the minor and run the query using this value. Art, as a minor, belongs to plan # 0000100500. If you run the query using this plan number for term 1031, the 75 students who have an active art minor will be returned. Listed along with their minor data will be their major, along with other sub plan and concentration data. If you prompt in any of the plan number fields, the search dialog window contains a column labeled "Plan Type". This column of data will indicate whether the plan is a major, minor, etc.
Why do duplicate rows of data appear in the permissions by class query results. There are two reasons. First, because of the fact the results include the location of the class in question. When a class meets in more than one location, a seemingly duplicate row of data appears in the results. If you look closer at the results, what you will see is that the meeting days, time and/or location are actually different for the second, and subsequent rows of data.
How am I suppose to handle data present in the query results when the FERPA flag is set to “Y” for a student. According to the office of the Registrar, a value of “Y” indicates the student has requested that no data about them be shared with anyone OUTSIDE of the JMU community. This includes any possible acknowledgement that they are actually attending JMU to anyone not employed by JMU. It is suggested you email registrar@jmu.edu if you have specific FERPA questions.
Preventing “duplicate” rows of data in the addresses by class query results. Although the query tool rarely “makes a mistake”, the way in which data is entered has a direct affect on how data can be retrieved. For example; you may run the addresses by class query for a given single class number and notice that each student is listed in the results twice. One possible explanation is how the data for the course was entered (and yes, you have a choice during the setup of a course). Let’s use BIO 114 as an example as it has a lab component which is the cause of the “duplicate” rows of data being returned. When sections of BIO 114 were setup, the same course information was entered for the course (lecture) and the lab, essentially two rows of data for the course. When the database is asked to return a list of students in BIO 114, it responds that each student is in the course twice; once for the lecture, and once for the lab. If the fact that a student appears on a class list twice is causing a problem with your data analysis, feel free to contact a member of the registrars office and they can assist in showing you how to setup the course so it has a lecture and a lab component.
I am using the results of a query in a mail merge process, but the leading zeros for the zip code disappear, why? Pete spent time trying to get MS Excel to behave itself when working with zip codes with leading zeros. All attempts which should have worked, did not. A work around follows. Open the spreadsheet which contains the zip code field. Right click on the column and select format cells. On the number tab, select custom as the category. Below the “type:” field enter five zeros and press OK. Now save the spreadsheet using file-save-as and select the format named “text (tab delimited) (*.txt)” in the save as type prompt. Use this .txt file as your data source for the merge process. The leading zeros for the zip will appear.
If you've run a number of the department secretary queries, you may have found yourself in need of an Excel function to perform an un-duplicated count of students. The scenario would be something like this: You have been asked to analyze academic plan related data in your department to determine how many students were active in a specific major for one calendar year vs. another. There are several queries built over academic plan data, you pick the query named jusds002b and run the query for the three terms 1048, 1051 and 1055 which constitutes the 2004-2005 academic calendar. You paste all three result sets into one Excel spreadsheet. There are 287 rows of data, with row1 containing the heading information, and row 288 containing the last row of actual data. The formula below assumes that column a contains the emplid of which you desire an unduplicated count. Place your cursor in column a289 and enter the following formula: =SUM((A2:A288<>OFFSET(A2:A288,1,))+0) After typing the formula, press the following three keys at the same time: Shift, Ctrl and Enter. This set of key sequences is required because the formula makes use of a data structure known as an array. If entered correctly, cell a289 will contain an un-duplicated count of the emplids. In this case, unduplicated count means you will only count a student once regardless if he was associated with your major more than one term.
I try to send results to Excel from query viewer, and the results magically disappear, try following these steps to correct the issue. On Windows XP, if the setting for the file type=xls is changed for the new action, the field labeled “confirm open after download” is checked OFF, all works fine
On Windows 7, you cannot change the file type settings With IE open, navigate: Tools, Internet Options, Click the security tab. Click trusted sites, click customer level. Scroll down to the Downloads section. Make sure the radio buttons for Automatic Prompting for File Download AND File Download are set to Enabled.
PeopleSoft queries and the proxy error. First, why the error. In non-technical terms, the system serving interactive requests is too busy. The system which serves interactive users is optimized for what that system does most; serving up pages to SA core users. Second, possible solutions.
Once you receive a proxy error, you will have no choice but to close your web browser, launch a new session and log back into the SA system. You then have a choice of how to receive your query results. You COULD attempt to run the query interactively again. And yes, IF the system serving interactive users is still too busy, you will once again receive the same proxy error, and need to repeat the steps to close the browser session, re-launch, log back in, and repeat. OR you could …..
Schedule the query. It is a fact there are more resources for SA processes which are scheduled, including scheduled queries. It is also a fact that scheduling a query and obtaining the results takes more time navigating the panels. Scheduling queries is a much better use of system resources, and the servers which run the scheduled queries are optimized for such tasks. The first time you schedule a query, there are a few extra steps required, but subsequent runs are far fewer steps. Documentation related to scheduled queries can be found on page 81 of this very document.
