Defining Selection Criteria

You define selection criteria to selectively retrieve the data that you want. Selection criteria refine your query by specifying conditions that the retrieved data must meet. For example, you can specify that the system retrieve only those phone numbers with a certain area code instead of all phone numbers.

This chapter discusses how to:

• Choose selection criteria.

• Define criteria.

• Define prompts.

• Define HAVING criteria.

• Define expressions.

• Drill URL in Oracle PeopleSoft Query.

Choosing Selection Criteria

Because your PeopleSoft database stores data in tables, you can identify every individual piece of data by saying what column (field) and row (record) it is in. When you create a query, select the data that you want by specifying which columns and rows you want the system to retrieve.

If you run the query after selecting the fields, the system retrieves all the data in those columns; that is, it retrieves the data from every row in the table or tables. This might be much more data than you want or need. You select which rows of data you want by adding selection criteria to the query.

The selection criteria serves as a test that the system applies to each row of data in the tables that you are querying. If the row passes the test, the system retrieves it; if the row does not pass, the system does not retrieve it. For example, suppose that you needed the names of all PeopleStore customers who were not PeopleSoft employees. You would start by creating a query that retrieved the Name and Company fields from the Customer table. You could then add a selection criterion that enables PeopleSoft Query to scan for rows where the company name is not PeopleSoft.

In most cases, a selection criterion compares the value in one of a row’s fields to a reference value. In the preceding example, you would compare the value in the Company field to the constant value PeopleSoft. In other situations, you might compare the value to the value in another record field or to a value that the user enters when running the query.

Defining Criteria

This section discusses how to:

• View and edit selection criteria.

• Edit criteria properties.

• Select condition types.

• Enter comparison values.

• Specify effective date criteria.

• Relate multiple criteria.

Pages Used to Define Criteria

Viewing and Editing Selection Criteria

Access the Criteria page by selecting Reporting Tools, Query, Query Manager, Criteria.

This is an example of the Criteria page:

Any rows after the first row must include either an AND or OR logical value in the Logical column to specify whether you want the rows to meet this criterion in addition to other criteria that you have defined or as an alternative criterion. The first criterion that you define does not have a value in this column. The default for subsequent criteria is AND.

To reorder the criteria for your query, click the Reorder Criteria button, enter the new positions for the criteria on the Edit Criteria Ordering page, and click OK. You can also click the Add Criteria button from this Criteria page to add additional criteria, and you can click the Group Criteria button to group your criteria logically.

Editing Criteria Properties

Use these steps to access the Edit Criteria Properties page:

1. Select Reporting Tools, Query, Query Manager.

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

3. Access the Criteria page, and click the Add Criteria button.

   Alternatively, access the Fields page and click the Use As Criteria link.

This is an example of the Edit Criteria Properties page:

Field

Select this option if you want to base the selection criterion on another field’s value, usually a field in another record component. To compare the values from fields in two records, you must join the record components. When you select this option, you must then select a condition type. See Selecting Condition Types. Select the appropriate comparison operator from the Condition Type drop-down list box.

Expression

Select this option if you want PeopleSoft Query to evaluate an expression that you enter before comparing the result to the value in the selected field. When you select this option and click the New Expression link to create a new expression, you must then select an expression type. If you are entering an aggregate value, select the Aggregate Function check box. You can also enter parameters for length and decimal positions. Also enter the expression in the text box. Query Manager inserts this expression into the SQL.

Creating Criteria Based on a Field

To create criteria based on a field:

1. Select Reporting Tools, Query, Query Manager.

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

3. Access the Criteria page, and click the Add Criteria button.

   Alternatively, access the Fields page and click the Use As Criteria link.

   If you accessed the page by clicking the Use As Criteria link on the Fields page, Query Manager displays the Edit Criteria Properties page with the selected field populated in the Expression 1 field.

4. In the Edit Criteria Properties page, specify the criteria for the field, and click the OK button to return to the Fields or Criteria page.

   You can also use a field from a record as criteria even if you have not chosen that field for query output.

Creating Criteria Not Based on a Field

To create criteria that is not based on a field:

1. Access the Edit Criteria Properties page by clicking the Add Criteria button on the Criteria page.

   The Edit Criteria Properties page appears enabling you to edit Expression 1 and Expression 2 fields.

2. In the Choose Expression 1 Type group box, select the Field or Expression option.

3. Edit the second (right-hand) Expression column to enter comparison values.

4. Save your query.

Selecting Condition Types

The condition type determines how Query Manager compares the values of the first (left-hand) expression to the second (right-hand) expression.

Query Manager also offers a not option that reverses the effect of each condition type. For example, not equal to returns all rows that equal to would not return.

Note. You should use the not version of an operator rather than the NOT operator on the entire criterion. When you use NOT, PeopleSoft Query cannot use SQL indexes to speed up the data search. When you use the not version of an operator, PeopleSoft Query can translate it into a SQL expression that enables it to use the indexes.

This table describes the available condition types:

Note. If you have selected the EFFDT field on an effective-dated table, PeopleSoft Query also offers special effective date operators.

Entering Comparison Values

This section provides an overview of comparison values and discusses how to:

• Select a constant value.

• Build a list of values.

• Add prompts to an expression list.

• Add comparison values to an expression list.

The procedure for entering comparison values differs depending on what kind of value you are entering. If you are comparing one field to another, select the second record field; if you are comparing the rows to a constant value, enter the constant.

