Modifying, Scheduling, and Organizing Queries

This chapter discusses how to:

Modify queries.
Schedule queries.
Organize queries

Modifying Queries

This section discusses how to:

Open existing queries.
Rename queries.
Delete queries.
Update existing queries.
Repair existing queries automatically.

Opening Existing Queries

Use Query Manager to open and view queries.

To open an existing query from your browser:

Select Reporting Tools, Query, Query Manager.

The Query Manager Search Results page appears.
Search for a query using the basic or advanced search functions.

Both search types enable you to select from the following search by criteria: access group name, description, folder name, owner, query name, type, uses field name, and uses record name. The basic search enables you to search using the begins with condition. The advanced search enables you to perform a progressively narrower search using one or more search by criteria and selecting from a choice of conditions for each search by criterion. The following list provides instructions on using search by criteria:
- To perform a basic search by name, enter the name of the query in the Search By field and click the Search button.
  
  If you do not know the name of the query and want to search through a list of queries, leave the Search For field blank and click the Search button to display a list of up to 300 queries.
  
  Perform a partial search by entering part of a query name or description in the Search By field. Use the drop-down list box to search queries by either name or description, and then click the Search button to display a list of queries that match your search criteria.
  
  The Search Results page appears and lists all the queries that match the search criteria. The following information appears: query name, query description, and ownership (public or private).
- If you know the name of the query that you want, enter it in the Search By field and click the Search button.
- To perform an advanced search, click the Advanced Search link.
  
  On the advanced search page, select the appropriate search by criteria and conditions and enter a search string in each of the corresponding fields. Then click the Search button to display a list of queries that match your search criteria. The Search Results page appears listing the query name, description, owner, and folder for all the queries that match the search criteria.
Click the Edit link on the row of the query that you want to modify.

See Also

Meta-SQL

Renaming Queries

You can rename existing queries.

To rename a query:

Select PeopleTools, Utilities, Administration, Query Administration.
On the Admin (Administration) page, choose a predefined search or a manual search by clicking the respective Search button.
Select the Queries from the Query List.
Click Rename.

The Rename Queries page appears, listing the current query names and query owners.
Enter new names for the queries in the New Name field.
Click OK to save your changes.

Note. Alternatively, you can use the Query Manager search page (Reporting Tools, Query, Query Manager) to rename a query. You must first select a query that you want to rename, and then select Rename Selected from the Action drop-down list box.

Deleting Queries

You can delete any public query that you have access to as well as any private query that you have created.

To delete a query:

Select PeopleTools, Utilities, Administration, Query Administration.
On the Admin (Administration) page, choose a predefined search or a manual search by clicking the respective Search button.
Click Delete.

The Query Delete Verification page appears.
Click Yes to confirm the deletion or No to avoid deletion.

Note. Alternatively, you can use the Query Manager search page (Reporting Tools, Query, Query Manager) to delete a query. You must first select a query that you want to delete, and then select Delete Selected from the Action drop-down list box.

Updating Existing Queries

At times, you might want to update a query to reflect new conditions or copy an existing query and modify it for other uses. You can open a query, examine it to determine whether it is suitable, and then use the Save As function to save it with a new name.

To update an existing query:

Select Reporting Tools, Query, Query Manager.
On the Query Manager Search Results page, find the query that you want to modify and click its Edit link.

The Fields page appears.
Navigate to the page that you want to modify—except the Run page.
Enter the new information on the page.
Click either the Save button to update the existing query, or the Save As link to save a different instance of the query under a new name.

Note. When using Microsoft Windows-based Query Designer, any modification to a previously saved query needs to be saved before downloading query results to Microsoft Excel or to Crystal Reports 9. A message will appear reminding you to save your work. This is to ensure that accurate data are displayed. New ad-hoc queries that have never been saved before in the database can be downloaded to Microsoft Excel and to Crystal Reports 9 without having to save after every modification.

Repairing Existing Queries Automatically

A record definition that is used in a query may change. When this occurs, PeopleSoft Query automatically repairs the query when it is opened. A warning message informs you about the problem and what actions were taken:

Problem	Action
Field deleted from the record definition is a selected field.	The field is removed from the selected list. If the field is the only field selected, the query cannot be saved.
Field deleted from the record definition is used in a prompt.	No action taken. This does not affect the prompt unless the field has been completely removed from the database, and the prompt label is either a short or long translate.
Field deleted from the record definition is used in an expression.	The field is removed from the expression. This may result in an invalid expression, but the query can be saved.
Field deleted from the record definition is used in a criterion.	The criterion is removed. If criterion is a subquery criterion, all lower subqueries are deleted.
Record deleted is the first record in the query.	Error returned. The query cannot be repaired.
Record deleted is not the first record in the query.	The record and all fields are removed. Note. The actions specified previously for deleted fields apply.
Record deleted contains hierarchy joins below it.	Record and all records joined below are removed. Note. The actions specified previously for deleted fields apply.

Note. If a record has been deleted, the query no longer appears in the list of queries because you no longer have access to all of the records in that query. To open such a query, you need to use Query API.

See Also

Setting Query Permissions

Scheduling Queries

