CREATE A PS QUERY REPORT USING THE PS QUERY TOOL
Start
Programs
Financials Education & Government 7.5 SUV
PeopleTools
OK
Go
PeopleTools
Query
Note: Database tab at bottom left
List of data tables displayed in left box (PS "Record" = Table)
PeopleSoft uses the terms Table and Record interchangeably.
Note: Click on + to display all fields in a Record (Table)
Turn on Results tab if not already done:
View
Preferences
Designer View
On the right side of panel, click on the two options under Result Tab.
OK
(This will enable the result tab to run the query; as well as show the row headings when you run a query).
Click Fields Tab top of right side of screen.
Scroll to Record (Table): JRNL_LN
Click + to see all fields in the Record (Table)
Doubleclick Record (Table) desired, automatically moves to Query tab at bottom left
Note: Doubleclick or drag desired fields from Record (Table) in left box to right side.
Use a "key" field as main selection for efficient running.
Doubleclick or drag following fields contained in JRNL_LN Record (Table):
BUSINESS_UNIT (key field)
ACCOUNT
FUND_CODE
DEPTID
PROGRAM_CODE
MONETARY_AMOUNT
JRNL_LN_REF
LINE_DESCR
Note: Fields (list on left of screen) with a + and arrow displayed indicate a join to another Record (Table); Also – right side of screen "Rel" column will display X to indicate related info in another Record (Table).
Note: Right side of screen now displays following info: column; record.field; format; related info in another table (X); order; translation (XLT); Agg = sums, totals, etc.; heading (can change by doubleclicking).
Note: Run query by clicking on button in tool bar (hover mouse over last 3 buttons to see button descriptions as follows: run query / run to excel / run to crystal report).
Can also run to screen by clicking on results tab and then click refresh.
Click on run query button.
Note: Can now review output and determine if you wish to set criteria, delete rows, add rows, change order of rows; some examples follow:
To change criteria:
Click on criteria tab
Drag field you wish to select on from left side of screen to
right side of screen (BUSINESS_UNIT)
Doubleclick white box under header "expression 2"
Input selection criteria into pop up box = UOD01
OK
Run query (business unit should now all be UOD01)
File, save as, filename = your initials plus desired name (ie. MDLTEST)
To remove an item:
Click on fields tab
Doubleclick on the col number you wish to remove: JRNL_LN_REF
Run query
To change order of results:
Click on fields tab
Doubleclick on Record.Field: DEPTID
Change column number from 4 to 2
OK
Run query
To sort results by a particular column:
After run query, click heading on column you desire to use for sort (will
instantly sort on column chosen). Can also determine sort order by
doubleclicking in "ord" column in the order desired.
To obtain totals/sums and/or do calculations, recommend "run to excel" and perform required functions in Excel.
INFORMATION ABOUT THE CROSS REFERENCE REPORTS CAN BE FOUND IN PEOPLEBOOKS AT FOLLOWING LOCATION:
PeopleTools
Development Tools
Application Designer
Peopletools Cross Reference Reports
Reviewing Cross Reference Reports
JOINS:
Click on fields tab
Note: "Rel" column with "X" indicates there is a related Record (Table) for that field.
Doubleclick on the X in the Rel column for the FUND_CODE field
Click on FUND_TBL (see note bottom of box: FUND_TBL joined with
A.FUND_CODE)
Click OK.
Effective date options box – make no changes at this time, click OK to accept default
(effective date options will be explored in a later query)
Note: The related Record (Table) with its fields will now be displayed in the bottom of the left side of screen with the first Record (Table) identified as A and second Record (Table) identified as B.
Doubleclick on DESCR in the FUND-TBL
Note: right side of screen now displays fields selected from Record A and Record B.
Doubleclick X in the Rel column for the PROGRAM_CODE
Click on PROGRAM_TBL.
Click OK.
Effective date options box – click OK.
Note: Third Record (Table) will now be identified as C.
File
Save As, filename = your initials plus desired name (ie. MDLTEST2)
Exit query screen
Exit PeopleSoft
Exit SQL Base Server
(Note: use joins with caution and review results for logic; complex joins may not produce desired results).
PS QUERY TOOL – CHANGE ALPHA LIST OF TABLES TO ACCESS GROUPS
Start
Programs
Financials Education & Government 7.5 SUV
PeopleTools
OK
Go
PeopleTools - Query
View
Preferences
Component View
Dictionary Tree - click Show Access Groups
OK
RUNNING A QUERY FROM THE PS WINDOWS CLIENT
Note: Queries may be created, modified and run through the Query tool as outlined in the first lesson; however, you may also run an existing query with no modification through the Windows Client. Delivered queries, especially large ones such as the Cross Reference Reports, may run faster through the Windows Client.
Start
Programs
Financials Education & Government 7.5 SUV
PeopleTools
OK
View
Navigator Display – Query
Queries available to run are listed on left side of screen.
Highlight the query you want to run and double-click.
INFORMATION ABOUT THE CROSS REFERENCE REPORTS CAN BE FOUND IN PEOPLEBOOKS AT FOLLOWING LOCATION:
PeopleTools
Development Tools
Application Designer
Peopletools Cross Reference Reports
Reviewing Cross Reference Reports