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