The following table describes all the available value types, the pages that appear based on each comparison type, and the fields that you must complete in those pages.

Note. Not all value types are available for all operators. For example, when you select the exists operator, Subquery is the only available value type. After you select an operator, PeopleSoft Query displays only the value types that are available for that operator.

Selecting a Constant Value

When you select Constant as your comparison value, the Define Constant page appears. In the text box, enter the value to which you want to compare the first expression. To add a value by selecting it from a list, click the Look Up button to display the Select a Constant page.

Note. A list of constants is available only for fields that have translate values or an assigned prompt table.

This is an example of the Select A Constant page:

To select a constant:

1. Click the Look Up button on the Select A Constant page.

2. Select the value that you want from the result list by clicking its associated link.

   The Select A Constant page appears again with the selected value in the field. If you are working with a date field, you can select a date/constant from a calendar.

3. If more than one field exists on the Select A Constant page, you must repeat steps 1 and 2 for each field to further narrow your search for the constant.

4. Click the OK button.

   The Edit Criteria Properties page appears with the selected value in the Define Constant page.

Another example of the Select A Constant page:

If this example, to select a constant, click a Select Constant link and the Edit Criteria Properties page reappears with the selected value in the Define Constant section.

Building a List of Values

When you select In List as your comparison value, the Edit List page appears. Use the Edit List page to build a list of values for PeopleSoft Query to compare to the value from the first expression. (After you have created such a list, you can also use this page to select from the list.)

To add a comparison value to the list, click the Look Up button on the Edit List page.

This is an example of the Edit List page, which dynamically reflects which record is used:

List Members	Lists the values that have been selected using the Add Value button. Note. The grid, containing the selected value, appears when a value is selected. To delete a value, select the check box to the left of the appropriate List Members value, and click the Delete Checked Values button.
Value	To add a value, enter it into the Value text box and click the Add Value button. The value appears in the List Members grid. To select from a list of values, click the Search button to display the Select a Constant page. Click the Look Up button to display the Look Up page. Enter part of a value in the text box. The system automatically adds a wild card to the end of the entry, which enables you to do a partial search. For example, if you enter a value of 10, the system returns all values, (up to a total of 300), that begin with 10, such as 10, 100, and 10069. Click the Look Up button to display the list of values that corresponds to the search criteria. Select the desired value from the list by clicking its associated link. The Select a Constant page appears again. Click OK. The selected value appears in the List Members grid.
Add Prompt	Click to add one or more prompts to the list so that users can enter the comparison values when they run the query. Note. The prompts must be defined before you can select them. If no prompts have been defined, you will receive an error message.
OK	Click to accept the values that are listed in the List Members grid. You will return to the Edit Criteria Properties page, where the selected values are displayed in the Edit List page.
Cancel	Click to return to the Edit Criteria Properties page without saving selections.

Adding Prompts to an Expression List

You can add one or more prompts to the expression list so that users can enter comparison values when they run a query.

Note. You must have defined the prompts before you can add them to your expression list.

To add prompts to an expression list:

1. With the expression list open, click the Add Prompt link in the Edit List page to access the Select a Prompt page.

2. Click the required prompt.

   The prompt appears on the Edit List page.

   If you selected the in list operator, you may want to add more than one prompt so that your users can enter more than one value to search for.

3. To add another prompt, click the Add Prompt link again and select a different prompt.

   Because you already have a prompt in place, a different page appears showing the prompt that you have already added.

4. To add the next prompt, click the New button and complete the Run-time Prompt page.

   The settings for this second prompt are the same as those that are used with the first prompt. If you want a different label for this prompt, enter that label in the Heading Text text box.

   When you click the OK button, the second prompt appears in the Available Prompts list.

5. Highlight the second prompt, and click the Select button to add it to the list of comparison values.

   The prompt then appears in the Edit List page.

6. Repeat this process for each prompt that you want to add.

   When you finish adding prompts, click the OK button to close the Edit List page.

Adding Comparison Values to an Expression List

When you select the Tree option as the comparison value (available if you select the in tree or not in tree condition type), the Select Tree Node List page appears. Use this page to create a list of values for PeopleSoft Query to compare to the value from the first expression. Click the New Node List link to display the Select Tree page.

If the Selected Nodes List has been previously populated, you can either:

• Change the tree by clicking the New Nodes List link.

• Open the previously selected tree, bypassing the Tree Selection page, by clicking the Edit Node List link.

Click the name of the tree that you want to display the Display and Select TreeNodes page, which you can use to select which element of the tree PeopleSoft Query will check. If no nodes have been previously selected, the Selected Nodes List grid is collapsed. If you do not know the name of the tree, you can perform a search for the tree.

This is an example of the Select a Tree page:

This example shows how to select tree nodes in the Display and Select TreeNodes page:

To select tree nodes:

1. In the Display and Select TreeNodes page, highlight the desired tree node, and click the Add Node icon.

2. If you know the name of the node that you want, enter the name of the node in the Manual Selection list box.

   Alternatively, click the Look Up button to select the desired node from a list of available nodes. When you find the node that you’re looking for, click the Add to List button to add it to the list. The nodes that you enter or select from the list appear in the Selected Nodes list box.

3. Remove nodes from the list by clicking the Remove from List icon corresponding to the node to be deleted.

4. Display the selected tree branch by clicking the corresponding icon.

