PeopleSoft Intermediate Reporting April 2008
[2]
PEOPLESOFT HRMS INTERMEDIATE REPORTING 3
ABOUT PEOPLESOFT HRMS 3
ABOUT THE GUIDE 3
UNDERSTANDING PEOPLESOFT QUERY 4
PEOPLESOFT QUERY: THE BIG PICTURE 4
PEOPLESOFT REPORTING SOLUTIONS 5
RUN AN EXISTING QUERY USING QUERY VIEWER 5
QUERY TO HTML RESULTS 6
QUERY TO EXCEL RESULTS 7
ADD TO FAVORITES 8
RUN AN EXISTING QUERY USING QUERY MANAGER 9
BASIC SEARCH RESULTS 10
ADVANCED SEARCH RESULTS 10
QUERY TO HTML RESULTS 11
QUERY TO EXCEL RESULTS 11
EXCEL QUERY NOTES 12
USING QUERY MANAGER 13
CREATING QUERIES 19
ADDING A RECORD (TABLE) 20
ADDING FIELDS 22
REMOVING FIELDS 23
MODIFYING FIELD PROPERTIES 24
CRITERIA 28
WORKING WITH MULTIPLE RECORDS 45
CREATING RECORD HIERARCHY JOINS 45
CREATING A RECORD HIERARCHY JOIN: 46
CREATING RELATED RECORD JOIN 48
CREATING ANY RECORD JOINS 51
REMOVE A RECORD FROM A QUERY 53
SAVING, DELETING AND RENAMING QUERIES 55
SAVING QUERIES 55
QUERY PROPERTIES 57
DELETING A QUERY 58
RENAMING A QUERY 59
APPENDIX 2
[3]
PeopleSoft HRMS Intermediate Reporting
About PeopleSoft HRMS PeopleSoft Human Resource Management System (HRMS) provides support for general human resource functions.
Vanderbilt University implemented PeopleSoft HRMS in 1998. Active employees of Vanderbilt University in the
previous system at the time of implementation of PeopleSoft were converted to PeopleSoft. However, detailed
employment information, such as salary history prior to 1998, was not converted. Current PeopleSoft modules
utilized by Vanderbilt University include, but are not limited, to the following:
Administer Workforce
Manage Positions,
Maintain Payroll Data
Manage Payroll Process
Administer Base Benefits
Administer COBRA
Administer Benefits Billing
Administer FMLA
Manage Labor Relations
During the fall of 2004, Vanderbilt University upgraded to PeopleSoft version 8.8. Business Objects version 6.1.a was
also implemented during the fall of 2004 and will continue to evolve through various phases.
Although this manual could possibly be applied to subsequent versions, the manual is to be used in conjunction with
PeopleSoft Version 8.8 and Business Objects version 6.1.a.
Note that PeopleBooks and PeopleSoft query training books have been utilized to build this guide.
About the Guide This guide is designed to support users in learning how to operate the application and to serve as a reference. This
file may be downloaded to a PC or printed.
Here are some tips to help users easily find guide information:
Helpful notes are indicated after this special icon. A must read.
[4]
UNDERSTANDING PEOPLESOFT QUERY
The main reason you store your business data in a database is so you can manipulate it to answer questions and
solve business problems. However, getting just the information you are looking for can often be a difficult and time
consuming.
Query enables you to extract the data you want using visual representations of our PeopleSoft database without
writing SQL (Structured Query Language) statements. The queries can be very simple or as complex as necessary.
Queries can be created to solve a one-time ad-hoc need or queries can be created to run repeatedly.
PeopleSoft Query: The Big Picture We have a lot of valuable data in our PeopleSoft database. The key to using that data effectively is getting the
information you need when you need it.
Data in PeopleSoft Database
PeopleSoft Query
Operator
SecurityQuery Trees
Query Profile
Search Dialog Grid ControlPeopleSoft
nVision
Microsoft Excel
Query retrieves data and passes it to the part of the system that needs it.
Query Security
[5]
PeopleSoft Reporting Solutions End-User Querying
PS/Query PeopleSoft Query enables user to extract information using visual
representation of the database without writing Structured Query Language
(SQL) statements.
Multidimensional and Reporting Analysis
PS/nVision PS/nVision enables users to import information directly from query into
predefined Excel spreadsheets. Use PS/nVision for data analysis and internal
or external reporting.
Production Reporting
SQR SQR is used for more complex reporting needs, procedural logic and to update
database tables. We will not be working with SQR in this class.
Types of Queries
Display data in HTML format (on the Preview tab). You can run queries within Query itself, displaying the result
set in a grid control for review. This option is useful as you refine your queries.
Provide input to a spreadsheet. Query can pass data to Microsoft Excel.
Run an Existing Query Using Query Viewer You may run an existing query in PeopleSoft 8.8 by entering the query name or the first letters of the query name
and conducting a search. Once the query has been identified, you may run the results to HTML or to Excel or you
may view the composition of the query.
There are two ways to generate query results, through Query Viewer or through Query Manager. Query Viewer will
only allow you to run the query results to HTML or to Excel. Users with access to Query Viewer only will not be able
to create or modify queries.
[6]
Navigation: Reporting Tools>Query
Navigation: Reporting Tools>Query>Query Viewer
Select Run to HTML to generate query results in HTML. Select Run to Excel to run the query results to Microsoft
Excel. You can also click the Add to Favorites link to add the query to your favorites list. Once the query is identified,
it will appear whenever you conduct a search within Query Viewer or Query Manager.
Query names cannot contain spaces. Spaces are represented by an underscore (_).
Query to HTML Results A separate window will open and generate the results as shown below. You may run the results to Excel or simply
close the window to return to Query Viewer.
You may run the query results to Excel or to a Text editor by selecting the appropriate hyperlink.
[7]
Query to Excel Results Note: It is best to save the spreadsheet when running a query to Excel. Users have experienced issues when
opening the query rather than saving. Click the Run to Excel HTML link.
Select Save to save the results.
Choose the filename and location and click Save.
[8]
Add to Favorites You may add a query to Favorites within the Query Viewer. This action will result in the query appearing in the My
Favorites query section each time you open the page Query Manager or Query Viewer.
Simply click on the Hyperlink Add to Favorites
The query is added to My Favorite Queries. You may remove the query from My Favorites by clicking on the minus
icon below the heading Remove.
[9]
Run an Existing Query Using Query Manager Query Manager allows you to create a new query, modify an existing query or simply run the query results to HTML
or to Excel. Note that all public queries that your userid has access to will be listed in the search results. Only those
private queries associated with your specific userid will be listed in the search results.
Search for a query using the basic or advanced search function. The image below depicts a basic search for a query.
This type of search is useful if you know the query name or at least the first few letters of the query name. The
second type of search function is an advanced search.
You may view all queries (public and those that are marked private for your user id) by leaving the query
name blank and selecting the search option.
Reporting Tools>Query>Query Manager
Enter Query Name or beginning query name and select Search
Enter the Name of the query first few letters and click Search.
Advanced
Search:
Hyperlink to
Advance
Search
features.
[10]
Basic Search Results
You may edit the query, run it to HTML or to Excel by selecting the hyperlink.
Advanced Search Results The advanced search function offers a variety of search features including Query Name, Uses Record Name (table),
Uses Field Name, Owner, etc.
The example below depicts a result set by querying all queries that are Public and that include the Job record (table).
Basic
Search:
Hyperlink
to Basic
Search
features
[11]
Query to HTML Results
You may run the query results to Excel or to a Text editor by selecting the appropriate hyperlink.
Query to Excel Results Note: It is best to save the spreadsheet when running a query to Excel. Users have experienced issues when
opening the query rather than saving.
Navigation
Icons: allows
user to view
various
records within
the result set.
View All:
allows user to
view the entire
result set. The
default is set to
display 100
records
Excel
Spreadsheet:
Download
result set to
Excel.
[12]
Select Save to save the results.
Choose the filename and location and click Save.
E