How to run large result delivered queries.

What are Large Result Delivered Queries?

Delivered Queries are queries that have been developed and are used to retrieve selected data. Large Result Delivered Queries are queries that have an output that is too large to view in Query Viewer.  These queries should be run in Query Scheduler.

Who will use ctcLink Large Result Delivered Queries?

Finance, HCM, and CS staff when large amounts of operational data is needed. Usage is based on security access.

Running a Large Result Delivered Query

Navigation:  Main Menu -> Reporting Tools -> Query -> Query Viewer

Use the Running Reports, Jobs, and Queriesin ctcLinkQuick Reference Guide for steps on running a delivered query.

  1. If a delivered query results are too large to view in Query Viewer, the below error message will result.
Query Viewer
  1. Query Scheduler allows you to run Large Result Delivered Queries.  In Query Viewer, under the Search Results, click on the Schedule link.
Query Search ResultsZoom: Query Search Results
  1. The Scheduled Query page will appear, prompting you to either create a new or find an existing Run Control ID.
  2. Run Control IDs are tied to a user ID and are visible only to the creator. Instead of entering the same values each time a query is scheduled; a Run Control can be saved with these settings. The next time the query is scheduled, the Run Control ID is selected and the system completes the settings with the previously defined parameters.
    1. To run an existing Run Control ID, click the Find an Existing Value tab.  Type in the name of the Run Control ID you wish to retrieve.  Click Search. Click on the Run Control ID you want to run.
    2. To create a new Run Control ID, click on Add a New Value tab.  Type a Run Control ID, using alpha numeric characters with no spaces. Underscore should be used for spacing. Click Add.
Add a New Value tab

5. If there are prompt(s) associated with the query (i.e. Institution or Business Unit), a pop up screen will appear asking you to populate the prompt(s). Not all queries will have prompt(s).

6. The Process Scheduler Request screen will appear.  Select additional choices on how the Scheduled Query will run.

a. Server Name should remain blank.

b. Recurrence will indicate how frequently the Schedule Query should run.  Leave blank for a one-time occurrence.

c. Run Date and RunTime indicates when the Scheduled Query will run.  The default settings are to run the Scheduled Query immediately.  Scheduled Queries can also be run at future dates and times.

7. From the Process List, use the dropdowns to make the below choices.

a. Type (Email/Feed/File/IB Node/Web/Window).  It is recommended to run Large Result Delivered Queries as Web.

b. Format (HTM/PDF /TXT/XFORM/XLS/XML/XMLP).  It is recommended to run Large Result Delivered Queries as a TXT format. This will result in an Excel output file. Click OK.

Process Scheduler RequestZoom: Process Scheduler Request

 Process Monitor

Navigation:  Main Menu -> PeopleTools -> Process Scheduler -> Process Monitor

  1. To view the Query Scheduler output, navigate to Process Monitor.
  2. The Scheduled Query should be listed in the Process Monitor.  The Scheduled Query is ready to retrieve when the Run Status shows Success.
  3. When the Scheduled Query indicates it has run successfully, click on the Details link.
  4. The Process Detail screen will appear.  This screen gives details on the Parameters used; the Message Log gives detail if there are errors running the Scheduled Query. 
  5. Click on the View Log/Trace link to retrieve the Scheduled Query.
  6. Click on the output file.  The output file will include the Scheduled Query name and the Process Instance Number in the name.  It will be a .csv file if you selected to run a TXT.
  7. A pop up window will ask what application to open the Scheduled Query with.  If you selected TXT, the default is Microsoft Excel.  Click OK.
  8. The Scheduled Query output is now available as an Excel file.

 

Print Article