Reporting

Access for Query Manager, the UAConnect reporting tool, is requested on the UAConnect Security Request. Information about requesting access to create or view queries in UAConnect is located in the Access section of this site.

This section represents the most requested topics pertaining to Query Manager.  A list of current Query Manager training sessions are located in the Training section.

 

The Query Manager search pages can include a list of queries called My Favorite Queries. Query View search pages can view a list of favorites, but a list of favorite queries cannot be created in Query Viewer.

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

2. Select Add to Favorites from the Action drop down menu.

Screenshot of Query Manager page with Save To Favorites featured

3. Click GO. The query appears in the list group box.

4. Multiple queries can be added to the My Favorite Queries list at one time by selecting the queries before choosing Add to Favorites.

Link to PDF

Query Manager allows you to copy a query from your list of private queries to another user. You can only copy private queries to another user's list of queries.

If the target user does not have permissions allowing access to all the records in a copied query, the query will not appear in the target user's list.

1. On the Query Manager Search Results page, select the query/queries that you want to copy.

2. Select Copy to User from the Action drop down menu.

Screenshot of Saving Query to Another User

3. Click GO. The Enter User ID page appears.

4. Enter the Username of the user who will receive the copied query.

5. Click OK.

Link to PDF

The University of Arkansas has defined a naming convention for queries that are developed in UAConnect. Knowing this naming convention should assist you in locating queries.

All UAConnect query names being with UA_, followed by the characters that define the department or college, which is followed by another underscore. The remaining characters define the query more specifically.

Some examples of department conventions are:

AD = Undergraduate Admissions

SR = Student Records

SF = Student Financials

FA = Financial Aid

GR = Graduate School Admissions

Following is an example of a query using the correct naming convention:

UA_AD_UGRD_APPLS

    1. UA_ - The first part of the query name that is required on all University of Arkansas generated queries.
    2. AD_ - The college or department creating the query. In this example, the department is Undergraduate Admissions.
    3. UGRD_APPLS - Describes what the query does. In this example, it pulls undergraduate applications.

Link to PDF

Query Manager allows you to schedule an existing query to run at a specific day and time.

 

Creating the Schedule

1. Click the Schedule link in the Query grid for the query you wish to schedule.

2. Select your Run Control ID from the Search Results or create a new Run Control ID by entering the name in the Run Control ID field. Check with your supervisor to see if there are established  naming conventions for your office.

3. Click Add.

4. If your query contains one or more Run-Time Prompts, you must provide the value for that prompt field.

5. Click OK when complete.

6. Enter the Description and click the OK button to submit the query process request.

7. In the Process Scheduler Request page click the Recurrence drop-down menu and select the recurring time intervals for the process to run. For example, to run a process every weekday at 5 p.m., select the predefined recurrence definition M-F at 5pm.

8. Click the Type drop-down menu and select the type of report you wish to receive and the Format drop-down menu to select the format of the report.

9. Click the Distribution link to set up the report distribution and click OK once completed.

10. If you selected Email as your output type and XLS as the format, you will receive an email in your account with an Excel attachment.

 

Cancelling a Scheduled Query

1. Click the check box in the Select column for the query you wish to cancel.

2. Click the Details link for the selected query.

3. On the Process Detail page, click the Cancel Request radio button in the Update Process section and click OK.

4. On the new Process Detail page, the deleted query now shows the new Run Status.  Click Save to complete the process.

 

Screenshot of Process List

 

Actions that are available in the Update Process group box depend on your user authorizations and current status of the request. The option you select depends on the current run status of the process request. For instance, you cannot cancel a job that has already completed, and you cannot hold a request that is currently processing.

The valid actions based on the current status of each process request appear in the following table:

 

Valid Actions Table

Link to the PDF

The more you use Query Manager, the more you are likely to refine queries with conditions other than the default Equal To condition.

Between - The value in the selected record field falls between two comparison values. The range is inclusive.

Equal to - The value in the selected record field exactly matches the comparison value.

Exists - This operator is different from the others in that it does not compare a record field to the comparison value. The comparison value is a subquery. If the subquery returns any data, Query Manager returns the corresponding row.

Greater than - The value in the record field is greater than the comparison value.

In list - The value in the selected record field matches one of the comparison values in a list.

In tree - the value in the selected record field appears as a node in a tree created with PeopleSoft Tree Manager. The comparison value for this operator is a tree or branch of a tree that you want Query Manager to search.

Is null - The selected record field does not have a value in it. You do not specify a comparison value for this operator. Key fields, required fields, character fields and numeric fields do not allow null values.

Less than - The value in the record field is less than the comparison value.

Like - The value in the selected field matches a specified string pattern. The comparison value may be a string that contains wildcard characters. Query Manager recognizes wildcard characters Percent (%) and Underscore (_).

 Link to PDF.

The pages in Query Manager enable you to tailor queries to retrieve data that is specific to your business needs. The following lists the Tab Name and the Purpose of the Query Manager pages.

Records - Select the records/tables to use in the query. At least one record must be selected before a query can be created.

Query - Select the fields needed for the query. At least one record and field must be selected before a query can be created.

Expressions - Create formulas to use with the query.

Prompt - Create runtime prompts.

Fields - View, edit, sort and reorder fields that appear in the query.

Criteria - Filter data to retrieve only those rows that are needed.

Having - Create criteria for fields that use aggregate functions.

Dependency - Specify if a record can be pruned during the SQL pruning process in composite query.

Transformations - A way of extracting, transforming and distributing data without the need to access any client tools.

View SQL - View the SQL that is generated when the query is created.

Run - View the results of the query.

Link to PDF