Creating and Running Simple Queries

This chapter provides an overview of query types and Dirty Reads in PeopleSoft Query, lists common elements, and discusses how to:

Click to jump to parent topicUnderstanding Query Types

PeopleSoft Query provides the following different types of queries:

User query

User queries retrieve data from the database directly from Windows-based Query Designer or the web-based Query Manager/Query Viewer applications.

Note. Because of the range of possible circumstances in which you might run an ad hoc query, there are no special considerations or requirements that apply to all of them.

Reporting query

Reporting queries are essentially the same as user queries, except that they are designed to be used by another reporting tool. Reporting queries can be used as data sources for ad hoc queries, scheduled queries, Crystal Reports, PS/nVision, Cube Manager, or BI Publisher.

When you define a custom report, you often include runtime variables that users specify when they run the report. For example, you might want users to be able to say which business unit, location, or time period to report on. Therefore, your reporting query may include one or more runtime prompt variables.

If your query requires input parameters, you must decide how users should enter them. If they run the report from any of the PeopleSoft Query applications, they can enter values into the page that appears in Query Manager, Query Viewer, or Scheduled Query.

When reporting queries are used as a data source to another third party reporting product, you may need to:

  • Create or modify a page to collect the necessary input parameters.

  • Create or modify a record definition, based on the input parameters.

  • Add a process definition to PeopleSoft Process Scheduler.

See Defining Selection Criteria, Adding New Process Definitions.

Process query

Process queries are queries that you intend to run periodically using a batch process. Create these automated batch processes using PeopleSoft Application Engine and the Query API. For example, you could write a query that returns any overdue receivables and schedule a batch process to run the query once a week.

Note. Process and role queries override the automatic row-level query security logic that is applied to all other types of queries. For this reason, you should restrict access to creating these types of queries to administrative roles and not include any sensitive data columns in the select list for these types of queries. You can restrict access to creating/modifying these queries based on Query Profile settings assigned to a Permission List. Also note that Workflow queries also override the row-level security logic.

Role query

PeopleSoft Workflow uses role queries to determine to whom to send an email, form, or worklist entry. A role query needs to return one or more role IDs based on the data that has been saved on the page that is triggering the routing.

Because a role query returns a list of role users, the record definition that you want is either PSROLEUSER (which lists role users and the roles to which they are assigned) or ROLEXLATOPR (which lists role users and their IDs).

The only field that you select in your query is ROLEUSER. Of course, you use other fields and join to other record definitions to specify the criteria that role users can select. But no matter how complex the query is—how many joins or selection criteria it has—it must return ROLEUSER and nothing more.

Define a role as a query because you want to route items differently based on the context of the transaction that the users are performing. Thus, every role query contains at least one bind variable whose value gets set at runtime. The bind variable or variables correspond to the data on which you want to base the routing decision. At runtime, the system sets the values of the bind variables based on data from the page that triggers the event.

Save your role queries with names that begin with [ROLE] so that you can identify them as role queries.

Note. Process and role queries override the automatic row-level query security logic that is applied to all other types of queries. For this reason, you should restrict access to creating these types of queries to administrative roles and not include any sensitive data columns in the select list for these types of queries. You can restrict access to creating/modifying these queries based on Query Profile settings that are assigned to a Permission List. Note that Workflow queries also override the row-level security logic.

See Understanding PeopleSoft Workflow.

Archive query

You can save a query as an archive query if you have access to workflow queries that include Archive Query, Role Query, and Process Query. These queries are generally only used by the PeopleSoft Data Archive Manager.

See Using PeopleSoft Data Archive Manager.

Note. You can only create and save archive queries as public.

PS/nVision query

Use PeopleSoft Query to create a query in order to specify the data source for a PS/nVision report layout. However, if you want to use your query in a PS/nVision matrix layout, you must apply aggregate functions to at least one column.

Queries used with PS/nVision tabular layouts do not have the same restrictions as matrix layout queries; they are like other reporting queries and do not require an aggregate column.

See Working with Advanced Query Options, Understanding Layouts.

Click to jump to parent topicUnderstanding Dirty Reads in PeopleSoft Query

Dirty Reads mode—also called Read Only mode—enables you to configure your servers to avoid table locking while running PeopleSoft queries. When Dirty Read mode is enabled for PeopleSoft Query, the performance is increased as queries do not lock on other INSERT and UPDATE operations.

