Running Large Results Queries (Schedule Query)

Purpose: Use this document as a reference for running Large Result Delivered Queries in ctcLink.

Audience: All College Staff in Finance, Human Capital Management (HCM), and Campus Solutions (CS) functions.  The procedures listed below  may be limited based on security access.

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.

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:  Reporting Tools > Query > Query Viewer

Use the Running Delivered Reports, Jobs or Queries Quick Reference Guide for steps on running a delivered query.

  1. If a delivered query results are too large to view in Query Viewer, an error message will result.
  2. Query Scheduler allows you to run Large Result Delivered Queries.  In Query Viewer, under the Search Results, select the Schedule link.
  3. The Scheduled Query page will appear, prompting you to either create a new or find an existing Run Control ID.
  4. 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.
  5. To run an existing Run Control ID, select the Find an Existing Value tab.  Type in the name of the Run Control ID you wish to retrieve.  Select Search. Select the Run Control ID you want to run.
  6. To create a new Run Control ID, select Add a New Value tab.  Type a Run Control ID, using alpha numeric characters with no spaces. Underscore should be used for spacing. Select Add.
  1. 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, select the Find an Existing Value tab.  Type in the name of the Run Control ID you wish to retrieve.  Select Search. Select the Run Control ID you want to run.
    2. To create a new Run Control ID, select Add a New Value tab.  Type a Run Control ID, using alpha numeric characters with no spaces. Underscore should be used for spacing. Select Add.

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

Process Monitor

Navigation:   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, select 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. Select the View Log/Trace link to retrieve the Scheduled Query.
  6. Select 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.  Select OK.
  8. The Scheduled Query output is now available as an Excel file.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.