1 / 50

Query Class

Query Class. Projects Office Augusta State University. Introductions. Introductions Audience Peoplesoft proficient Some steps have multiple ways of doing things – we will show you some of the ways. What do you want to learn in this class? What would you like to use query for?. Agenda.

huslu
Download Presentation

Query Class

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Class Projects Office Augusta State University

  2. Introductions • Introductions • Audience • Peoplesoft proficient • Some steps have multiple ways of doing things – we will show you some of the ways. • What do you want to learn in this class? • What would you like to use query for?

  3. Agenda • Introduction to Peoplesoft Query • Understanding Basic Query Concepts • Running an Existing Query • Understanding the Peoplesoft Query Panel • Creating a Basic Custom Query • Using Selection Criteria • Creating a Public Query

  4. Agenda • Advanced Query Techniques • Using Record Joins • Adding Runtime Prompts to a Query

  5. Understanding Basic Query Concepts • Extract Data from the database • View Data within Query – Results Panel • Send to Microsoft Excel • Send to Crystal Reports • Why use Excel and Crystal Reports ???

  6. Running an Existing Query • Peoplesoft delivered with more than 100 predefined queries, • More added by GaFirst (Board of Regents) • More added by individual institutions • Look at predefined queries before creating your own

  7. Run a query (option 1) • Sign on to Peoplesoft • Select View , Navigator Display, Query • Right click on query name for menu. • Print to grid, Excel or Crystal

  8. Run a Query (option 1)

  9. Run a Query(option 2)Within the query tool • Go, Peopletools, Query • Open Query (icon) • Click on the Prompt (down arrow) • Select a Query • Run Query • Exercise: Find 2 queries in your functional area and run them • View the Results

  10. Run Query (option 2)

  11. Run a Query(option 2 only) • Changing Column Headings and Sorting • Double click on the field in the Record. Field column NOTE: If you are changing a query other than one you created, please save under a new query name (File, Save as)

  12. Relational Structures • Relational Database Structure • Multiple Tables • Empty or Null rows do not take up space • Can have virtually unlimited rows ie Multiple telephone numbers • Efficient Data Retrieval through use of key fields

  13. Journal Generator Processing Edit, Budget Check, and Posting Processes Actuals Ledger Table GL Journal Header GL Journal Line GL Processing Overview When a user posts a transaction to the Purchasing, Accounts Payable, or Accounts Receivable modules, balanced accounting entries are created and posted to the module accounting tables. Journal Generator picks up these entries and creates GL Journals, which are subsequently posted to the Ledger. GL Module Purchasing Accounting Entry Tables AP Accounting Entry Tables AR Accounting Entry Tables

  14. Dr. Pre-Encumbrance Cr. Reserve Pre-Enc. Reversal of P.O. Entry Dr. Reserve Encumbrance Cr. Encumbrance Voucher Entry Dr. Expense Cr. Accounts Payable Dr. Accounts Payable Cr. Cash Reversal of Req. Entry Dr. Reserve Pre-Enc. Cr. Pre-Encumbrance P.O. Entry Dr. Encumbrance Cr. Reserve Encumbrance Requisition to Check Accounting Transactions Check Issuance Requisition Purchase Order Payment Voucher Accounting Entries

  15. Relational Database • Tables designed to reduce redundancy of data • Sometimes difficult to find the data • Board of Regents – GaFirst – Job Aid • Other Queries

  16. Relational Database Terms • Table • Column • Row • Field • Key Field- a field which uniquely identifies each row of data on a table.

  17. Relational Database • In Banner & PeopleSoft, you can use multiple tables to store information. Relational Systems are more suitable for large applications such as a Student Information System & Accounting Systems.

  18. Understanding the Peoplesoft Query Panel

  19. The Designer View(the work area) • Fields Tab – pick the fields to display • Criteria Tab –Conditional information • SQL Tab – view the SQL select statement created from your Fields and Criteria • Results Tab – Results displayed after running query

  20. Component View • Graphical Display of the Peoplesoft database • Database tab • List of tables and fields, expressions, prompts • Query tab • Those used in current query

  21. Query Toolbar Overview • Standard file and copy/paste commands • Sort • Query properties • New union – creates unions between tables • Criteria icons – new, delete, group and ungroup • Negate – specify criteria that a row should not meet • Output – run, run to excel or crystal

  22. Creating a Basic Custom Query • Click on new Query icon • Locate the Record (file) • Use Query Job Aid to find table name • Click on the down arrow of the scroll bar and find the table name. • Right click and select add record • Or double click on the table Note: Component view shifts from Database to Query Tab – if this does not shift you must double click again (until it shifts – cannot pick fields from the Database tab)

  23. Fields Tab • Select Fields – multiple ways • Double click fields • Left click and drag field to Designer View • Changing Column Order on Results – Right click on field and change Column Number or drag field

  24. Fields Tab • Changing Column Headings • short names • Double click on field. • Short name • Long name • Free-form

  25. Translate Values • If field has translate value (code translated to a description). Translate value has N, S, L • Example: dept id of 1042300 = Biology Dept

  26. Sort • Sort data by one or more fields • Ascending is default, right click to change

  27. Save Query • SAVE Query – Save icon • Private - only you • Public – any one with appropriate access • Query name and Description NOTE: Save Often

  28. Output to Excel • Review Query Output in Excel • Exercise – Create a query and run it to Excel

  29. Criteria Tab • Rules to display database rows that meet your conditions. • Select specific rows of data from the database • DeptID = 1062401

  30. Criteria Tab

  31. Using Selection Criteria

  32. Criteria • AND, OR, NOT & ( ) • Double click on the logical cell – toggles AND/OR • Logic of multiple lines with Or • Removing criteria

  33. Criteria Operators • Algebraic • Equal to (=) • Not Equal to • Greater than (>) • Not greater than • Less than (<) • Not less than

  34. Criteria Operators • In List, Not in list • Between, Not Between • Is Null, Is not Null (empty/not empty)

  35. Criteria Operators • Like, Not Like (wildcards) • DeptID Like 106% • Gives all deptids that start with 106

  36. Criteria Options • New Criteria – adds a new row of criteria • Delete Criteria – Deletes highlighted row • Negate Criteria – Criteria that row should not meet (is not = zero) • Group Criteria - adds left and right parentheses to group to process in specified order • Ungroup Criteria – takes away parentheses

  37. Grouping Criteria • Highlight line, press shift key and highlight other lines, then click group criteria icon.

  38. Creating a Public Query • Private Query – only you can run • Public Query – other users can execute • Changing your Private query to Public • File  Properties • Click Public

  39. SQL Tab • View the programming code created by your fields selections and your criteria.

  40. Using Record Joins (auto join) • Record Join – Joins 2 or more database tables • Join on matching key fields • Select 1st table • Select fields from first table • Click on Database tab • Select 2nd table – double click • Join Criteria dialog box • System automatically establishes the joins – you confirm that they are correct. If incorrect change using Insert, Delete, etc.

  41. Record Joins(Record Hierarchy) • Already defined in the database • Available through access groups

  42. Prompts • Allows users to specify values for specific fields (example prompt for fund code, budget period, deptID

  43. Adding Runtime Prompts to a Query • Criteria Tab • Drag the field to the Criteria Tab • Verify the Logical Column and Operator Column have the logic and operators you want • Right click on Expression 2 • Click on Prompt • Run the Query with the new Prompt

  44. Prompt Screen

  45. Aggregate Functions • Gives single value for multiple rows of data • Example: select Dept ID and $ amt where there are 10 journal lines for a dept ID • Sum on the $ amt • Returns one row for that ID with the total $ amt

  46. Using Aggregate Functions • Aggregate Functions are functions that summarize data • Find Agg column on Fields tab • Right click in the Agg Column • Select Aggregate

  47. Aggregate Functions

  48. Crystal ReportsFormatting • Menu Bar (format) • Supplementary Tool Bar • Special Fields • Page number, dates, report comments

  49. Crystal ReportsSummary Reports • Drill down reports • Summary Only Data

  50. Where to find documentation www.aug.edu/projects - Project Office • http://www.usg.edu/gafirst/financials/download/ • Includes Job Aid and Query Manual • At ASU • Ubiquity – financial_docs\manuals\ • At MCG • ???????

More Related