Dirty Read mode is set in configuration files for PeopleSoft Application server and PeopleSoft Process Scheduler server:

See Also

Application Engine

Use Dirty-Read

Setting Parameters for the Application Engine Server

Click to jump to parent topicCommon Elements in This Chapter

Add Record

Click this link to access the Query page, where you can add fields to the query content or add additional records.

Show Fields

Click this link to display the fields included in the record.

Col (column)

Displays the current column number for each field listed.

Query Name

New Unsaved Query appears in this read-only field until you change it on the Properties page. This field appears on all of the Create New Query pages.

Record.Fieldname

Displays the record alias and name for each field listed.

Indicates key fields.

Click the Delete button to delete the row.

A confirmation message appears. Click the Yes button to proceed with the deletion. Click the No button to cancel the deletion.

Click the Use as Criteria or Add Criteria button to open the Edit Criteria Properties page, where you can determine how this field will be used as a criterion for the current query.

From the Query tab, click the Folder button to view the fields for the chosen record, if they are not already displayed. Query Manager expands the record so that you can see the fields and make sure that this record has the content that you want.

Click the Folder button again to hide the fields for a record.

A key is displayed to the left of key fields.

Hover over to view the feeds of this query.

See Creating and Using Query Feeds.

Save

Click to save a query at any time after you have selected one record and at least one field for it.

See Saving Queries, Updating Existing Queries.

Save As

Click to access the Query Properties page where you can enter basic information about the query and save it.

See Saving Queries, Updating Existing Queries.

New Query

Click to access the PeopleSoft Query Manager where you can start creating a new query.

See Creating New Queries.

Preference

Click to access the Preference page where you can specify query preferences.

See Specifying Query Preferences.

Properties

Click to access the Query Properties page where you can view and edit data about the current query, such as the query name and description. You can also record information about your query so that you can use it again in the future.

See Viewing and Editing Query Properties.

Public As Feed

This link is available only when query was not published as feed.

Click to access the PSQuery Data Type - Publish Feed Definition page where you can define feed properties such as the feed title, security, and other options.

See Creating and Using Query Feeds.

Manage Feed

This link is available only when query was published as feed.

Click to access the PSQuery Data Type - Publish as Feed page where you can define feed properties such as the feed title, security, and other options.

See Creating and Using Query Feeds.

New Union

This link is available only when query does not have union.

Click to create an union of multiple queries.

See Working with Unions.

Delete Union

This link is available only when query have an union attached.

Click to delete an existing union of multiple queries.

See Working with Unions.

Return to Search

Click to return to the Query Manager search page where you can create new queries, modify existing queries, schedule queries, and organize queries.

See Modifying, Scheduling, and Organizing Queries.

Click to jump to parent topicCreating New Queries

This section discusses how to:

Click to jump to top of pageClick to jump to parent topicPages Used to Create a Query

Page Name

Definition Name

Navigation

Usage

Query Preferences

QRY_PREFERENCES

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

Click the Preferences link on any page of the Query Manager component except the Run page.

Specify query preferences.

Records

QRY_RECORDS

Reporting Tools, Query, Query Manager.

Click the Create New Query link.

Click the Search button to find an existing record.

Select the records upon which to base the new query.

Query

QRY_QUERY

Reporting Tools, Query, Query Manager.

Click the Create New Query link.

Click the Search button to find an existing record.

Click the Add Record link for the desired record.

Add fields to the query content. You can also add additional records by performing joins.

When you first access this page, if you have selected the record for an effective-dated table, a page informs you that an effective date criteria has been automatically added for this record. Click the OK button to close the page.

Fields

QRY_FIELDS

Reporting Tools, Query, Query Manager.

Click the Create New Query link.

Click the Search button to find an existing record.

Click the Add Record link for the desired record.

Select the desired fields, and select the Fields tab.

View how fields are selected for output; view the properties of each field; and change headings, order-by numbers, and aggregate values.

Edit Field Ordering

QRY_FIELDS_ORDER

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

From the Fields page, click the Reorder/Sort button.

Use to change the column and sort order for multiple fields.

Edit Field Properties

QRY_FIELDS_SEC

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

From the Fields page, click the Edit button to the right of the desired field.

Use to format the query output; for example, to change column headings or display translate table values in place of codes.