5. When you are done selecting nodes, click OK.

   The selected tree setID, tree name, effective date, and nodes appear in the Select Tree Node List page.

Defining a Criteria with Tree Prompt in PeopleSoft Query

In addition to the option of selecting the tree value at design time for a criteria, Tree Prompts option will allow the user an option to select the tree and node value at run time. This allows the user to reuse the same query to get various organizational reports.

Note. In both PIA Query Manager and Windows client [psqed.exe], while creating a query you have the option of choosing to enter the tree details either at the design time or at the runtime. There is a Tree Prompt Option (an expression type) and a Tree Option when you select the condition type as In Tree or Not In Tree. Based on your selection, the tree option is handled to execute the query accordingly in the specified modules.

When you select the Tree Prompt Option as the comparison value, all options to select tree values at design time are not available. However, you are able to select tree values as criteria prompts when you run query.

This is an example of the Edit Criteria Properties with the Tree Prompt Option is selected:

After the Tree Prompt Option is selected:

• The Criteria page has a same criteria prompt entry.

• The View SQL page displays the viewable SQL for the in tree prompt.

  Note. Since tree information at query design time is not known yet, the SQL in the View SQL page is different from the SQL that actually runs in the database. When query is finally executed, based on the user's selection of tree and nodes when prompted, the SQL is modified accordingly with tree info in order to fetch rows of data.

• When you access the Run page, you are prompted for selecting the tree for the specified field and its node list.

  Based on the tree node list that you selected, and after collecting the other prompt values, if any, the results are displayed on the Run page.

Note. Query that has a criteria with tree prompts cannot be run as part of any process, either via Process Scheduler or from command line. For example, a crystal process which might need a query that has tree prompt to be executed to fetch data is not allowed (either by scheduling a process in Process Scheduler or using psccrun.exe at the command line). Although, you can execute the query with tree prompts from schedule query.

Specifying Effective Date Criteria

Effective-dated tables have record definitions that include the Effective Date (EFFDT) field. This field, used throughout the PeopleSoft applications, provides a historical perspective enabling you to see how the data has changed over time. Whenever users add a row of data to the table, they specify the date on which that data becomes effective; whenever users change a row of data, they specify a new effective date and the system retains the previous version of the row as history.

When you use a PeopleSoft application for day-to-day processing, you usually want the system to give you the currently effective rows of data—the rows where the effective date is less than or equal to today’s date. You do not want to see the history rows, which are no longer accurate, nor do you want to see future-dated rows, which are not yet in effect.

When you query an effective-dated table, however, you may want to see some rows that are not currently in effect. You might want to see all the rows, regardless of their effective dates. Or you might want to see the rows that were effective as of some date in the past.

To specify effective date criteria:

1. When you choose the record that has EFFDT as a key field, Query Manager automatically creates default criteria and adds that criteria to the Criteria page.

   This criteria is used to specify which row of data PeopleSoft Query retrieves for each item in the table. The default is the currently effective row. Defaults are:

   Expression 1	Record Alias.EFFDT
   Condition Type	EffDt <=
   Expression 2	Current Date
   Effective Sequence	Last

2. If you select one of the comparison options, choose to compare each row’s effective date against today’s date or a date other than today.

   • Select Current Date to compare each row’s effective date against today’s date.

   • Select Constant to display the Define Constant box so that you can enter a date.

     Select this option when you want to see the rows that were effective as of a past date or that will be effective on some future date.

   • Select Expression to display the Define Expression page so that you can enter a SQL expression that evaluates to a date.

     Select this option if you want to prompt users for an effective date when they run the query. You can add a prompt to the expression that you define in the Define Expression page.

   • Select Field to display the Select Field box so that you can select the record field that holds the date to which you want to compare effective dates.

     Select this option when you want to see the rows that were effective at the same time as some other record. For example, if you’re reviewing the list of products on a customer order, you will want to see the products that were effective on the date of the order.

   • Select First Effective Date to return the row with the oldest effective date, usually the first row that is entered for an item.

   • Select Last Effective Date to return the row with the latest effective date, even if that date is still in the future.

   • Removing the Effective Date criterion is equivalent to selecting No Effective Date. The query returns all rows, regardless of their effective dates.

Note. All options (except No Effective Date) return a single row for each item on the table. If you want a subset of the rows (for example, all future-dated rows or all history rows), enter a selection criterion in the Effective Date field. Use the standard comparison operators rather than the Effective Date comparison operators.

Remember that the effective date operators work differently than the standard comparison operators: they always return a single effective-dated row. For example, Eff Date <= returns the one row for which the EFFDT value is most recent, whereas not greater than would return the currently active row and all history rows.

See Also

Using Effective Dates

Relating Multiple Criteria

Using PeopleSoft Query, you can relate multiple criteria in specific ways that you define using the AND, AND NOT, OR, and OR NOT operators. You can also group criteria using parentheses.

This section discusses how to:

• Use AND and OR logical operators.

• Group criteria with parentheses.

Using AND and OR Logical Operators

When you specify two or more selection criteria for a query, you must tell PeopleSoft Query how to coordinate the different criteria. For example, suppose you are querying your list of customers and you have defined two criteria: one selects customers from the state of Washington and another selects customers who have purchased airplanes. You may want PeopleSoft Query to return only those rows that meet both conditions (customers in Washington who have purchased airplanes), or you may want the rows that meet either one of the conditions (all Washington customers plus all customers who have purchased airplanes).

