Enterprise PeopleTools 8.50 PeopleBook: PeopleSoft Query

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:

Create new queries.
Save queries.
Run queries.
Add queries to the Internet Explorer Favorites list.
Download queries.
Perform lookups.
Print query results.
Use Query Viewer.

Understanding 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 XML 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 Adding New Process Definitions. See Defining Selection Criteria.
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. See Understanding Layouts.

Understanding 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:

In PeopleSoft Application server, under section [PSQRYSRV], the setting ‘Use dirty-read’ controls behavior for PSAPPSRV, PSQCKSRV, and PSQRYSRV.
In PeopleSoft Process Scheduler server, under section [PSAESRV], the setting ‘scheduledquery-dirtyread’ controls behavior for scheduled query process executing under PSAESRV process.

See Also

Application Engine

Use Dirty-Read

Setting Parameters for the Application Engine Server

Common 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 /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.
Feed	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. See 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. See 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.

Creating New Queries

This section discusses how to:

Specify query preferences.
Select records.
Add fields to query content.
View fields selected for output.
Change the column and sort order for multiple fields.
Edit field properties.
View and edit query properties.
View underlying SQL code.
Preview query results prior to saving.

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

Specifying Query Preferences

To specify query preferences:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
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

Selecting Records

Use these steps to access the Records page:

Select Reporting Tools, Query, Query Manager.
Click the Create New Query link.
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:

Find the record upon which to base the query.
- If you know the entire record name, description, access group name, or field name included in the record, select the appropriate item in the Search By drop-down list box.
  
  Enter the name in the field (or click the Look Up button if searching by access group name). Then click the Search button to display a list of records that match your search criteria.
- You can perform a partial search by entering part of the name in the Search By field.
  
  Note. Enter as much of the name as possible to find the correct record.
- You can perform an advanced search by clicking the Advanced Search link.
  
  You can perform a progressively narrower search by selecting conditions for the record name, record description, or field name contained in the record, and then entering an appropriate search string in the fields that correspond to your selections. For access group name, select a condition, click the Lookup Name button, and then select the access group name from the list of access group trees that appears.
- If you want to view a list of available records, leave the field blank and click the Search button to display a list of up to 300 records.
  
  By default, only the first 20 records appear on the page. To see more of the list, use the navigation buttons and links located on the header bar. To display 100 of the records, select the View 100 link, and use the scroll-bar to go through the rest of the list.
If you want to view a record's fields, click Show Fields 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.
Click Return when you have finished looking at the fields.
Click Add Record next to the record you want to add to the query.

This takes you to the Query field, from which you can select which 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

Adding Fields to Query Content

Use these steps to access the Query page:

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

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:

Select the fields to add to the query either by clicking their Fields check boxes or by clicking the Check All Fields button.
When you have selected the desired fields, select the Fields tab.

See Also

Joining Records

Viewing Fields Selected for Output

Use these steps to access the Fields page:

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

Changing the Column and Sort Order for Multiple Fields

Use these steps to access the Edit Field Ordering page:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
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.

Editing Field Properties

Use these steps to access the Edit Field Properties page:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
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

Viewing and Editing Query Properties

Use these steps to access the Query Properties page:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
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.

Security Join Optimizer

If this query contains multiple joins to the same query security record, define whether it should be run optimized:

Select to enable this query to join once to the first security record.

If this is a non-self join (same security tables) or a self join, the query joins security table once.

If this is a non-self join with different security tables, the query joins security table multiple times using the Inner Join logic and the Security Join Optimizer option is not used.

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

If this is a non-self join with same security tables or a self join, the query joins security table multiple times using the Inner Join logic.

If this is a non-self join with different security tables, the query joins security table multiple times using the Inner Join logic and the Security Join Optimizer option is not used.

Note. The concepts of Security Join Optimizer are also applied when you perform Left Outer Joins with security records.

Query Definition

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

See Also

Creating Workflow Queries

PeopleSoft Query Security

Viewing Underlying SQL Code

Use these steps to access the View SQL page:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
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.

