Understanding Query Types

User queries retrieve data from the database directly from Windows-based Query Designer or the web-based Query Manager and Query Viewer applications.

There are two usage types of User query:

• Reporting query

• PS/nVision query

Reporting queries are essentially the same as user queries, except that they are designed to be used by another reporting tool. Reporting queries can be used as data sources for ad hoc queries, scheduled queries, Connected Query, Composite Query, Pivot Grid, PS/nVision, Cube Manager, or BI Publisher.

When you define a custom report, you often include runtime variables that users specify when they run the report. For example, you might want users to be able to say which business unit, location, or time period to report on. Therefore, your reporting query may include one or more runtime prompt variables.

If your query requires input parameters, you must decide how users should enter them. If they run the report from any of the PeopleSoft Query applications, they can enter values into the page that appears in Query Manager, Query Viewer, or Scheduled Query.

When reporting queries are used as a data source to another third party reporting product, you may need to:

• Create or modify a page to collect the necessary input parameters.

• Create or modify a record definition, based on the input parameters.

• Add a process definition to PeopleSoft Process Scheduler.

See Defining Criteria , Adding New Process Definitions.

Use PeopleSoft Query to create a query in order to specify the data source for a PS/nVision report layout. However, if you want to use your query in a PS/nVision matrix layout, you must apply aggregate functions to at least one column.

Queries used with PS/nVision tabular layouts do not have the same restrictions as matrix layout queries; they are like other reporting queries and do not require an aggregate column.

See Working with Aggregate Functions, Working with Subqueries, Working with Unions, Joining Records, Understanding Layouts.

Process queries are queries that you intend to run periodically using a batch process. Create these automated batch processes using PeopleSoft Application Engine and the Query API. For example, you could write a query that returns any overdue receivables and schedule a batch process to run the query once a week.

PeopleSoft Workflow uses role queries to determine to whom to send an email, form, or worklist entry. A role query needs to return one or more role IDs based on the data that has been saved on the page that is triggering the routing.

Because a role query returns a list of role users, the record definition that you want is either PSROLEUSER (which lists role users and the roles to which they are assigned) or ROLEXLATOPR (which lists role users and their IDs).

The only field that you select in your query is ROLEUSER. Of course, you use other fields and join to other record definitions to specify the criteria that role users can select. But no matter how complex the query is—how many joins or selection criteria it has—it must return ROLEUSER and nothing more.

Define a role as a query because you want to route items differently based on the context of the transaction that the users are performing. Thus, every role query contains at least one bind variable whose value gets set at runtime. The bind variable or variables correspond to the data on which you want to base the routing decision. At runtime, the system sets the values of the bind variables based on data from the page that triggers the event.

Save your role queries with names that begin with [ROLE] so that you can identify them as role queries.

See Understanding PeopleSoft Workflow .

You can save a query as an archive query if you have access to workflow queries that include Archive Query, Role Query, and Process Query. These queries are generally only used by the PeopleSoft Data Archive Manager.

See Understanding PeopleSoft Data Archive Manager.