Query Manager interacts with PeopleSoft Process Scheduler to enable you to schedule queries.

This section discusses how to:

Submit process requests.
Select process requests.
Use Process Monitor.
View output in Report Manager.
Specify a user’s language.

Submitting Process Requests

A process request enables you to submit a job or process to run. The Schedule Query page submits a process request and brings up the Process Request page, which enables you to specify such variables as where to run the process and in what format to generate the output based on a run control ID.

To submit a process request:

Select Reporting Tools, Query, Schedule Query.
Search for and select an existing run control ID, or select the Add New Value tab to enter a new ID.
Select the query for which you want to schedule and update any prompt parameters.

Use the Update Parameters link to select or change the value that is required for each available prompt. When values have been selected, they appear in the Prompt Name and Value fields.

Note. If the query does not have any prompt, the Update Parameters link does not appear.

Note. If you have one or more Asian languages installed, select the language that you want from the Asian Font drop-down list box. This drop-down list box appears only if you have one or more Asian languages installed.
Click the Save button to save changes and remain on the Schedule Query page.
Click the Run button to submit the query process request and display the Process Scheduler Request page.

When you are scheduling queries from Query Manager or Query Viewer, the Run button is replaced with an OK button.
Click the Process Monitor link to view the status of your request; click the Report Manager link to view the output of your request.

Note. If a Query that is used as a data source for XML Publisher is run through Reporting Tools, Query, Schedule Query, the XML Publisher-related prompts do not appear. The normal basic table-formatted Query results will be generated.

See Submitting and Scheduling Process Requests.

See Scheduling Reports in Query Report Scheduler.

Selecting Process Requests

The Process Scheduler Request page enables you to set the server, run date and time, how often the process runs (the recurrence of the process), output type, and format.

To select a process request:

Select Reporting Tools, Query, Schedule Query.
Select a run control ID, and click the Run button.

The Process Scheduler Request page appears.

Complete the fields on the Process Scheduler Request page.

Server Name	Select the name of the server on which you want the process to run.
Recurrence	Select the recurring time intervals for the process to run. For example, to run a process every weekday at 5:00 p.m., select the predefined recurrence definition M-F at 5pm.
Time Zone	Select the time zone in which the process will run. For example, you might be in Eastern Standard Time (EST) and schedule a process to run in Pacific Standard Time (PST).
Run Date	Select the date on which you want the process to run.
Run Time	Select the time at which you want the process to run.
Reset to Current Date/Time	Click to reset the run date and time to the present date and time.
Select	Select a job or process to run. You can select multiple jobs and processes.
Description	Identifies a process or job. Jobs are listed as links. Click the link to display the Job Detail page, which shows all of the individual processes and jobs that are associated with the selected main job.
Process Name and Process Type	Identifies the name and type (such as COBOL or Crystal) of the process as it appears in the process or job definition.
Type	Select the output type for this job or process. An output type that is selected for a process at the process definition level overwrites the output type and the output type drop-down list box becomes unavailable. An output type that is selected for a job at the main job level carries through to the job items. An output type that is selected for individual job items overwrites the output type that is entered for the parent job. Values are: File: Writes the output to the file that you indicate in the Output Destination field. Note. For PS/nVision, the Output Destination must contain the full path and the name of the file. Printer: Sends the output to a printer. You can enter a custom printer location in the Output Destination field if you have the appropriate security access. If the Output Destination field is left blank, the printer that is defined on the Process Profile Permissions page is used. If that printer is undefined, the default printer that is defined for the process scheduler is used. Email: Sends the output through an email. To distribute a report to an email list, enter the appropriate information on the Distribution Detail page by clicking the Distribution link. By default, the output is sent through email to the person running the process. This option is available for Structured Query Reports (SQR), PS/nVision, and Crystal reports. Web: Sends all output of the process to the report repository, including log and trace files. The format of the report is specified by the format list. Window: Sends the output to a new browser window. The status of the process now appears in the new browser window before the results are displayed. The different statuses are Queued, Initiated, Processing, Success, Error, or Warning All output for the process is also sent to the report repository, including log and trace files. The format of the report is specified by the format list. When multiple processes are requested, a new browser window is opened for each request. Note. This output type is not available if the user does not have REN Server Report Window permission, or if no active REN Server cluster is available for Reporting. To grant access to the new browser window, the permission lists of the users must include full access for the Realtime Event Notification for Reporting Window and the WEBLIB_RPT web library with full access.
Format	Select the output format for this job or process. When an output format is selected for a process at the process definition level, it cannot be changed. Therefore, the output format drop-down list box becomes unavailable. Note. An output format that is selected for a job at the main job level carries through to the job items. The format that is selected for individual processes or jobs that are attached to a job overrides the format that is entered for the parent job. Note. To get text-wrapping support for very long text fields, use HTML format. Note. PDF format has a maximum page width size of approximately 353 cm and it does not support text-wrapping. All output columns must fit within this limit. A column’s size will be as wide as the longest text it has to accommodate. If a text field's length needs more than 353 cm of space, the text may go beyond its column border and successive columns will not print.
Distribution	Click the Distribution link to access the Distribution Detail page, where you enter additional distribution information when the output type is Web, Window, or Email. Also use this page to select a folder name to which the output should be distributed when the output type is Web or Window.

