PS Query Quick Start Guide - SBCTC
DATA SERVICESctclink PeopleSoft QueryQuick Start GuideQuick Start Guide for PeopleSoft Query BasicsCreated – 6/2016Paula McDanielData Analytics Learning and Education InstructorWashington State Board of Community and Technical Colleges
PS QUERY BASICS QUICK START GUIDETable of ContentsIntroduction to PeopleSoft Query . 1What is a Relational Database? . 1PeopleSoft Pillars and Modules . 3PS Query Protocol. 4Query Development Life Cycle . 4Query Migration . 4PS Query Development Protocol . 6CtcLink PS Query Maintenance Protocol . 7PS Query Terms . 8Using PeopleSoft Query . 9Accessing PS Query . 9Using PeopleSoft Query Viewer . 10Searching Using Query Viewer . 10Searching Using Wildcards . 11Query Viewer Options . 11Using PeopleSoft Query Manager . 12Searching Using Query Manager . 12Searching Using Wildcards . 13Query Manager Options . 13Using PeopleSoft Schedule Query . 14Scheduling a Query to Run Through Query Viewer and Query Manager . 14Scheduling a Query to Run Through Schedule Query. 17Scheduling a BI Publisher Report . 20Creating a Simple Query . 22The Records Tab . 23The Query Tab . 26The Fields Tab . 27View SQL Tab . 29Run Tab. 30Creating Joins in PS Query . 32Standard Join . 32Outer Join . 33Creating a Join . 33Using Criteria in PS Query . 35Adding Criteria . 36Using Prompts to Extend the Life of a Query . 42Creating Prompts through the Prompts Tab . 42
PS QUERY BASICS QUICK START GUIDECreating Prompts Through the “Add Criteria” icon. 46PeopleSoft Query Tips and Tricks . 48Helpful Prompts. 48Working With Multiple Effective Dates . 48Optional Prompt with Data Validation . 50Optional Prompt with No Data Validation . 51Optional Numeric Prompt with No Data Validation . 52Date Prompt . 54DATE RANGE PROMPT . 55Prompt with wildcard (%) . 56Fields that Don’t Match (but look like they do) . 57PS Query Core Tables . 58Campus Solutions . 58Finance . 58Human Capital. 58Prompt Tables to Use . 59Campus Solutions (CS) . 59Finance (FIN) . 59Human Capital Management (HCM) . 59Running Large Queries . 59Coding Manual Link . 63FERPA Data Restrictions in Campus Solutions . 63
PS QUERY BASICS QUICK START GUIDEHow to Use This Quick Start GuideAs we work our way through the training manual, be on the lookout for this icon which indicates areas ofspecial interest or importance.I C O NK E Y Valuable informationThe Quick Start Guide is broken down into three sections. Section 1 covers the introduction to PeopleSoft Query with information on how data is stored andaccessed through Query Viewer and Query Manager as well as protocol information and helpfulterms. Section 2 goes over using PeopleSoft Query including Query Viewer and Query Manager as well asSchedule Query. This section also covers the “how to” of using Query Manager to create simpleQueries, Joins and Prompts. Section 3 details PeopleSoft Query Tips and Tricks. This section provides step by step instructionon how to create a number of helpful Prompts, as well as a list of ctcLink core Tables and a list ofPrompt tables to use. The end of the section contains the QRG (Quick Reference Guide) forrunning large Queries as well as a link to the Student and Course Coding Manual.
PS QUERY BASICS QUICK START GUIDEPeopleSoft QueryWelcome to PeopleSoft Query! This versatile tool is simple to use and willallow Query Developers to create Queries in an effective and efficientmanner.Introduction to PeopleSoft QueryPeopleSoft Query or PS Query is an end-user reporting tool that allows Query Developers toextract information in the form of a Query from the relational database, without the need towrite SQL (Structured Query Language) statements. Queries can be simple or quite complex;they may be used one time or repeatedly, as necessary. Results can be displayed on a page or sent toExcel, HTML, XML or scheduled to run at a later time. In its simplest form a Query is basically acompilation of data from certain fields displayed in the way the user has selected.What is a Relational Database?A relational database is a way of storing information that organizes data into tables. The tables arereferred to as records in PS Query and they consist of columns and rows (imagine an ExcelSpreadsheet). The columns represent fields and the rows detail each instance of stored information.Tables can be linked by creating a defined relationship. These relationships enable you to retrieve andcombine data from one or more tables with a single Query. They are based on keys, or columns thatuniquely identify each row of data. If a database only has a single table it is referred to as a flatdatabase but if there are two or more tables it is called a relational database.Imagine that you are responsible for keeping track of all books checked out of the local library. Youmight keep a list similar to the following:First NameJenniferLast NameSmithAddress13 Elm StPhone867-5309Book TitleAnne of Green GablesDate6/28/2015This flat database table works pretty well at meeting the basic need to keep track of who has checkedout which book, but it does have a few drawbacks in terms of efficiency, space required, andmaintenance time. For example, each time Jennifer checks out another book her contact informationwill have to be entered again and again.1
PS QUERY BASICS QUICK START GUIDEFirst NameJenniferJaneLast ss13 Elm St1 FreebieHouse Lane13 Elm St13 Elm StPhone867-5309555-8267Book TitleAnne of Green GablesMercy BladeDue Date6/28/20157/1/2015867-5309867-5309Anne of AvonleaAnn f Windy Poplars7/13/20157/18/2015This is less efficient and opens the database up to possible errors (maybe the phone number is enteredwrong). Therefore, instead of using flat database, multiple tables can be used to “have a place foreverything and everything has a place”.Customer TableSo now all that is needed is a way to relate the two tables. The easiest way to do this is to use a primarykey, a way to tell you what combination of fields in the record make each row unique. In the examplebelow, we have created a CUST ID to identify each customer.Customer TableCUST ID123456Checkout TableCUST ID123456123123First NameJenniferJaneLast NameSmithYellowrockAddress13 Elm St1 Freebie House LaneBook TitleAnne of Green GablesMercy BladeAnne of AvonleaAnne of Windy PoplarsPhone867-5309555-8267Due Date6/28/20157/1/20157/13/20157/18/2015An example of two PS Query tables which can be related by the primary key EMPLID is below.2
PS QUERY 101PeopleSoft Pillars and Modules The CtcLink implementation of PeopleSoft is composed of three Pillars. These are: HCM – Human Capital ManagementCS – Campus SolutionsFSCM – Financials and Supply Chain Management Pillars are comprised of modules where data is captured and stored.PillarCSCSCSCSCSCSCSCSModuleAcademic AdvisementAcademic StructureCampus CommunityCurriculum ManagementFinancial AidRecruiting and AdmissionsStudent FinancialsStudent RecordsModule FSFSAsset ManagementAccounts PayableAccounts ReceivableBillingCash ManagementContractsExpensesGeneral LedgerGrantsCommitment ControlProject RHCHCHCHCHCHCHCHR CoreAbsence ManagementPayrollTime and LaborTalent Acquisition ManagementBenefits AdministrationFaculty WorkloadHRABPYTLTMBAFWEach pillar has its own relational database and as you are creating Queries it is important to notethat standard Queries cannot cross pillar boundaries.Page 3
PS QUERY 101PS Query ProtocolQuery Development Life CycleQueryDevelopmentRequestMigration toProductionProduction(PRD) Search for an appropriate existing query first. If none found, develop query in PQA (Production Quality Assurance) Environment. Make sure the query uses prompts wherever possible. Use the correct query naming convention Add description and a definition to query. Include key search terms in the description Make query public and ensure that it is not in a private folder. Test query to make sure it gives the desired results and runs in 1 minute or less. Submit Service Desk ticket to migrate query to Production (ERP Support / Data &Reporting / Query Migration Request) Include completed “Query Migration Request Form” form with ticket ensuring thequery passes all “Pre-Migration Checklist” requirements. Query will be tested by Data Services (in Olympia) Migration usually occurs overnight and available the next business day Query will be added to Report Library in metaLinkQuery MigrationQueries are developed in the CtcLink PQA environment. Once they have been reviewed andtested by the SBCTC Data Services team, they are migrated over to production. There isgenerally a 24 hour turn-around time for this so the process is quick and efficient.Query DeveloperWrites Query in PQAData Services TeamChecks out QueryQuery Moved toProductionThe Data Services team will review the Query for the following: Correct Naming ConventionQuery has a DescriptionQuery has a Definition Query is PublicUtilization of PromptsPerformance StandardsPage 4
PS QUERY QUICK START GUIDEThe Query Migration Request Form is used by both Query developers and SBCTC to ensure the Querymeets all required criteria. Query Developers should first fill out the Pre-Migration Checklist portion ofthe form before submitting the request for migration. Once the form is submitted with the request, theData Services team will also review the Query for compliance.Query Migration Request FormQuery Pre-Migration Checklist Does the query run without errors and produce the expected results?Does the query run in 1 minute or less?Does Query name meet naming standard? See spreadsheet below.Is the Query Public? Private queries will no longer be migrated.Prompts – does Query use Prompts instead of hard coding? Eg: institution, term, business unit etc.Is the Query description included?Is the Query definition included?Query Migration Request to ProductionQuery name:Pillar: Campus Solutions (CS) Human Capital (HCM) Finance (FIN)Query developer’s name:Query developer’s college:Source environment (which environment is the query currently in):Target environment ( where should it be migrated to):* Query name consists of: the letter Q (for query), the 2 character pillar abbreviation (from thespreadsheet below), the 2 character module abbreviation (from the spreadsheet below) and a briefdescription or name, for example: QCS FA NEED R2TF WORKSHEETPrior to migration, query will be tested by Data Services in Olympia to ensure it meets performancestandards. Please attach this request to the Service Desk migration ticket.5
PS QUERY QUICK START GUIDEPS Query Development ProtocolSEARCH FOR EXISTING QUERIES BEFORE DEVEL OPING NEW QUERIESAlways search existing Queries before creating a new Query. This can potentially save a largeamount of time and resources. If you find a Query that is close you can use that as a foundation foryour new Query by using “Save As”. As all colleges will have access and be storing their Queries in thesame places it is likely you will find that the Query you need has already been developed.QUERY NAMING CONVENTIONQueries developed should all follow the same naming convention which allows them not be droppedor deleted by changes to the environment. The correct protocol is to start the Query name with Qfor Query or V for View followed by: FS – for Finance CS – for Campus Solutions HC – For Human CapitalFor example, a Query for Campus Solutions would start with QCS. This beginning section of the name isthen followed by the two character module abbreviation which is then in turn followed by a description. AsQuery names do not allow for spaces or special characters, use underscores for spaces. An example of acorrectly formatted Query name is:QCS AA ENROLLED NO ADVISORQuery/Pillar ModuleDescriptionQUERY DESCRIPTION AN D DEFINITIONThe Description Field is 30 characters. Use approved abbreviations once the complete list isavailable. Try to use a description which will facilitate searching.The Definition is not a searchable field from the standard PS Query search areas, however it can be foundby creating a Query to search Queries. Use for the “long” description as there is no character limit. Inaddition, add your home institution code, name and email address. For example: Paula McDaniel 890: pmcdaniel@sbctc.eduPUBLIC NOT PRIVATEQueries can be saved either privately or publically. CtcLink Queries should always be saved aspublic so that other Query Developers are able to see and use them. If all Queries are public andable to be searched it will prevent the duplication of effort that could happen if a Query was private and notvisible to others on the team and then reproduced.PROMPTS UTILIZED WHE RE POSSIBLERuntime Prompts, or prompts are pop-up selection windows which appearwhen the Query is ran that asks the end user to select something from alist – for example, a specific Institution. Prompts will be discussed in detail later in the course; however it isimportant to keep in mind that prompts should be used as much as possible to increase the value of theQuery. If a Query is developed for a specific institution with the institution number hard coded into theQuery that Query will only ever be good for that institution, however if a prompt is used where the enduser selects which institution they work for; the Query now becomes usable for everyone regardless ofinstitution.PERFORMANCE STANDARDS6
PS QUERY QUICK START GUIDEThe last thing the Data Analysis team will check is that the Query is efficient and does not take too long torun. Inefficient Queries can use up valuable resources. The best way to ensure your Query is efficient is torun it and verify that its run time is less than one minute.USING QUERIES CREATE D BY OTHERSIf you find a Query that is very close to what you need but not quite right, it is absolutely okayto use that Query as a base that you can then change to fit your needs. The caveat is that youmust first “Save As” to save the Query to a new name which you can then modify. This includes yourown Queries if the one you want to use is already in Production. Keep in mind that there are certain rulesto follow for changing Queries already in production – which include Queries created by you. These ruleswill be discussed further in the training manual.QUERY ORGANIZATIONFolders can be used to categorize and organize Queries. Queries may only be stored in one folder at atime. There is currently no nomenclature standardization at the time of the writing of this manual. Inaddition to Folders, users may also save Queries in a favorites list for easy access and organization.CtcLink PS Query Maintenance ProtocolCHANGING QUERIES THAT ARE IN PEOPLESOFT PRODUCTIONBusiness Rule: Only under the below scenarios can a Query be modified once it’s in PeopleSoftproduction. Any other scenario will result in a new Query being created. Prompt(s) can be added to limit result set. Prompt(s) must use a wildcard (%) or blankoption so Query can be run as originally created. Field(s) can be added to the Query but not removed. New field(s) must not result inrow duplication. The Query Definition should be updated to include a brief description of changes, changedate, initials of Query developer and institution.The modifications or Query creation must be done in the PQA (test) environment. Once the Query istested and ready for migration to production, a migration request must be submitted using the Service Deskticketing system. The Service Desk “Request Type” should be ERP Support Data & Reporting Reporting Query Migration Request.Modifications made to Queries should be logged into the Definition field. Users should input: The date of the changeA description of the changes madeTheir name and email addressThe Query modification information entered into the Query Definition in Query Properties will be includedon the ctcLink Reporting Catalog and communicated to intended audience by Data Services.7
PS QUERY QUICK START GUIDEPS Query TermsRelational Database: A database system in which the database is organized and accessed according to therelationships between data items without the need for any consideration of physical orientation andrelationship. Relationships between data items are expressed by means of tables (records).Record/Table: Records/Tables are the foundation of the Query tool. A record stores data that is arrangedby rows (entries) and columns (fields). For example, a record/table containing data about “people” wouldhave a row for each individual person and columns (fields) for each piece of data stored for that individual(ex: name, address, phone). Records can be added to a Query from the “Records” tab.Column/Field: In a database context, a field is the same as a column. For example, a record of peoplecould contain separate fields such as name, address, phone, etc.Query: A Query is a SQL SELECT statement that reads data from Records and views within the database,and returns the result set to the requester. PS Queries cannot change data within the database.SQL: Structured Query Language (SQL) is a language that provides an interface to relational databasesystems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as anISO and ANSI standard. Some people pronounce SQL "sequel".Criteria: Specifying criteria in your Query allows you to set conditions which limit the results returned bythe Query to only those data that you are interested in. Criteria are viewed and maintained on the“Criteria” tab. Example: You may want to set criteria to limit your Query to retrieve a relevant subset ofdata such as active undergraduate students as opposed to returning results for all active students.Join: The process of combining data from two or more Records using matching keys.Public Query: Public Queries are viewable and editable by any user with access to Query Manager and theproper Record access. Public Queries are available for use by many different users, so please do not saveany changes that you make to a public Query.Private Query: Private Queries are only viewable by the individual who created the Query.Primary Key: A column in a Record whose values uniquely identify the rows in the Record. A primary keyvalue cannot be NULL.Foreign Key: A column in a Record that does NOT uniquely identify rows in that Record, but is used as alink to matching columns in other Records to indicate a relationship.Definitions courtesy of http://www.orafaq.com/8
PS QUERY QUICK START GUIDEUsing PeopleSoft QueryAccessing PS QueryOnce you have logged into PeopleSoft there are three main areas in PS Query you will be able to access: Query ManagerQuery ViewerSchedule QueryQuery Manager is used to create and modify Queries and is only available to Query Developers.Query Viewer is accessible by everyone with a PeopleSoft License and can be used to view Query output inHTML, Excel or XML. Users are also able to schedule a Query to run through Query Viewer.Schedule Query is used exclusively for scheduling aQuery to run at a future time or to run large resultsqueries.Use the following menu paths to access PS Query: Query Manager: Main Menu ReportingTools Query Query ManagerQuery Viewer: Main Menu Reporting Tools Query Query ViewerSchedule Query: Main Menu Reporting Tools Query Schedule Query9
PS QUERY QUICK START GUIDEUsing PeopleSoft Query ViewerQuery Viewer: Main Menu Reporting Tools Query Query ViewerThe first screen of Query Viewer will give users the option to search for a Query as well as display anypreviously selected Favorite Queries.Searching Using Query ViewerFrom this screen users are able to search using the Operator “Begins With” by multiple criteria including: Access Group NameDescriptionFolder NameOwnerQuery NameTypeUses Field NameUses Record NameIn addition there is an Advanced Search capability where even more search functionality is available includingdifferent Operators such as: 10Begins WithBetweenContainsInNot
PS QUERY QUICK START GUIDESearching Using WildcardsPeopleSoft allows users to use wildcards in place of a single space by using or in place of everythingfollowing the wildcard by using %.matches any single character. For example, ones matches any five-character string endingwith “ones”, such as "Jones" or "Cones".% matches any string of zero or more characters. For example, C% matches any string starting with C,including C alone.Query Viewer OptionsUsers are able to further refine search results by selecting folders from the Folder View field.The Query results allow for users to run the results of the Query to: HTMLExcelXMLScheduleNote that this is also where regularly accessed Queries can be added to Favorites by simply clicking on the"Favorite" hyperlink.11
PS QUERY QUICK START GUIDEUsing PeopleSoft Query ManagerQuery Manager: Main Menu Reporting Tools Query Query ManagerThe first screen of Query Manager will give users the option to search for a Query as well as display anypreviously selected Favorite Queries.Searching Using Query ManagerFrom this screen users are able to search using the Operator “Begins With” by multiple criteria including: Access Group NameDescriptionFolder NameOwnerQuery NameTypeUses Field NameUses Record NameIn addition there is an Advanced Search capability where even more search functionality is available includingdifferent operators such as: 12Begins WithBetweenContainsInNot
PS QUERY QUICK START GUIDESearching Using WildcardsPeopleSoft allows users to use wildcards for either a single space by using or everything following thewildcard by using %.matches any single character. For example, ones matches any five-character string ending with "ones", suchas "Jones" or "Cones".% matches any string of zero or more characters. For example, C% matches any string starting with C,including C alone.Query Manager OptionsUsers are able to further refine search results by selecting Folders from the Folder View field.The Query results allow for users either edit the Query or run the results of the Query to: HTMLExcelXMLSchedule Remember that if editing a Query, users must first save the Query under a new name in order not tooverwrite any existing data by selecting “Save As”. Please see the Using Queries Created by Otherssection of this document for more information.In addition Query Manager allows for users to take certain actions on Queries. To the left of the list of resultsare checkboxes.One or multiple Queries can be selected. You can then bring up the list of available actions by selecting thedrop down menu from the Actions field.Available actions are: 13Add to FavoritesCopy to UserDelete SelectedMove to FolderRename Selected
PS QUERY 101Using PeopleSoft Schedule QuerySchedule Query is used exclusively for scheduling a Query to run at a future time or to run Querieswith large results. However, Queries can also be scheduled through Query Viewer or Query Manager.The process to schedule a Query to run through Query Viewer and Query Manager is the same.Scheduling a Query to Run Through Query Viewer and Query ManagerSearch for the desired Query and click on "Schedule "from the list of results.Query ManagerQuery ViewerThe “Scheduled Query” page will come up with the Query name previously selected already filled inalong with designating whether the Query is Private or Public. If there is an existing Run Control IDfor the Query it will display here.If there is not an existing Run Control ID, enter one in the Field. A Run Control ID is used to tell thesystem when and where and how you want the report to run. For example, you might tell the systemto run the report on the database server at 2 am or every Sunday afternoon, or you might tell it torun the report immediately. For most reports, you must also set parameters that determine thecontent o
PeopleSoft Query Welcome to PeopleSoft Query! This versatile tool is simple to use and will allow Query Developers to create Queries in an effective and efficient manner. Introduction to PeopleSoft Query eopleSoft Query or PS Query is an end
Why should you Query? Centers for Medicare and Medicaid Services supports the use of query forms as a supplement to the health care record. “Use of the physician query form is permissible to the extent it provides clarification and is consistent with other medical record documentation.” 3 File Size: 254KBPage Count: 26Explore furtherPhysician Query Examples Journal Of AHIMAjournal.ahima.org2019 update: Guidelines for achieving a compliant query .acdis.orgGuidelines for Achieving a Compliant Query Practice (2019 .bok.ahima.orgThe Physician Query Process Compliance Issuesassets.hcca-info.orgThe Physician Query: What Every Coder Wants You To Knowcapturebilling.comRecommended to you b
Tags:css media query, css media query examples, css media query for ipad, css media query for mobile, css media query value defined in the query. max-width Rules applied for any browser width below the value defined in the query. min-height Rules applied for any browser height over the value defined in the query. max-height Rules applied for any
Records for holidays, weather, eyeballs. Forecast is done one week ahead. Measure SMAPE: Query Previous Model Described Neural Network Query #1 10.60 13.05 Query #2 23.23 22.60 Query #3 48.57 18.23 Query #4 47.41 26.35 Query #5 19.40 16.87 Query #6 19.25 22.65 .
This system calls, Advanced SQL Query To Flink Translator This proposed system receives Ad-vanced SQL Query from the user then generate Flink Code for exe-cuting this Query. Finally, it returns the results of Query to the user. General Terms: SQL Query, Apache Flink Keywords Big data, Flink, SQL Translator, Hadoop, Hive, Advanced SQL Query 1 .
Introduction to PeopleSoft Query PeopleSoft Query or PS Query is an end-user reporting tool that allows Query Developers to extract information in the form of a query from the relational database, without the need to write SQL (Structur
2 excel power query tutorial ( Image Search ) 2 power query training ( Image Search ) 2 vba excel power query power pivots ( Image Search ) 1 excel and power query report ( Image Search ) www.accessanalytic.com.au 2 excel power query power pivot ( Image Search ) 2 power query excel 2010 ( Image Search ) 2 vb
Advanced Query for Secondary Schools Conference 201 5 Advanced Query for Secondary Schools Section 270 - Page 1 Session Description: Multi-table queries, the "CHANGE" button and command; advanced applications of Query for secondary school personnel. 1. Query Principles and a Review of Basics Query Tips
INTRODUCTION TO FIELD MAPPING OF GEOLOGIC STRUCTURES GEOL 429 – Field Geology Department of Earth Sciences Montana State University Dr. David R. Lageson Professor of Structural Geology Source: Schmidt, R.G., 1977, Geologic map of the Craig quadrangle, Lewis and Clark and Cascade Counties, Montana: U.S. Geological Survey GQ-1411, 1:24,000. 2 CONTENTS Topic Page Introduction 3 Deliverables 4 .