Query Properties

QRY_PROPERTIES

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

With a query open, click the Properties link at the bottom of the page (this link appears on all pages but the Run page).

View and edit data about the current query, such as the query name and description.

Also use to record information about your query so that you can use it again in the future.

View SQL

QRY_SQL

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

With a query opens, select the View SQL tab.

View the underlying SQL code that Query Manager generates based on your query definition.

You cannot modify SQL on this page.

Run

QUERY_VIEWER

Reporting Tools, Query, Query Manager.

Create a new query or search for an existing one.

With a query opens, select the Run tab.

View the results of your query prior to saving in order to verify that your query yields the results that you need. Continue to adjust and preview your query as necessary.

Click to jump to top of pageClick to jump to parent topicSpecifying Query Preferences

To specify query preferences:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. Access the Query Preferences page by clicking the Preferences link on any page of the Query Manager component (except the Run page).

This is an example of the Query Preferences page:

Name Style

Specify how record and field names are displayed. Select one of the following:

  • Description only

  • Name and Description

This is used mostly for global users where the record and field names are in English but the descriptions are in another language.

Enable Auto Join

Select to indicate that your query should automatically determine the join conditions when a new record component is added.

Enable Auto Preview

Select to indicate that your query preview should automatically rerun each time you access the Run page of the Query Manager component.

This option is selected by default.

See Also

Selecting Records

Adding Fields to Query Content

Previewing Query Results Prior to Saving

Understanding Internally Controlled Options

Click to jump to top of pageClick to jump to parent topicSelecting Records

Use these steps to access the Records page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Click the Create New Query link.

  3. Click the Search button.

    The Records page appears.

This is an example of the Records page:

Note. In your PeopleSoft database, tables are represented as record definitions. In PeopleSoft Query, we refer to the record definitions as records.

To select a record in the Records page:

  1. Find the record upon which to base the query.

  2. If you want to view a record's fields, click the Show Fields link next to the record in question.

    A page displays the record's fields. You can use this information to verify whether you want to base the query upon this record.

  3. After viewing the fields, click the Return button to return to the Records page.

  4. Click the Add Record link next to the record that you want to add to the query.

    The Query page appears enabling you to select the fields from the selected record to add to the query.

Note. In most cases, you select only one base record from this page. If you navigate back and select a second base record, you are creating an any join for the two records.

See Also

Creating Any Record Joins

Click to jump to top of pageClick to jump to parent topicAdding Fields to Query Content

Use these steps to access the Query page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Click the Create New Query link.

  3. Click the Search button to find an existing record.

  4. Click the Add Record link for the desired record.

    The Query page appears.

This is an example of the Query page:

Click the Sort button once to list fields in alphabetical order. Click the button again to return to the original sort.

Alias

The alias name that the system automatically assigns to the chosen records.

Hierarchy Join

Click this link to join a child table to its parent table.

Check All Fields

Click this button to check all fields in the record. After you select a field, the system automatically adds it to the query and you can view it on the Fields page.

This button does not appear when the field names are hidden.

Uncheck All Fields

Click this button to clear all fields in the record.

Fields

Select the check box to the left of each field that you want to add to your query content.

Related Record Join

Click such links to join two records that are based on a shared field.

For example, in the above example, the QE_DEPT_TBL record is related to the QE_EMPLOYEE record by the DEPTID field.

Expand All Records

Click this button to view all fields in the records

This button appears only when there is more than one record listed.

Collapse All Records

Click this button to hide all fields in the records.

This button appears only when there is more than one record listed.

In the Query page, to add fields to your query:

  1. Select the fields to add to the query either by clicking their Fields check boxes or by clicking the Check All Fields button.

  2. When you have selected the desired fields, select the Fields tab.

See Also

Joining Records

Click to jump to top of pageClick to jump to parent topicViewing Fields Selected for Output

Use these steps to access the Fields page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Click the Create New Query link.

  3. Click the Search button to find an existing record.

  4. Click the Add Record link for the desired record.

  5. Select the desired fields, and then select the Fields tab.

    The Fields page appears.

This is an example of the Fields page:

Format

Field type and length for each field listed.

Ord (order)

Shows one or more fields selected to sort your query output.

If the field is the first sort field, a 1 appears, and the system sorts rows that are based on this field first. The second sort field that is selected is numbered 2, and so on.