This diagram illustrates the rows that are returned by AND and OR:

Rows returned by AND and OR

When your query includes multiple criteria, link them using either AND, AND NOT, OR, or OR NOT. When you link two criteria with AND, a row must meet the first and the second criterion for PeopleSoft Query to return it. When you link two criteria with OR, a row must meet the first or the second criterion, but not necessarily both.

By default, PeopleSoft Query assumes that you want rows that meet all of the criteria that you specify. When you add a new criterion, PeopleSoft Query displays AND in the Logical column on the Criteria tab. To link the criterion using one of the other options instead, select the required option from the drop-down list box.

Grouping Criteria with Parentheses

When your query includes multiple criteria, PeopleSoft Query checks the criteria according to the rules of logic: it evaluates criteria that are linked by ANDs before those that are linked by ORs. When all the criteria are linked by ANDs, this order always returns the correct results. When you include one or more ORs, however, this is not always what you want.

For example, suppose you want a list of customers who are not friends and reside in either California (CA) or Florida (FL), you would enter the following criteria:

The set of criteria that was entered in the previous sample page returns a list of customers in California (except for those who are friends) and all customers in Florida (including those who are friends). This list results because PeopleSoft Query evaluates criteria in the order of appearance. It looks for rows where the customer is not friend and where the state is California or rows where the state is Florida.

What you really want PeopleSoft Query to search for are rows where the state is California or Florida, and where the customer type is not friend. That is, you want PeopleSoft Query to evaluate the OR before the AND. To accomplish this task, add parentheses to the list of criteria. When a list of criteria includes parentheses, PeopleSoft Query evaluates the criteria inside the parentheses before the criteria outside the parentheses.

Using the Group Criteria button on the Criteria page to access the Edit Criteria Grouping page where you can insert the opening parenthesis just before the field name and the closing parenthesis just after the comparison value. For example, the following settings of criteria return the results that you want:

To group criteria:

1. Click the Group Criteria button on the Criteria tab.

   The Edit Criteria Grouping page appears.

2. Use the edit boxes to enter parentheses for the criteria as needed.

   A parenthesis appears at the beginning of the Expression1 column for the first row that you selected and at the end of the Expression2 column for the last row that you selected. In the previous example, notice that the AND operator precedes the parentheses, while the OR operator is located within the parentheses.

   You can add as many parentheses as needed.

Defining Prompts

This section provides an overview of prompts and discusses how to edit prompt properties.

Understanding Prompts

Adding a prompt lets you further refine a query when you run it. For example, suppose that you want to change a query so that you could prompt the user to enter a value for the duration of a vacation. Before you add the prompt, the query always retrieves rows for employees who had taken vacation based on a defined constant value on which to make a comparison. Adding a prompt to the query enables the user to enter any duration, and then the query can return employees based on the value provided when running the query.

When you run a query with a prompt, a prompt page requests the required value. All date, time, and datetime prompt fields are required fields when running Query. Enter the value into the field. The query uses the value that you enter as the comparison value for the criterion that included the prompt.

If the field for which you are prompting has an associated prompt table (even if it is the Translate table), the Edit Table drop-down list box shows its name.

Pages Used to Define Prompts

Editing Prompt Properties

Use these steps to access the Edit Prompt Properties page:

1. Select Reporting Tools, Query, Query Manager.

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

3. Access the Prompts page.

4. Click the Add Prompt button or the Edit button on the Prompts page.

   The Edit Prompt Properties page appears.

This is an example of the Edit Prompt Properties page:

To define prompts:

1. From the Prompts page, click the Add Prompt button to add a new prompt, or click the appropriate Edit button to edit an existing prompt.

   The Edit Prompt Properties page appears.

2. Click the Look Up button next to the Field Name field to select a prompt field.

   After you select a prompt field, the name of the field appears. PeopleSoft Query looks to the record definition for information about this field and completes the rest of the page based on its properties.

Note. When using a prompt table on a field from a record definition with multiple keys, you must prompt for all higher-level keys before lower-level keys. PeopleSoft Query needs values for the higher-level keys to generate the correct prompt list. Because of this complication, you should not use multikey prompt tables.

See Also

Adding Fields to Query Content

Defining HAVING Criteria

SQL does not support the use of aggregate functions in WHERE clauses. Therefore, after you have applied an aggregate function to a field, you cannot use that field in your selection criteria, which corresponds to a SQL WHERE clause. When you want to select rows based on the results of an aggregate function, Query Manager enables you to create HAVING criteria. You might use such criteria, for example, when you want a list of the departments whose minimum salary is greater than 100,000.00 USD.

In SQL, a HAVING clause is similar to a WHERE clause for rows of data that have been aggregated into a single row of output. The system evaluates WHERE clauses by looking at the individual table rows before they are grouped by the aggregate function, and then it evaluates HAVING clauses after applying the function. So if you want to check the value that is returned by the function, you must define a HAVING criterion.

When you click the Add Criteria icon from the Fields or Query pages for an aggregate field, new criteria is added to the Having page instead of the Criteria page. Add selection criteria using the Having page in the same way that you add selection criteria using the Criteria page.

Keep in mind that PeopleSoft Query compares the result of applying the aggregate function to the comparison value.

Defining Expressions

This section provides an overview of expressions and discusses how edit expression properties.

Understanding Expressions

