Scheduling Queries

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 example illustrates the fields and controls on the Schedule Query page.

 Schedule Query page

To submit a process request:

  1. Select Reporting Tools > Query > Schedule Queries.

  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.

  4. Optionally, select an option in the Asian Font field.

    The Asian Font field is available if the output format is set to PDF with Asian fonts.

    If the output format is set to PDF, you are able to run queries to PDF outputs with all Western European fonts, Korean font, Japanese font, Traditional Chinese font, and Simplified Chinese font. Alternatively, use BI Publisher to run queries to PDF outputs with Eastern European fonts, Thai fonts, Russian fonts, and so on; or change the output to another format.

    See Using Scheduled Queries.

  5. 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. This drop-down list appears only if you have one or more Asian languages installed.

    You are unable to open or schedule a query that has been disabled.

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

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

  8. 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 Queries the BI Publisher-related prompts do not appear. The normal basic table-formatted query results will be generated.

See Understanding Run Control IDs, Submitting Process Requests, Scheduling Process Requests, Using Process Status Notifications, 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 example illustrates the fields and controls on the Schedule Query page with query that has In Tree prompts. Definitions for the fields and controls appear following the example.

 Schedule Query page with query that has In Tree prompts

Field or Control

Description

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.

Scheduling and Distributing Query and Connected Query Results with Transformation

In PSQuery, the available user-defined output formats are HTM, PDF, TXT, XFORM, XLS, XML, and XMLP. You select the XFORM format to trigger a transformation (XSLT). Note that you must provide the XSLT to define the desired transformation of the extracted data.

To generate the query results with transformation and distribute that resulting data, you schedule the query process to run using the Process Scheduler Request page and select XFORM as the output format.

The following example shows the fields and controls on the Process Scheduler Request page when you generate the query results with transformation.

 Process Scheduler Request page, XFORM format

To generate the query results with transformation and distribute that resulting data to an external IB node using the Integration Broker framework, you schedule the query process to run using the Process Scheduler Request page, set the output type to IB Node, and set the output format to XFORM. The page also includes the Approval Required option that enables you to review and approve the transformed output before distributing it.

The following example shows the fields and controls on the Process Scheduler Request page when you generate the query results with transformation and distribute that resulting data to an external IB node using the Integration Broker framework. Definitions for the fields and controls follow the example.

 Process Scheduler Request page, IB Node and XFORM

Field or Control

Description

Type

The available options are:

  • Email: If the Email option is selected, the Format and Distribution drop-down lists appear.

  • Feed: If the Feed option is selected, the Format, Distribution, and Feed Title drop-down lists appear, and the only available format is ATOM.

  • File: If the File option is selected, the Format and Distribution drop-down lists appear.

  • IB Node: The IB Node option is used to generate the custom output. The selected Integration Broker node will define the destination of the output. If the IB Node option is selected, the Format list, Node list, Approval Required check box, and Transformation list appear, and the only available format is XFORM.

  • Web: If the Web option is selected, the Format and Distribution drop-down lists appear.

  • Window: If the Window option is selected, the Format and Distribution drop-down lists appear.

Format

The available options are HTM, PDF, TXT, XFORM, XLS, XML, and XMLP.

Note: If the output type is set to Feed, the only available format is ATOM and the Transformation drop-down list is not available.

ScheduledQuery-CSVUnicodeEncoding setting applies only when OutputUnicode property of query class is set to True and scheduled query is set to ‘TXT’ format. It is not a required setting. If it is not set, the CSV output encoding will be UCS2 with BOM. See PSTools Section

If the output type is set to IB Node:

  • The only available option in the Format column is XFORM, which enables you to generate a customized output format.

  • The Node, Approval Required, and Transformation drop-down lists appears.

Node

This option is available when the Type column is set to IB Node.

Select an IB node from the list of the external IB nodes, which were enabled to receive the transformation output with the routing added to receive the delivered Query Transform Server Operation.

Note: To enable an IB Node to receive the query and connected query transformed outputs, you must add a routing for Service Operation PRCS_OUTPUT_IB_OPER. This routing enables the node to be an output destination for the transformed outputs.

Approval Required

This option is available when the Type column is set to IB Node.

Select to request that the transformed output file be reviewed and approved before you distribute its output data to external destinations.

