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 component to open and view queries.

This is an example of the Query Manager component:

To open an existing query from your browser:

1. Select Reporting Tools, Query, Query Manager.

   The Query Manager Search Results page appears.

2. 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.

3. Click the Edit link on the row of the query that you want to modify.

See Also

Meta-SQL Elements

Renaming Queries

You can rename existing queries using the Rename Queries page, as shown:

To rename a query:

1. Select PeopleTools, Utilities, Administration, Query Administration.

2. On the Admin (Administration) page, select a predefined search or a manual search by clicking the respective Search button.

3. Select the queries from the Query List.

4. Click the Rename button.

   The Rename Queries page appears, listing the current query names and query owners.

5. Enter new names for the queries in the New Name field.

6. Click the OK button 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:

1. Select PeopleTools, Utilities, Administration, Query Administration.

2. On the Admin (Administration) page, select a predefined search or a manual search by clicking the respective Search button.

3. Click the Delete button.

   The Query Delete Verification page appears.

4. Click the Yes button to confirm the deletion or click the No button 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:

1. Select Reporting Tools, Query, Query Manager.

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

   The Fields page appears.

3. Access the tab that you want to modify the information—except the Run tab.

4. Enter the new information.

5. 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 must be saved before downloading query results to Microsoft Excel or to Crystal Reports 2008. 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 2008 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.

This table lists the problems and their corresponding actions:

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.

This is an example of the Schedule Query page:

To submit a process request:

1. Select Reporting Tools, Query, Schedule Query.

2. Search for and select an existing run control ID, or select the Add New Value tab to enter a new ID.

3. 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.
   
   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.
   
   You are unable to open or schedule a query that has been disabled.

4. Click the Save button to save changes and remain on the Schedule Query page.

5. 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.

6. 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 BI Publisher is run through Reporting Tools, Query, Schedule Query, the BI Publisher-related prompts do not appear. The normal basic table-formatted query results will be generated.

See Submitting and Scheduling Process Requests, Scheduling Reports in Query Report Scheduler.

Schedule Query with Tree Prompt

You can schedule queries that have tree prompts using the Schedule Query component or using the Schedule links in the Query Manager and Query Viewer. While scheduling these queries, you are prompted for selecting the tree for the specified field and its node list. After the successful selection of values for the tree prompts, other prompts are available for your input. These queries can either be saved and used in the future or can be scheduled and run immediately by clicking the Run button.

This is an example of the Schedule Query page when you run a query that has a criteria with tree prompt:

Update Tree Parameters	The Update Tree Parameters link is available if you are running queries that have tree prompts. Click this link to access the Select a Tree page and the Select Tree Node page, where you can select a tree and its node list.
Tree Prompts	The Tree Prompts section displays the field names and expression texts, which will be used in forming the SQL before running the query.

Note. In Windows query application psqed.exe, you also can create tree prompts and use that prompts to enter input at runtime. Execution of the query is similar to PIA Query Manager, where you select the tree node to run the query.

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.

This example shows the Process Scheduler Request page:

To select a process request:

1. Select Reporting Tools, Query, Schedule Query.

2. Select a run control ID, and click the Run button.

   The Process Scheduler Request page appears.

3. 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. If you set Type value to File or Printer, you must enter the name of the directory in the Output Destination field. 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. Note. Using Scheduled Query, you can schedule queries to run and have the results email directly to recipients as an attachment. If a report fails to run, no email is sent. If a query result includes a file without data, the report headers or other text are sent in email that indicates that the report was empty. If no file is found, the email will contain a meaningful message to inform recipients. If sending email is successful, process status is be marked as success; otherwise, it ends in error. If the generated output file is too large, the email is returned to the sender with information to indicate any attachment size limitations. Feed: Sends query results as feeds. You have to enter query prompts values, select the Format as Atom, and select a feed definition which matches the query prompts values. If there are no existing feeds with matching prompt values, all the scheduled feeds appear. You can select any scheduled feed to make a copy of that feed and later also change the title of the new cloned feed. See Creating and Using Query Feeds. 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. To get text-wrapping support for very long text fields, use HTML format. 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. XML format is for producing webrowset XML format result. XMLP format is producing XMLP format result.
   Distribution	Click 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.
   Output Destination	Enter the file directory path or printer destination for the output. For example, C:\Documents and Settings\admin\psft\pt\8.5x\appserv\prcs\QEDMO\log_output. Note. This field is available only when the output type that you select is File or Printer. If you select an output destination (OutDest) for a process at the process definition level, this field is populated with that output destination. See ProcessRequest Properties.

4. Click the OK button to run the query.

   The system displays the Schedule Query page. The process instance number appears below the Run button.

5. 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 selecting 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.

This example shows the 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 in-box 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.

You can view all of your reports by opening your Report List in your browser.

To access the Report Manager, select Reporting Tools, Report Manager, or click the Report Manager link on the Schedule Query page.

This is an example of the Report Manager - List page:

Note. BI Publisher reports can also be viewed by navigating to Reporting Tools, BI Publisher, BIP Report Search.

See Also

Using Process Monitor

Working With Processes and Reports

Searching the BI 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:

1. Select Reporting Tools, Query, Query Manager.

2. On the Query Manager Search Results page, select the query that you want to add to the My Favorite Queries list.

3. Select the Add to Favorites option from the Action drop-down list box.

4. 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 nonpublic 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:

1. Select Reporting Tools, Query, Query Manager.

2. On the Query Manager search results page, select the query or queries that you want to copy.

3. Select the Copy to User option from the Action drop-down list box.

4. Click the Go button.

   The Enter User ID page appears.

5. Enter the user ID of the user to whom you want to copy the query.

6. Click the OK button.

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:

1. Select Reporting Tools, Query, Query Manager.

2. On the Query Manager search results page, select the query or queries that you want to move to an organization folder.

3. Select the Move to Folder option from the Action drop-down list box.

4. Click the Go button.

   The Move to Folder page appears.

5. 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.

6. Click the OK button.