Expressions are calculations that PeopleSoft Query performs as part of a query. Use them when you must calculate a value that PeopleSoft Query does not provide by default—for example, to add the values from two fields together or to multiply a field value by a constant.

You can work with an expression as if it were a field in the query: select it for output, change its column heading, or choose it as an “order by” column.

In Query Manager, you can use expressions in two ways:

• As comparison values in selection criteria.

• As columns in the query output.

Editing Expression Properties

Use these steps to access the Edit Expression Properties page:

1. Select Reporting Tools, Query, Query Manager.

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

3. Access the Expressions page.

4. Click the Add Expression button or the Edit button.

   The Edit Expression Properties page appears.

This is an example of the Edit Expression Properties page:

To add or edit expressions for queries:

1. Select Reporting Tools, Query, Query Manager, then click the Create New Query link, and then select the Expressions tab to open the Expressions page.

2. The query name appears in the Query Name field.

   The default for this field is New Unsaved Query until you change it on the Properties page.

3. Click the Add Expression button to open the Edit Expression Properties page, where you can select expression types.

4. Select an option from the Expression Type drop-down list box.

   If you select Character, enter the maximum length of the expression result in the Length field.

   If you select Number or Signed Number, enter the total number of digits in the Length field and the number of digits after the decimal point in the Decimal field.

   Note. For Number and Signed Number, expression types, the Length field defines the total length of the number (integer portion + decimals portion). For example, if Length = 10 and Decimals = 3, then this means that the integer portion = 7 (Length - Decimals = Integer).

5. If you are entering an aggregate value, such as SUM, AVG, or COUNT, select the Aggregate Expression check box.

6. In the Expression Text field, enter the expression (for example, A. Total * .1, where A represents the first record.)

   Query Manager inserts the expression into the SQL for you. You can include Oracle hints in PeopleSoft Query expressions as long as you adhere to the following rules:

   • Expressions containing a hint must begin with /*+ .

   • Expressions can contain only one hint.

     For example, only one set of /*+ */ is allowed.

   • Expressions must contain a complete hint.

     For example, an expression can't have only /*+ or */ . Both must be in the same expression.

7. Click the Add Prompt button to add prompt properties for this expression; click the Add Field button to add another field to this expression.

   Note. You must define a prompt, as described in the next section, before you can add it to your expression.

Using MetaSQL in Expressions

MetaSQL enables you to enter an expression that can be used across any supported database. For example, to concatenate strings, use the MetaSQL %CONCAT, and Enterprise PeopleTools will convert it to the appropriate database string concatenation function. To create a substring, use %SUBSTRING.

Note. You need to make sure that the expression type and length are set correctly for the data that is going to be returned. Only the MetaSQL functions that are valid for dynamic views will work with PeopleSoft Query.

See Also

Meta-SQL

Drilling URL in Oracle PeopleSoft Query

This section provides an overview of drilling URL in Oracle PeopleSoft Query, lists common terminology used in this section, and discusses how to:

• View and edit expression properties.

• Define Query URL widgets.

• Define Component URL widgets.

• Define External URL widgets.

• Query results with Drilling URLs.

• Schedule Queries with Drilling URLs.

Understanding Drilling URLs

Drilling URLs are the URLs that you define by selecting the menu, component, page, portal object, or URL of choice.

When you build a query using Query Manager, you can define drilling URLs that are associated with this query. These settings are saved into the database, along with prompt, criteria, and so on, as part of the metadata for this query. When you execute this query through Query Manager or Query Viewer, the query results page shows results as links, which you can click to be redirected to a different page in a new browser. Depending on how drilling URLs are defined, the new browser is either a PeopleSoft Pure Internet Architecture page, another query result page, or an external page.

Common Terminology Used in This Section

Pages Used to Define Drilling URLs

Viewing and Editing Expression Properties

Drilling URLs are a special type of expression that you can define using the Edit Expression Properties page in Query Manager.

To access the Edit Expression Properties page:

1. Select Reporting Tools, Query, Query Manager.

2. Click the Create New Query link on the Query Manager search page, or click the Search button and open an existing one.

3. If you create a new query, click the Search button and click the Add Record link to select a record for the query.

4. Select the Expressions tab, and click the Add Expression button.

   The Edit Expression Properties page appears.

This is an example of the Edit Expression Properties page:

Expression type	To define drilling URLs, you must select the Drilling URL option from the Expression type list.
Expression text	Optionally, type the URL in the Expression text text box. Note. You have two options: type the URL in this Expression Text box or click the Query URL, Component URL, or External URL links to allow the appropriate system building URLs. If you type the URL directly into the Expression text box, the system does not validate against a value for the correct format.
Query URL	Click the Query URL link to access the Query URL definition widget, where you can select a query to build URLs in a query URL format.
Component URL	Click the Component URL link to access the Component URL definition widget, where you can select a component to build URLs in a component URL format.
External URL	Click the External URL link to access the External URL definition widget, where you can enter external URL to build URLs in an external URL format.

Note. Drilling URLs are saved into database as an expression, so you have the option of accessing the Expressions page and adding the defined drilling URLs as fields. However, since drilling URLs are a special type of expression, you cannot add it as a criterion. You can add drilling URLs as query fields just like regular expressions. On a query result page, values in that column will be expanded to a fully qualified URL, which you can click to either run a query, access a PeopleSoft Pure Internet Architecture page, or go to an external URL.

See Defining Expressions.

