PeopleSoft Technical: PS Query

Query Security

The first step to creating queries is setting up the security. This requires creating a query tree and setting up a permission list that allows access to the query trees. Within the tree all tables that the end user is allowed to view must be included. If a table is not in the query tree, then the query will not be available for the end user to run.

Creating Access Group Trees

Query Access Manager is the administration tool for creating and maintaining your query trees. Navigate to People Tools > Security > Query Security > Query Access Manager.

Permissions

For your end users to be able to have access to run queries and utilize the records in your tree, a permission list should be created. This permission list should be added to one of their roles. Navigate to PeopleTools | Security | Permission And Roles | Permission List to create the permission list.

• Query_Admin This page allows for viewing of all queries. The ability tokill a query is done from this page.
• Query_Manager This page allows the creation and running of queries.Give access to this page only if you want your end user to create and change queries.
• Query_Viewer This page will only allow the end user to run queries they have access to.
• Sched_Query This page allows for the scheduling of queries.
• Sched_Query_Qryvw This page will allow the end user to view only the scheduled queries that they have access to.

Query Types

PeopleSoft has delivered six different types of queries. These types are available depending on the permissions given to the end user.

• User query Uses the Query Manager to retrieve data from the database.
• Reporting query Basically the same as a user query, except it is designedto be used with other reporting tools, such as Crystal Reports, PS/nVision, Cube Manager, and XML Publisher.
• Process query A batch query used with Application Engine and the QueryAPI.
• Role query Used with workflow, to determine who should receive e-mails or work list entry. This query returns a list of roles.
• Archive query Used by PS Archive Manager for archiving.
• PS/nVision query Creates a specific data source for PS/nVision reports and matrix layouts.

Query Steps

There are seven major parts to creating a query. Only a few of these steps are required, but they may be needed depending on the query requirements.

1. Select Records Data is stored in records (tables). Depending on the request, you will need to select the records that store the correct data.
2. Select Fields Identify the fields from the records that are needed in the query.
3. Using expression (optional) Expressions are new fields that can be the following: fields that are not on your records, fields combined into one field, or fields using aggregate functions (for example, sums, counts).
4. Using prompts (optional) Requesting input from the end user (for example, Date range).
5. Selection criteria (optional) Applying criteria to the data that is being selected to reduce the data returned from the database (for example, only rows that were created in the current year).
6. Having criteria (optional) Another type of selection criteria, used when the field you are checking is an aggregate value (for example, only pull departments with more than five employees).
7. Run query View the results of the query.

Query Tables

The objects that are created within the Query Manager are stored in tables. The full list of tables involved in Query is as follows:

• PSQRYFIELD Stores all fields used in all aspects of query operation.
• PSQRYDEFN Stores high-level query definitions with version numbers. Non-English definitions are stored in PSQRYDEFNLANG and PSQRYHEADLANG.
• PSQRYRECORD Stores all records used in all aspects of query creation.
• PSQRYSELECT Stores all SELECT requirements by select type, including union, subselect, join.
• PSQRYCRITERIA Stores all criteria expressions in code format.
• PSQRYBIND Stores runtime prompt data.
• PSQRYEXPR Stores the text associated with each criteria expression.
• PSQRYLINK Stores the relationships to child queries.