If the Approval Required option is selected, then at report runtime:

  • The transformed output file is posted to the repository for review.

    Note: The Distribution Status column displays Pending until the transformed output file is reviewed, approved, and published.

  • The owner who submits the process can review the result output using Process Monitor.

  • After the result output file is reviewed and approved, it is distributed using the Integration Broker framework based on the Integration Broker node set on the Process Scheduler Request page.

If the Approval Required option is not selected, then at report runtime:

  • The result output file is not posted to the report repository.

  • The result output file is distributed using the Integration Broker framework based on the Integration Broker node set on the Process Scheduler Request page.

Transformation

This option is available when the Format column is set to XFORM.

This option is required for generating customized output.

Select a transformation option from the list of valid XSLTs that were previously defined for the selected query. The selected transformation will determine the XSLT to be applied to the query XML data.

Reviewing and Publishing the Transformed Output

If the Approval Required check box in the Process Scheduler Request page is selected, the query owner who submits the process can access the Process Monitor to review the output and then publish the output to the external destinations after it is approved.

Note: In general, the available distribution statuses are N/A (not available), Generated, Posting, and Posted. You can view the output from a web browser when the process displays the distribution status of Posted. If the process runs with the output type set to IB Node and the output format set to XFORM, then the Pending distribution status is also available. The Pending status indicates that the transformed output files were transferred to the report repository and must be reviewed and published.

To review and publish the transformed output files:

  1. Access the Process Monitor page.

  2. Click the Details link to access the Process Detail page.

  3. Click the View Log/Trace link to access the View Log/Trace page.

    If the process has the run status of Success and the distribution status of Pending, then the Publish link appears next to the request output.

    This example shows the View Log/Trace page with a list of the output files and the Publish link.

     View Log/Trace page

  4. Optionally, review the output file by clicking the Name link in the file List section.

  5. Click the Publish link to send the output to the request Integration Broker node, which is defined in the Process Scheduler Request page.

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 illustrates the fields and controls on the Process Scheduler Request page. Definitions for the fields and controls appear following the example.

 Process Scheduler Request page

Field or Control

Description

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) 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 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 BI Publisher.

    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 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 Understanding Query Feeds, Publishing Query Feeds, Defining Advanced Options for Query Feeds, 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.

  • IB Node: Select the IB Node option to generate custom output using the IB framework. The selected Integration Broker node defines the destination of the output.

    Note: The Node, Approval Required, and Transformation columns are available after the Type column is set to IB Node.

    See “Scheduling and Distributing Query and Connected Query Results with Transformation” in the Scheduling Queries section.

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 becomes unavailable.

Select the XFORM option to display the Transformation column. You must define a transformation to submit the process.

See “Scheduling and Distributing Query and Connected Query Results with Transformation” in the Scheduling Queries section.

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.

Node

This column is available when the Type column is set to IB Node.

Select one node from the list of the external Integration Broker nodes that are valid to receive query output with transformation; for example, the IB external node with routing defined for the query transformation service operation.

Approval Required

This column is available when the Type column is set to IB Node.

Select to request that the transformed output be reviewed and approved before you distribute the output data to the external destinations.

If the Approval Required option is selected, then at the report runtime:

  • The result output file is posted to the repository for review.

  • The owner who submits the process can review the result output using Process Monitor.

  • After the result output file is reviewed and approved, it is distributed using the Integration Broker framework based on the Integration Broker node set on the Process Scheduler Request page.

If the Approval Required option is not selected, then at the report runtime:

  • The result output file is not posted to the report repository.

  • The result output file is distributed using the Integration Broker framework based on the Integration Broker node set on the Process Scheduler Request page.

See “Reviewing and Publishing the Transformed Output” in Scheduling Queries.

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 PeopleCode Syntax Quick Reference.

Transformation

This column is available when the output format is set to XFORM.

Select a transformation from the list of valid transformations that were defined for the query or connected query.

See Extracting and Transforming Data from PeopleSoft Pure Internet Architecture.

To select a process request:

  1. Select Reporting Tools > Query > Schedule Queries.

  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.

  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.

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 illustrates the fields and controls on the Process Monitor - Process List page. Definitions for the fields and controls appear following the example.

 Process Monitor - Process List page

Field or Control

Description

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, and then select a unit type from the drop-down list. 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, Pending, 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.

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 example illustrates the fields and controls on the Report Manager - List page.

 Report Manager - List page

Note: BI Publisher reports can also be viewed by navigating to Reporting Tools > BI Publisher > Search for BIP Reports.

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.