Defining Query URL Widgets

The Query URL widget enables you to use the Select a Query page to build drilling URLs in a query URL format. You can click the Query URL link in the Edit Expression Properties page to access the Select a Query page.

To access the Select a Query page:

1. Select Reporting Tools, Query, Query Manager.

2. Click the Create New Query link on the Query Manager search page, or click the Search button and open an existing one.

3. If you create a new query, click the Search button and click the Add Record link to select a record for the query.

4. Select the Expressions tab and click the Add Expression button.

   The Edit Expression Properties page appears.

5. Select the Drilling URL option from the Expression Type list.

6. Click the Query URL link.

   The Select a Query page appears.

This is an example of the Select a Query page:

Query Name	Type a query name in the text box or click the query lookup icon to search for an existing query.
Prompt Keys	Click the Prompt Keys button to access the Query Search Page where you can search for the prompt criteria of the query that you entered in the Query Name field. If the entered query has prompt criteria, a list of those fields appears in the URL Keys section. If the entered query has no prompt criteria, a message appears saying “Query does not contain any prompt key(s)”. Note. This step is optional. If you do not map any prompt keys to the source query column field, then when you click a drilling URL link in query result column page, you will be directed to the Prompt page, where you can enter prompt key values before retrieving query result.
Map Columns	Click the Map Columns button to display the Map URL to Query Columns section. Note. This step is required for the drilling URLs to be available in a query results page. You can define multiple drilling URLs in a query, but each query result column can only bind with one URL. Therefore, when you click the Column Mapping button, only those fields that have no bond with any URL appear in the Map URL to Query Columns section. If all query columns have bonds with other tracking URLs, then a message appears saying “All columns have already been mapped to other drilling URLs.”
URL Keys	The URL Keys section is available only after you define the Query field and click the Prompt Keys button. In this URL Keys section, you define value mapping between Drilling URL destination query prompt fields and Drilling URL source query result column fields. To map these fields, select the key field by selecting the check box adjacent to a prompt key field, and then click the lookup icon to select the source query column field to map to it. Note. This step is optional. If you do not map any prompt keys to a source query column field, then when you click a drilling URL link on the query result column page, you will be directed to the Prompt page, where you can enter prompt key values before you retrieve query results.
Map URL to Query Columns	The Map URL to Query Columns section is available only after you define the component values and click the Map Columns button. Note. This step is required for the drilling URLs to be available in the query results page. Use the Map Columns button for binding source query result column fields with Drilling URL. You must map URL to query result columns in order for the drilling URLs to be available as links in column fields of the source query results. You can define column mapping by selecting the appropriate check box in the Map URL to Query Columns section.
OK	Click the OK button to finish defining the Query URL widget and return to the Edit Expression Properties page.

Searching for Query

Access the Query Search page (from the Select a Query page, click the Prompt Keys button).

Query Search page

Use this page to search for the prompt criteria of the query that you entered in the Query Name field of the Select a Query page. If the entered query has prompt criteria, a list of those fields appears in the URL Keys section. If the entered query has no prompt criteria, a message appears saying “Query does not contain any prompt key(s)”.

Steps Used to Select a Query to Build Drilling URLs

To select a query to build drilling URLs:

1. Select Reporting Tools, Query, Query Manager.

   The Query Manager search page appears.

2. Click the Create New Query link on the Query Manager search page to create a source query, or click the Search button and open an existing one.

3. If you create a new source query, click the Search button and click the Add Record link to select a record for the query.

   The Fields page appears.

4. Select the Expressions tab, and click the Add Expression button.

   The Edit Expression Properties page appears.

5. Select the Drilling URL option from the Expression Type list.

6. Click the Query URL link to define drilling URL for the source query that you just created.

   The Select a Query page appears.

7. Type a query name in the Query Name text box, or click the lookup icon to search for one from existing queries.

8. Optionally, define value mapping in the URL Keys section.

   1. Click the Prompt Keys button to bind the prompt keys of the destination query with the columns of the source query.

      Note. If the destination query has prompt criteria, the URL Keys section appears with a list of key fields.

   2. Define value mapping in the URL Keys section by selecting the check box adjacent to a prompt key field, and then clicking the lookup icon to select a source query column field to map to it.

9. Optionally, define the column mapping in the Map URL to Query Columns section:

   1. Click the Map Columns button to map the drilling URL to a source query selected column.

   2. Select appropriate values from the Map URL to Query Columns section.

10. Click the OK button.

    The Edit Expression Properties reappears with query drilling URLs in the Expression Text field.

    This is an example of a drilling URL:

    '/q/?ICAction=ICQryNameURL=PUBLIC.DESTINATION&BIND1=A.DEPTID:A.DEPTID'

    In this example, the standard query URL format is:

    /q/?ICAction=ICQryNameURL=PUBLIC.DESTINATION&BIND1=A.DEPTID

    and the binding column is A.DEPTID.

    Thus, this drilling URL is binding with column A.DEPTID.

    Note that, a query drilling URL:

    • Always begins with '/q/.

    • Is saved as part of expression metadata for the query.

    • Contains two parts: query URL format and query result columns binding with the Drilling URL.

11. Save your query.

12. Select Reporting Tools, Query, Query Manager.

    Alternatively, select Reporting Tools, Query, Query Viewer.

13. Click the HTML link or the Download to Excel link.

    The query results page displays results as links. When you click these links, the destination query is run using prompt key values that are defined using the source query.