Click OK to run the query.

The system displays the Schedule Query page. The process instance number appears below the Run button.
Click the Process Monitor link to view the status of your request.

The Process List page appears. The Run Status field reveals the status of your scheduled or running process.

Using Process Monitor

The Process Monitor can be used to view the status of your process requests.

You can access the Process Monitor by clicking the Process Monitor link on the Schedule Query page or by navigating to PeopleTools, Process Scheduler, Process Monitor.

Process Monitor consists of two pages: the Process List page and the Server List page.

The Process List page enables you to monitor the process requests that you have submitted.

You can select filtering options—for example, process type and run status—to view only selected process requests. And you can click the Details link to view details that are associated with particular process requests.
The Server List page enables you to monitor the PeopleSoft Process Scheduler server agents within your system.

Process Monitor - Process List page

User ID	View the processes that were submitted by a particular user ID. Usually, you view by your own user ID.
Type	View by a particular process type—for example, Application Engine.
Last	Specify an interval of time by which to limit the process requests that appear in the list. Enter a numerical value in the edit box preceding the drop-down list box, and then select a unit type from the drop-down list box. Values are Days, Hours, and Minutes.
Refresh	Click to check the current status of a process. This button refreshes the list.
Server	Select to view processes that run on a particular server.
Name	View processes by a process name.
Instance	Specify a range of instances by which to limit the process requests that appear in the list. To limit the view to a single request, enter the required instance ID in the first text field.
Run Status	Select if you want to view processes by a specific status, such as Success or Error.
Distribution Status	Displays the distribution status for each individual job and process. Valid statuses are N/A, None, Generated (OS390), Not Posted, Posting, and Posted.
Save On Refresh	Select to save filter criteria changes when you click the Refresh button.
Instance	Displays the process instance, that is, the order in which the process appears in the queue. This number is automatically generated.
Seq (sequence)	This field is blank; it is not used for query processes.
User	The ID of the user who submitted the process.
Details	Click to open the Process Detail page.

Viewing Output in Report Manager

Report Manager is like your own personal inbox of reports and process output. It provides a secure way to view report content, check the posting status of your output, and see content detail messages.

View all of your reports by opening your Report List in your browser.

Select Reporting Tools, Report Manager, or click the Report Manager link on the Schedule Query page.

Note. XML Publisher reports may also be viewed by navigating to Reporting Tools, XML Publisher, XMLP Report Search.

See Also

Using Process Monitor

Working With Processes and Reports

Searching the XML Publisher Report Repository

Specifying a User’s Language

For scheduled queries, the system uses the language that is specified in the user’s profile, not the language that is selected during sign-in. The system also uses the international and regional settings that the user has specified using My Personalizations. If you have not specified personal settings, the system uses the default installation international settings.

Note. Most PeopleSoft components can use the international settings from the browser by default if the user has not set any user specific settings. However, this is not available for scheduled queries or any PeopleSoft Process Scheduler processes.

See Also

Administering User Profiles

Organizing Queries

This section discusses how to:

Add queries to the My Favorite Queries list.
Copy a query to another user's list of queries.
Move a query to a folder.

Adding Queries to the My Favorite Queries List

The Query Manager and Query Viewer search pages can include a list of queries called My Favorite Queries. If you use certain queries often, you can put the queries in this list for easy access.

Note. Although you can view a list of favorites, you can't create a list of favorites in the Query Viewer.

To add a query to the My Favorite Queries list:

Select Reporting Tools, Query, Query Manager.
On the Query Manager Search Results page, select the query that you want to add to the My Favorite Queries list.
Select the Add to Favorites option from the Action drop-down list box.
Click the Go button.

The query appears in the My Favorite Queries list group box .

Note. You can add multiple queries to the My Favorite Queries list at once by selecting several queries before selecting Add to Favorites and clicking the Go button.

Copying a Query to Another User's List of Queries

The Query Manager enables you to copy a query from your list of queries to another user's list of queries.

Note. You can copy only non-public queries to another user's list of queries.

If the target user does not have permission to access all of the records in a copied query, that query does not appear in the target user's list of queries. When permission has been granted, the query appears in the list.

To copy a query to another user's list of queries:

Select Reporting Tools, Query, Query Manager.
On the Query Manager search results page, select the query or queries that you want to copy.
Select the Copy to User option from the Action drop-down list box.
Click the Go button.

The Enter User ID page appears.
Enter the user ID of the user to whom you want to copy the query.
Click OK.

Moving a Query to a Folder

Organizing queries in folders can help you more easily access the queries.

To move a query to a folder:

Select Reporting Tools, Query, Query Manager.
On the Query Manager search results page, select the query or queries that you want to move to an organization folder.
Select the Move to Folder option from the Action drop-down list box.
Click the Go button.

The Move to Folder page appears.
Select one of the following:
- Select an existing folder to move to: From the drop-down list box, select the folder to which you want to move the queries.
- OR enter a folder name to move to: Enter the name for a new folder to which you want to move the queries.
Click OK.