You can also specify a descending sort order. The letter D appears if you are sorting fields in descending order.

XLAT (translate)

Specifies the translate value that you want to appear in the query results: N (none), S (short), or L (long).

The table you’re querying may include fields that use the Translate table. If so, the field itself contains a short code of some kind, for which the Translate table provides a set of corresponding values. For example, if the table includes an EFF_STATUS field, the value is A or I, which the Translate table translates into Active and Inactive. If a field has values on the Translate table, a letter appears in the XLAT column for that field.

In your query results, you might want to display the translated value rather than the code (for example, Active instead of A). To instruct PeopleSoft Query to make this substitution, specify L as the translate value.

Translate tables are effective-dated, so you must select which effective date to use for them. For most tables, PeopleSoft Query defaults to the current date, meaning that it uses the currently active list of Translate table values. However, if the table you’re querying is also effective-dated, PeopleSoft Query uses the value in the EFFDT field for a row. That is, for each row the query returns, PeopleSoft Query uses the Translate table values that were active as of that row’s effective date.

If neither of these effective date options are what you want, you have two more options:

  • If the table you’re querying includes another date field, you can use the value in that field as the effective date for Translate table values.

    Click the Edit button, select the Field option, and then select the field name from the drop-down list box.

  • Use an expression to set the effective date for the Translate table.

    For example, enter a fixed effective date or prompt the user for a fixed effective date.

Agg (aggregate)

Aggregate function for each field listed.

Heading Text

The heading assigned to appear at the top of the column for the query output for each field listed.

Edit

Click this button to display the Edit Field Properties page.

See Viewing and Editing Query Properties.

Reorder/Sort

Click this button to display the Edit Field Ordering page, which enables you to change the column order and/or sort order for multiple fields.

Click to jump to top of pageClick to jump to parent topicChanging the Column and Sort Order for Multiple Fields

Use these steps to access the Edit Field Ordering page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. Select the Fields tab, and click the Reorder/Sort button.

    The Edit Field Ordering page appears.

This is an example of the Edit Field Ordering page:

New Column

Enter the new column number to reorder the columns. Columns that are left blank or assigned a zero are automatically assigned a number.

Order By

Current sort order.

Descending

Select this check box to sort fields in descending order.

New Order By

Enter the new sort order number to change the sort order. Enter zero to remove a sort order. If the field is the first sort field, enter 1, and the system sorts rows based on this field first. To designate the second sort field, enter 2, and so on.

Click to jump to top of pageClick to jump to parent topicEditing Field Properties

Use these steps to access the Edit Field Properties page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. Select the Fields tab, and then click an appropriate Edit button.

    The Edit Field Properties page appears.

This is an example of the Edit Field Properties page:

Field Name

The name of the field for which you are editing properties.

Column Number

By default, the fields appear in the same order as they are listed in the record on the Query page. To change the order for the selected field, enter the new column number. When you close the Field Properties page, the columns reorder.

Heading

Select a column heading from the following options:

  • No Heading: The column does not have a heading.

  • Text: The column heading is the text that you have entered in the text box.

  • RFT Short: The column heading is the short name from the record definition.

  • RFT Long: The column heading is the long name from the record definition.

Unique Field Name

Used for translations. There is no need to change the default value, which is a single-letter alias for the record followed by the record field name (for example, A.NAME or B.EMPLID).

Aggregate

If you are using aggregate values, select the aggregate function value for this field.

An aggregate function is a special type of operator that returns a single value based on multiple rows of data. When your query includes one or more aggregate functions, PeopleSoft Query collects related rows and displays a single row that summarizes their contents.

None

Will not use aggregate functions.

Sum

Adds the values from each row and displays the total.

Count

Counts the number of rows.

Min (minimum)

Checks the value from each row and returns the lowest one.

Max (maximum)

Checks the value from each row and returns the highest one.

Average

Adds the values from each row and divides the result by the number of rows.

See Also

Working with Aggregate Functions

Click to jump to top of pageClick to jump to parent topicViewing and Editing Query Properties

Use these steps to access the Query Properties page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. Click the Properties link on any page of the Query Manager component except the Run page.

    The Query Properties page appears.

This is an example of the Query Properties page:

Query

Use this field to edit the name of the query.

Description

Edit the description of the query.

Folder