Defining Component URL Widgets

The Component URL widget enables you to use the Select a Component page to build drilling URLs in component URL format. When you click the Component URL link in the Edit Expression Properties page to access the Select a Component page, a PeopleSoft Pure Internet Architecture page is launched.

To access the Select a Component page:

1. Select Reporting Tools, Query, Query Manager.

2. Click the Create New Query link on the Query Manager search page, or click the Search button and open an existing one.

3. If you create a new query, click the Search button and click the Add Record link to select a record for the query.

4. Select the Expressions tab.

5. Click the Add Expression button.

   The Edit Expression Properties page appears.

6. Select the Drilling URL option from the Expression Type list.

7. Click the Component URL link.

   The Select a Component page appears.

This is an example of the Select a Component page:

Content Reference	Enter the name of a content reference or use the Add Content Reference Link link to select a content reference from an existing list. See Administering Content References.
Add Content Reference Link	Click the Add Content Reference Link link to access the Select a Content Reference or Content Reference Link page, where you can select a content reference or content reference link. Note. The values of the Menu Name, Market, and Component fields are populated if you select a content reference or content reference link using the Add Content Reference Link link.
Menu Name	Enter a menu name or click the lookup icon and select one menu name from an existing list. Otherwise, the menu name value is populated if you select a content reference or content reference link using the Add Content Reference Link link.
Market	Enter a market code or click the lookup icon and select one market code from an existing list. The market code is populated automatically if you select a content reference or content reference link using the Add Content Reference Link link.
Component	Enter a component name or click the lookup icon and select one component name from an existing list. Otherwise, the component name value is populated if you select a content reference or content reference link using the Add Content Reference Link link.
Search Keys	Click the Search Key button to select mapping between component search keys and source query result columns. If the entered component has a search key, a list of those search keys appears in the URL Keys section. If the entered component has no search key, a message appears saying “Component does not contain any search keys”. Note. This step is optional. If you do not map any search keys to the source component, then when you click a drilling URL link in the query result column page, you will be directed to a component search page, where you can select search key values.
Map Columns	Click the Map Columns button to display the Map URL to Query Columns section. Note. This step is required for the drilling URLs to be available in query results page. You can define multiple drilling URLs in a query, but each query result column can only bind with one URL. When you click the Map Columns button, only those fields that have no bond with any URL appear in the Map URL to Query Columns section. If all query columns have bonds with other tracking URLs, then a message appears saying “All columns have already been mapped to other drilling URLs.”
URL Keys	The URL Keys section is available only after you define the component and click the Search Keys button. In this URL Keys section, you define value mapping between Drilling URL destination component search keys and Drilling URL source query result column fields. To map these fields, select the key field by selecting its check box and, then click the lookup icon to select source query column field to map to it. Note. This step is optional. If you do not map any search keys to the source component, then when you click a drilling URL link in query result column page, you will be directed to a component search page, where you can enter search key values before you launch the component.
Map URL to Query Columns	The Map URL to Query Columns section is available only after you define the component values and click the Map Columns button. Note. This step is required for the drilling URLs to be available in the query results page. Use the Map Columns button for binding source query result column fields with a Drilling URL. You must map URLs to query result columns in order for the drilling URLs to be available as links in column fields of the source query results. You can define column mapping by selecting the appropriate check box in this Map URL to Query Columns section.
OK	Click the OK button to finish defining a Component URL widget and return to the Edit Expression Properties page.

Selecting a Content Reference or Content Reference Link

Access the Select a Content Reference or Content Reference Link page (from the Select a Component page, click the Add Content Reference Link link).

Use this page to select a content reference or content reference link.

Steps Used to Select a Component to Build Drilling URLs

To select a component to build drilling URLs:

1. Select Reporting Tools, Query, Query Manager.

   The Query Manager search page appears.

2. Click the Create New Query link on the Query Manager search page to create a source query, or click the Search button and open an existing one.

3. If you create a new source query, click the Search button and click the Add Record link to select a record for the query.

   The Fields page appears.

4. Select the Expressions tab, and click the Add Expression button.

   The Edit Expression Properties page appears.

5. Select the Drilling URL option from the Expression Type list.

6. Click the Component URL link to define a drilling URL for the source query that you just created.

   The Select a Component page appears.

7. Type a content reference in the text box or click the Add Content Reference Link link to select one from existing components.

8. If needed, define the values for the Menu Name, Market, and Component fields.

9. Optionally, define value mapping in the URL Keys section.

   1. Click the Search Keys button.

      Note. If the selected component has a search key, the URL Keys section appears with a list of search key fields.

   2. Define value mapping in the URL Keys section by selecting an appropriate search field and selecting a source query results column.

10. Optionally, define the map columns in the Map URL to Query Columns section.

    1. Click the Map Columns button to map the drilling URL to a source query selected column.

    2. Select appropriate values from the Map URL to Query Columns section.

11. Click the OK button.

    The Edit Expression Properties page appears with a component drilling URL in the Expression Text field.

    This is an example of a component drilling URL:

    '/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=A.DEPTID&SETID=A.SETID:A.SETID:
    A.DEPTID'

    In this example, the standard query URL format is:

    /c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=A.DEPTID&SETID=A.SETID

    and the binding columns is A.SETID:A.DEPTID.

    Thus, this drilling URL is binding with column A.DEPTID and A.SETID.

    Note that, a component drilling URL:

    • Always begins with '/c/.

    • Is saved as part of expression metadata for the query.

    • Contains two parts: component URL format and query result columns binding with the drilling URL.

