Query class
Download
1 / 50

- PowerPoint PPT Presentation


  • 171 Views
  • Updated On :

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '' - huslu


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Query class l.jpg

Query Class

Projects Office

Augusta State University


Introductions l.jpg
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 l.jpg
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


Agenda4 l.jpg
Agenda

  • Advanced Query Techniques

    • Using Record Joins

    • Adding Runtime Prompts to a Query


Understanding basic query concepts l.jpg
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 ???


Running an existing query l.jpg
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


Run a query option 1 l.jpg
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



Run a query option 2 within the query tool l.jpg
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



Run a query option 2 only l.jpg
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)


Relational structures l.jpg
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


Gl processing overview l.jpg

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


Requisition to check accounting l.jpg

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


Relational database l.jpg
Relational Database

  • Tables designed to reduce redundancy of data

  • Sometimes difficult to find the data

    • Board of Regents – GaFirst – Job Aid

    • Other Queries


Relational database terms l.jpg
Relational Database Terms

  • Table

  • Column

  • Row

  • Field

  • Key Field- a field which uniquely identifies each row of data on a table.


Relational database17 l.jpg
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.



The designer view the work area l.jpg
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


Component view l.jpg
Component View

  • Graphical Display of the Peoplesoft database

  • Database tab

    • List of tables and fields, expressions, prompts

  • Query tab

    • Those used in current query


Query toolbar overview l.jpg
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


Creating a basic custom query l.jpg
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)


Fields tab l.jpg
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


Fields tab24 l.jpg
Fields Tab

  • Changing Column Headings

    • short names

    • Double click on field.

      • Short name

      • Long name

      • Free-form


Translate values l.jpg
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


Slide26 l.jpg
Sort

  • Sort data by one or more fields

    • Ascending is default, right click to change


Save query l.jpg
Save Query

  • SAVE Query – Save icon

    • Private - only you

    • Public – any one with appropriate access

    • Query name and Description

NOTE: Save Often


Output to excel l.jpg
Output to Excel

  • Review Query Output in Excel

  • Exercise – Create a query and run it to Excel


Criteria tab l.jpg
Criteria Tab

  • Rules to display database rows that meet your conditions.

  • Select specific rows of data from the database

    • DeptID = 1062401




Criteria l.jpg
Criteria

  • AND, OR, NOT & ( )

    • Double click on the logical cell – toggles AND/OR

    • Logic of multiple lines with Or

  • Removing criteria


Criteria operators l.jpg
Criteria Operators

  • Algebraic

    • Equal to (=)

    • Not Equal to

    • Greater than (>)

    • Not greater than

    • Less than (<)

    • Not less than


Criteria operators34 l.jpg
Criteria Operators

  • In List, Not in list

  • Between, Not Between

  • Is Null, Is not Null (empty/not empty)


Criteria operators35 l.jpg
Criteria Operators

  • Like, Not Like (wildcards)

    • DeptID Like 106%

      • Gives all deptids that start with 106


Criteria options l.jpg
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


Grouping criteria l.jpg
Grouping Criteria

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


Creating a public query l.jpg
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


Sql tab l.jpg
SQL Tab

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


Using record joins auto join l.jpg
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.


Record joins record hierarchy l.jpg
Record Joins(Record Hierarchy)

  • Already defined in the database

  • Available through access groups


Prompts l.jpg
Prompts

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


Adding runtime prompts to a query l.jpg
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



Aggregate functions l.jpg
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


Using aggregate functions l.jpg
Using Aggregate Functions

  • Aggregate Functions are functions that summarize data

  • Find Agg column on Fields tab

  • Right click in the Agg Column

  • Select Aggregate



Crystal reports formatting l.jpg
Crystal ReportsFormatting

  • Menu Bar (format)

  • Supplementary Tool Bar

  • Special Fields

    • Page number, dates, report comments


Crystal reports summary reports l.jpg
Crystal ReportsSummary Reports

  • Drill down reports

  • Summary Only Data


Where to find documentation l.jpg
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

    • ???????


ad