Previewing Query Results Prior to Saving

Use these steps to access the Run page:

Select Reporting Tools, Query, Query Manager.
Create a new query or search for an existing one.
From the Fields page, select the Run tab.

The Run page appears.

This is an example of the Run page:

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.

Saving 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:

After you make your changes in Query Manager (Reporting Tools, Query, Query Manager), click the Save button.
In the Query field, enter a short name for the query.
In the Description field, enter an appropriate description for the query.

This information will help you identify the query later.
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.
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.
In the Owner field, select whether your query is Public or Private.
- Private: Only the user ID that created the query can open, run, modify, or delete the query.
- Public: Any user with access to the records used by the query can run, modify, or delete the query.
Enter the query definition.
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.

Running 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:

Select Reporting Tools, Query, Query Manager.

The Query Manager Search page appears.
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:
- If you know the entire name of the query that you want to run, select Query Name from the Search By drop-down list box, and then enter the query name in the Search By field.
  
  If you do not know the name of the query and want to search through a list of queries, leave the Search By field blank and click the Search button to display a list of up to 300 queries.
  
  Perform a partial search by entering part of the search string in the Search By field.
- To search using any other search by criteria, select the appropriate item from the Search By drop-down list box, and then enter the search string in the field.
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.
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:
- Query name.
- Query description.
- Owner (public or private).
- Folder.
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.
To run a query, use the following links on the row of the query:
- HTML: Click to generate an HTML version of the query.
- Excel: Click to download the query to a Microsoft Excel spreadsheet.
- Schedule: Click to schedule a time for the query to run.
- XML: Click to download query result to browser as webrowset format. There are options that enable you to open, save, or cancel the downloaded file.

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

See Scheduling Queries.

See Running XML Publisher PeopleSoft Query Reports.

See Also

Security Basics

Adding Queries to the Internet Explorer Favorites List

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

Select Reporting Tools, Query, Query Manager.

Alternatively, select Reporting Tools, Query, Query Viewer.
Click Search, and then right-click the HTML link on the Query Manager or Query Viewer search page.
Click the Add to Favorites option on the Internet Explorer menu.

The Add Favorite page appears.
Change the text in the Name box to the name of your query.
Click the OK button.

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

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

HTML: Click this link on the Query Manager or Query Viewer search results page.
Excel: Click this link on the Query Manager or Query Viewer search results page.

After you click the Excel link, there are options that enable you to save, open, or cancel downloading query results to Microsoft Excel.

Note. If you have Office 2007 installed, then when doing a download-to-Excel, you might encounter a Microsoft Office Excel warning message suggesting that you verify that the file is not corrupted and that it is from a trusted source before opening it. You should click the Yes button in this situation.

The CacheBaseDir= setting in the psappsrv.cfg file specifies the location of temp files that are created by the Query to Excel execution. Those temp files are removed automatically after the run. If the CacheBaseDir is not set in the psappsrv.cfg file, all temp files are located under %PS_SERVDIR%.

See Enabling the Query Access List Cache.

See CacheBaseDir.
XML: Click this link on the Query Manager or Query Viewer search results page.

After you click the XML link, query result will be downloaded to browser as XMLP format and there are options that enable you to open, save, or cancel the downloaded file. If you click the Open button, XML formatted query result is downloaded to browser. You can also select the XML option as the format of your query results in the Schedule Query page when schedule to run a query.
Download to Excel: Click this link on the Query Manager or Query Viewer Run page.
Excel SpreadSheet: This option is available after you have clicked the HTML link on the Query Manager or Query Viewer search results page. However, you can also click the Download to Excel or Excel links without downloading the query to HTML.

Microsoft Excel 2000 or later is preferred. If you’re using Microsoft Excel 97, click the CSV Text File link when you need to download large result sets.

You can configure your environment to open the Microsoft Excel file in a separate window or save it as a file on your local hard drive by modifying the File Type Option settings for Microsoft Excel Worksheets.