12. Save your query.

13. Select Reporting Tools, Query, Query Manager.

    Alternatively, select Reporting Tools, Query, Query Viewer.

14. Click the HTML link or the Download to Excel link.

    The query results page displays results as links. When you click these links, destination query is run using the prompt key value that is defined using the source query.

Defining External URL Widgets

The External URL widget enables you to use the Enter an External URL page to build drilling URLs in an external URL format; when you click the External URL link in the Edit Expression Properties page to access the Enter an External URL page, you are directed to an external page.

To access the Enter an External URL page:

1. Select Reporting Tools, Query, Query Manager.

2. Click the Create New Query link on the Query Manager search page, or click the Search button and open an existing one.

3. If you create a new query, click the Search button and click the Add Record link to select a record for the query.

4. Select the Expressions tab.

5. Click the Add Expression button.

   The Edit Expression Properties page appears.

6. Select the Drilling URL option from the Expression Type list.

7. Click the External URL link.

   The Enter an External URL page appears.

This is an example of the Enter an External URL page:

Note. Query URL and component URL have a certain format for key list values in the URL. However, external URL has no key mapping widget because external URL is free-format URL.

Steps Used to Enter External URLs to Build Drilling URLs

To enter an external URL to build drilling URLs:

1. Select Reporting Tools, Query, Query Manager.

   The Query Manager search page appears.

2. Click the Create New Query link on the Query Manager search page to create a source query, or click the Search button and open an existing one.

3. If you create a new source query, click the Search button and click the Add Record link to select a record for the query.

   The Fields page appears.

4. Select the Expressions tab, and click the Add Expression button.

   The Edit Expression Properties page appears.

5. Select the Drilling URL option from the Expression Type list.

6. Click the External URL link to define drilling URL for the source query that you just created.

   The Enter an External URL page appears.

7. Type a valid URL in the External URL edit box.

8. Optionally, define the column mapping in the Map URL to Query Columns section.

   1. Click the Map Columns button to map the drilling URL to source query selected column.

   2. Select appropriate values from the Map URL to Query Columns section.

9. Click the OK button.

   The Edit Expression Properties reappears with query drilling URLs in the Expression Text field.

   An external drilling URL always begins with '/e/.

   This is an example of external drilling URL: '/e/?url=[http://www.yahoo.com]:A.EFFDT.

10. Save your query.

11. Select Reporting Tools, Query, Query Manager.

    Alternatively, select Reporting Tools, Query, Query Viewer.

12. Click the HTML link or the Download to Excel link.

    The query results page displays results as links. When you click these links, destination query is run using prompt key value that is defined using the source query.

Querying Results with Drilling URLs

When you access the Run page of a query that has a Drilling URL defined, its query results are shown as links. Clicking these result links either:

• Runs the destination query with prompt key values if the source query has prompt criteria defined and prompt keys are mapped to source query result columns.

• Launches the component with the search key values if the source query has search keys defined and search keys are mapped to source query result columns.

• Redirects the external URL.

This is an example of the Run page with query results as links:

On the Run page, when you click the Download to Excel link to download query results to a Microsoft Excel spreadsheet, an Excel spreadsheet is launched with links to respective cells. When you click the Excel link from Query Manager or the Query Viewer search results page, you get similar query results.

This is an example of query results in Microsoft Excel when you click the Excel link in Query Manager or Query Viewer. Note that the URL appears when you hover the mouse over a cell:

You can also view the query results of a query that has drilling URL defined using the HTML links in Query Manager or Query Viewer.

This is an example of query results as links when you click the HTML links in Query Manager or Query Viewer:

In this query result page:

• You can click the Excel Spreadsheet link to download query results to Microsoft Excel.

  Note. The Excel Spreadsheet link in this query result page has the same usage as the Excel link in Query Manager or the Query Viewer search result page.

• You can click the CSV Text File link to download query results into a CSV format file.

  Note. Because CSV file is opened using a text editor such as Notepad and links do not apply to text editors, unlike HTML and Excel, no links exist in the CSV format file. However, if you select drilling URL as query selected field, then the drilling URL appears in a fully expanded version.

This is an example of CSV file content:

"QEDM1","10900","10900","http://buffy-lap1.peoplesoft.com:8080/psp/ps_2/EMPLOYEE
/QE_LOCAL/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=10900&SETID=QEDM1”

Scheduling Queries with Drilling URLs

The Schedule Query page has five different output types: HTM, PDF, TXT, XLS, and XML. Result files from a successful schedule query run contain links for respective columns using HTM, PDF, and XLS formats.

This is an example of query results in PDF format. Note that the URL appears when you hover the mouse over a cell:

If drilling URL is defined in the query, then no links exist in the result file for TXT and XML formats. However, if you select drilling URLs as a query selected field, drilling URLs are shown in a fully expanded version.

This is an example of TXT file content:

"QEDM1","10900","10900","http://buffy-lap1.peoplesoft.com:8080/psp/ps_2/EMPLOYEE
/QE_LOCAL/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=10900&SETID=QEDM1

Note. Theoretically, one query can have multiple drilling URLs defined; however, the number of URLs for each query should be limited to enhance performance. For queries that return a large number of results, having multiple URLs defined would slow query execution time.