Enter the name of the folder in which you would like the query to reside.

To create a new folder, enter the name of the new folder.

Query Type

Select Archive, Process, Role or User.

Workflow queries are either Process or Role.

See Understanding Query Types.

Owner

Select from the following options:

  • Private: If you select this option, only the user ID that created the query can open, run, modify, or delete the query.

  • Public: If you select this option, any user with access to the records used by the query can run, modify, or delete the query (if he or she has access to public queries).

Distinct

Select this option to eliminate duplicate rows in a query result.

Some queries return the same row more than once because it satisfies the query in more than one case. For example, if you query the JOB record to return all JOBCODES, you receive multiple rows that look identical because some employees have the same JOBCODE. If you select the Distinct check box, you receive each JOBCODE once.

This option is not visible for union selections because unions are automatically distinct.

Note. In Oracle databases, PeopleSoft LongCharacter fields use the CLOB datatype when their length definition exceeds 1333. Since CLOBS and other binary datatypes are not valid columns for all operators, all queries including columns with LongCharacter field length definition that exceeds 1333 should not include the DISTINCT operator.

Security Join Optimizer

If the tables being joined in the current query are associated with the same query security record, use this option to determine whether the joins should be optimized.

  • Select this option to enable query to join once to the security record and use those results to filter all subsequent joins to that same security record.

  • Clear this option to enable query to join multiple times to the same security record.

Note. For left outer joins, PSQuery uses either ‘inner join’ or ‘in line view’ (in the case of Oracle (+) and Sybase (*) proprietary syntax) logic to join the individual tables to their security records first, before performing the left outer join. In these cases, the Security Join Optimizer option is not applied to ensure the query returns correct results.

Query Definition

Free text area that you can use to further describe your query.

See Also

Creating Workflow Queries

PeopleSoft Query Security

Click to jump to top of pageClick to jump to parent topicViewing Underlying SQL Code

Use these steps to access the View SQL page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. From the Fields page, select the SQL tab.

    The View SQL page appears.

This is an example of the View SQL page:

Query SQL

Displays the underlying SQL code that Query Manager generates based on your query definition. To copy the SQL statement, highlight the text of the statement and copy it using your browser’s copy command. Paste it into another application, if desired.

Click to jump to top of pageClick to jump to parent topicPreviewing Query Results Prior to Saving

Use these steps to access the Run page:

  1. Select Reporting Tools, Query, Query Manager.

  2. Create a new query or search for an existing one.

  3. From the Fields page, select the Run tab.

    The Run page appears.

This is an example of the Run page:

Note. Because PeopleSoft Query 8.52 partly supports iOS (Apple's mobile operating system), iPad users can view query results or rerun query from the Run page, but they cannot download query results using the Download to Excel or Download to XML link.

View All

Click this link to view all rows and use scroll bar to navigate.

Rerun Query

Click this link to rerun your query preview. If you have made changes to your query since the last preview, you must rerun the query to see the effect of your changes.

Note. If you do not want to rerun the query every time you want to preview it, deactivate the Enable Auto Preview feature. When this feature is active, your query automatically runs each time you select the Run tab. To activate Enable Auto Preview, click the Preferences link and then select Enable Auto Preview. (The Preferences link is available on every page except the Run page.)

See Specifying Query Preferences.

Download to Excel

Click this link to download your query to Microsoft Excel.

Note. To grant users access to download your query to Microsoft Excel, you must make sure that their permission lists include the WEBLIB_QUERY web library with full access.

Download to XML

Click this link to download your query result as XML format to another browser window.

Note. To grant users access to download your query to XML, you must make sure that their permission lists include the WEBLIB_QUERY web library with full access.

Query Results in Double-Byte Characters

In the Run page, the width of rows are automatically adjusted to show the query results. If the query results are in double-byte characters (for example, Japanese), rows may wrap vertically.

To view the row horizontal with no line break:

  1. In PeopleSoft Application Designer, open the PTQUERYSTYLESUB definition.

  2. Open the PSQRYRESULTSHDR style class properties.

  3. In the Spacing/Alignment tab, select the Nowrap option from the White Space drop-down list box.

See Setting Style Class Attributes, Specifying Spacing and Alignment.

Click to jump to parent topicSaving Queries

You can save a query at any time after you have selected one record and at least one field for it. Save queries from any Query Manager page (except for the Run page) by clicking either the Save button or the Save As link. You must enter some basic information about the query before the system allows you to save it for the first time.

This example shows the information that you must define when you save your query for the first time:

To save a query for the first time:

  1. After you make your changes in Query Manager (Reporting Tools, Query, Query Manager), click the Save button.

  2. In the Query field, enter a short name for the query.

  3. In the Description field, enter an appropriate description for the query.

    This information will help you identify the query later.

  4. In the Folder field, enter the name of the folder into which you would like to save the query.

    To create a new folder, enter the name of the new folder.

    Note. The folder that you create does not reside on your local drive. You create a folder for the purpose of describing the query and to facilitate grouping and sorting.

  5. Select a query type.

    Standard queries are designated as User queries. If you have access to workflow queries, your list of types will also expand to include role, process, and archive.

  6. In the Owner field, select whether your query is Public or Private.

  7. Enter the query definition.

  8. Click the OK button to save the query.

Note. Using the Save As link creates another instance of the query that you can modify and save under a different name. When you click the Save As link, the page you just saw appears enabling you to change the name, description, and owner of the new query.

Click to jump to parent topicRunning Queries

You can run a predefined query from your browser and view it online. When you click the Run button on the Query Manager search page, PeopleSoft Query displays the results in a new browser window. This Run option is useful if you want to run multiple queries or run the same query multiple times with different runtime prompt values and compare the results of the queries.

If you want to run queries that you haven't saved, you can use the Run page in the Query Manager.

To run a query:

  1. Select Reporting Tools, Query, Query Manager.

    The Query Manager Search page appears.

  2. Search for a query using the basic or advanced search function.

    Both search types enable you to use 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 allows you to search using the begins with condition. The advanced search enables you to perform a progressively narrower search by using one or more search by criteria and selecting from a broad choice of conditions. These are instructions on using search by criteria:

  3. To perform an advanced search, click the Advanced Search link on the Query Manager Search page.

    On the Advanced Search page, select the appropriate search by criteria and conditions, and then enter a search string in each of the corresponding fields.

  4. Click the Search button to display a list of queries that match your search criteria.

    The Search Results page appears. The results list all the queries that match the search criteria. The following information appears:

  5. Scroll to the name of the query that you want to run.

    By default, only the first 30 queries appear on the page. To see more of the list, click the navigation buttons and links located on the header bar.

    To display 100 of the queries, select View 100 and use the scroll-bar to view the remainder of the list.

  6. To run a query, use the following links on the row of the query:

Note. If a Query—that is used as a data source for BI Publisher— is run through Reporting Tools, Query, Query Manager, the BI Publisher-related prompts do not appear. The normal basic table-formatted query results are generated.

See Scheduling Queries.

See Also

Running BI Publisher PeopleSoft Query Reports

Security Basics

Click to jump to parent topicAdding Queries to the Internet Explorer Favorites List

To add a query to the Favorites list on the Internet Explorer menu:

  1. Select Reporting Tools, Query, Query Manager.

    Alternatively, select Reporting Tools, Query, Query Viewer.

  2. Click Search, and then right-click the HTML link on the Query Manager or Query Viewer search page.

  3. Click the Add to Favorites option on the Internet Explorer menu.

    The Add Favorite page appears.

  4. Change the text in the Name box to the name of your query.

  5. Click the OK button.

    Internet Explorer adds the query to your Internet Explorer Favorites list.

Click to jump to parent topicDownloading Queries

You can download your query to a Microsoft Excel spreadsheet or CSV text file.

After you have downloaded the query to Microsoft Excel, the first row in the spreadsheet displays the total number of rows of your query that are included in the spreadsheet.

Note. Because PeopleSoft Query 8.52 partly supports iOS (Apple's mobile operating system), iPad users can edit or schedule queries from the Query Manager page or Query Viewer page, but they cannot download query results using the HTML, Excel, or XML link.

A Microsoft Excel spreadsheet can contain a maximum number of 65,536 rows. If the spreadsheet cannot include all of the rows in your query, the first row displays—in a red font—the total number of rows returned by the query. If this number is greater than 65,536, Microsoft Excel spreadsheet does not include the remaining rows.

If you download your query from the Run page, the query has a different default filename than if you download your query after clicking the HTML or Excel links. These default filenames are different because: (a) using the Run page to run queries, queries are run using the application server, and (b) using the HTML or Excel links, queries are run using a query service.

To download your query results, select one of the following options:

To modify the File Type Option settings for Microsoft Excel Worksheets when using Microsoft Windows 2000:

  1. Open Windows Explorer (right-click Start, Explore).

  2. Select Tools, Folder Options, File Types.

  3. Select the XLS extension (Microsoft Excel Worksheet).

  4. Click the Advanced button, and select or clear the Browse in same window check box.

Applying a Formula to a Newly Inserted Column

When working in Microsoft Excel on a query that you have downloaded, adhere to the following guidelines in order to insert a new column into the spreadsheet, and then add a formula to that column.

When you insert a new column in Microsoft Excel, the new column's cells assume the default format from one of the adjacent columns—typically the left column. For example, if you insert a column between columns B and C, the new column takes on the format of column B. If column B is formatted as Text, you cannot apply a formula to the newly inserted column.

Note. If the columns adjacent to the newly inserted column have a format of General, you should be able to apply a formula without performing the following procedure.

In Microsoft Excel, to apply a formula to a newly inserted column of a query that you have downloaded:

  1. Highlight the newly inserted column.

  2. Change the column's format from text to general by selecting Format, Cells, and then selecting General from the list of categories on the Number tab.

  3. Insert the formula into the column.

Click to jump to parent topicPerforming Lookups

Some queries are designed to prompt you for information when you run them. This approach narrows the query results to match the information that you entered. To help you enter the correct information, you can perform a search using the Look Up button that appears on the page.

For example, the ADDRESSLIST query prompts you for a specific employee ID. You might know an employee’s last name, but not have the employee ID handy. Using the Look Up button, you use the information that you do have to find the required information.

In this example, we perform a basic lookup using ADDRESSLIST:

ADDRESSLIST page

To perform a lookup:

  1. Select Reporting Tools, Query, Query Manager.

  2. Search for an existing one.

  3. Click the HTML link from the Query Manager search page for the ADDRESSLIST query name from the query list.

  4. If you know the employee ID for the address that you are looking up, enter it in the EmplID (employee ID) field; if you need to search for the employee ID, click the Look Up button.

  5. On the Look Up page, click the drop-down arrow to find more search values.

    In this example, you can select EmplID, Name, or Last Name. To find all values for this field, leave the search field blank and click the Look Up button. You can also display all of the search fields at once by clicking the Advanced Lookup link.

  6. Select the value to search by, and then click the Look Up button.

    In this example, we know the employee’s last name, but not his employee ID.

    Your lookup information appears on the page.

  7. Click the link in the result field.

    The Query page appears with the required value already complete.

  8. Click the View Results button.

    The query results appear on the page.

Click to jump to parent topicPrinting Query Results

Before you can print query results, you must run the query.

To print query results on your default printer:

  1. Select Reporting Tools, Query, Query Manager.

  2. Click the Search button, and then click either the HTML or Excel links.

  3. Click the Print button or select File, Print.

    If you download the query to Microsoft Excel, you can print the query using Microsoft Excel’s print function.

    If you download the query as a CSV text file, you can print it using the print functions of the applications you use to work with it.

Note. Because PeopleSoft Query 8.52 partly supports iOS (Apple's mobile operating system), iPad users can edit or schedule queries from the Query Manager page or Query Viewer page, but they cannot download query results using the HTML, Excel, or XML link.

To grant users access to download your query to Microsoft Excel, you must make sure that their permission lists include the WEBLIB_QUERY web library with full access.

See Setting Query Permissions.

Click to jump to parent topicUsing Query Viewer

Query Viewer is a read-only version of Query Manager. It enables security administrators to limit some users to read-only access for all queries, so they can only view or print queries.

This example shows the Query Viewer page:

Query Viewer enables you to:

Note. Because PeopleSoft Query 8.52 partly supports iOS (Apple's mobile operating system), iPad users can edit or schedule queries from the Query Manager page or Query Viewer page, but they cannot download query results using the HTML, Excel, or XML link.

Online viewing of Query-based BI Publisher reports is available by selecting Reporting Tools, BI Publisher, Query Report Viewer.

See Also

Previewing Query Results Prior to Saving

Running Queries

Scheduling Queries

Running Reports in Query Report Viewer