Note. Downloaded data such as numbers and dates are formatted in the Microsoft Excel spreadsheet according to the regional settings on the user's machine.

If you use the Windows or Macintosh operating systems, you can set your downloaded queries to open in browser windows instead of in the Microsoft Excel application. To set this option in Windows, select the XLS file type in your folder options and select the Browse in same window check box. When this check box is cleared, downloaded queries open in the Microsoft Excel application.
CSV Text File: This option is available after you have clicked the HTML link. If you click this option, the File Download page appears, at which point you can open the file in your browser or save it to disk.

Note. The output has no formatting and does not support UTF-8 encoded data.

Because Microsoft Excel does not support UTF-8 encoding, the CSV file is written in binary mode with UCS-2 encoded data. Moreover, Excel does not automatically recognize Unicode-encoded, comma-delimited files even if they have a .csv extension. Therefore, the user receiving the file will not be able to open it by double-clicking. Instead, he or she must open it with Excel’s File, Open menu and choose the comma delimiter.
XML File: This option is available after you have click the HTML link. If you click this option, the File Download page appears, at which point you can open the XML formatted query result in your browser or save it to your local machine.

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

Open Windows Explorer (right-click Start, Explore).
Select Tools, Folder Options, File Types.
Select the XLS extension (Microsoft Excel Worksheet).
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:

Highlight the newly inserted column.
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.
Insert the formula into the column.

Performing 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:

To perform a lookup:

Select Reporting Tools, Query, Query Manager.
Search for an existing one.
Click the HTML link from the Query Manager search page for the ADDRESSLIST query name from the query list.
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.
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.
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.
Click the link in the result field.

The Query page appears with the required value already complete.
Click the View Results button.

The query results appear on the page.

Printing Query Results

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

To print query results on your default printer:

Select Reporting Tools, Query, Query Manager.
Click the Search button, and then click either the HTML or Excel links.
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. 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.

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

Query Viewer enables you to:

Search for a query using the basic or advanced search functions.

Both search types allow you to select 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 enables you to search using only 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 choice of conditions for each search by criterion.
Run a query (which displays results in a new browser window).

To run a query, click the HTML or Excel button to the right of the query on the Search Results page.

Note. After you click the HTML link, you have an option to view the XMLP formatted result in a new browser by clicking the Download to XML File link on the HTML result page.
View a query in XMLP formatted result by clicking the XML link.
Download the results to a Microsoft Excel spreadsheet or a CSV Text file.

To download the results, first click the HTML link associated with the query on the Search Results page. Then, to download the results to a CSV text file, click the CSV Text File link on the HTML results of the query.

To download the results to a Microsoft Excel file, click the Excel Spreadsheet link on the HTML results page.
Print a query.

See Printing Query Results.
Schedule a query.

From the Search results page, click the Schedule link associated with the query. Query Viewer interacts with PeopleSoft Process Scheduler to enable you to schedule queries. You can submit requests to schedule a query, check the status of your request using Process Monitor, and view your output using Report Manager.

See Using Process Monitor.

See Using Report Manager.

Note. Online viewing of Query-based XML Publisher reports is available by selecting Reporting Tools, XML Publisher, View Query Report.

Running Queries

Scheduling Queries

Running Reports in Query Report Viewer

	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.

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.

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.

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.

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.
Security Join Optimizer	If this query contains multiple joins to the same query security record, define whether it should be run optimized: Select to enable this query to join once to the first security record. If this is a non-self join (same security tables) or a self join, the query joins security table once. If this is a non-self join with different security tables, the query joins security table multiple times using the Inner Join logic and the Security Join Optimizer option is not used. Clear to enable this query to join multiple times to the security record. If this is a non-self join with same security tables or a self join, the query joins security table multiple times using the Inner Join logic. If this is a non-self join with different security tables, the query joins security table multiple times using the Inner Join logic and the Security Join Optimizer option is not used. Note. The concepts of Security Join Optimizer are also applied when you perform Left Outer Joins with security records.
Query Definition	Free text area that you can use to further describe your query.

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.