Defining Query Drilling URLs

The Query URL widget enables you to use the Select a Query page (QRY_FIELDS) to build drilling URLs in a query URL format.

Navigation

  1. Select Reporting Tools > Query > Query Manager.

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

  3. Select the Drilling URL option from the Expression Type list and then click the Query URL link.

Image: Select a Query page

This example illustrates the fields and controls on the Select a Query page. Definitions for the fields and controls appear following the example.

Select a Query page

Field or Control

Definition
Portal Name

Optionally, enter or select a portal that is used to build drilling URLs at report runtime.

Note: If the Portal Name field has no value, PeopleSoft Query uses value %PORTAL to form a drilling URL at report runtime.
Node Name

Optionally, enter or select a node that is used to build drilling URLs at report runtime.

If the Node Name field has no value, PSQuery uses value %NODE to form a drilling URL at report runtime.
Use psc (content servlet)

Used to define the content type that is used to build drilling URLs.

  • Deselect this option to use the portal service (psp) type.

    By default, this option is deselected.

  • Select this option to use the content servlet (psc) type.
Query Name

Type a query name in the text box or click the query lookup icon to search for an existing query.
Format

Used to select the format of report results that will be generated from the drilling URLs. Available options are:

  • XML (XMLP format)

    If you select the XML option, drilling URL in the Expression Text field includes the ICQryNameXMLURL value.

  • HTM

    If you select the HTM option, drilling URL in the Expression Text field includes the ICQryNameURL value.

  • XLS

    If you select the XLS option, drilling URL in the Expression Text field includes the ICQryNameExcelURL value.
Prompt Keys

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

Click to access the Select Query Column section.
Map Columns

Click 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.
Select Query Column

Select query result columns to form field name and value pairs and to define the order of the appended value pair in the URL.

Note: The Select Query Column section is available after you click the Select Field button.
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 to finish defining the Query URL widget and return to the Edit Expression Properties page.

Use the Query Search Page (QUERY_URL_SRCH_SPG) 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)”.

Navigation

  1. Select Reporting Tools > Query > Query Manager.

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

  3. Select the Drilling URL option from the Expression Type list, and click the Query URL link.

  4. Click the Prompt Key button next to the Query Name field.

Image: Query Search page

This example illustrates the fields and controls on the Query Search page.

Query Search page

Using the Select Query Column section, you can append field name and value pairs to the end of existing Drilling URL types. The format of the appended field name and value pair is &FIELDNAME=VALUE.

For example:

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

    In this example, the appended field name and value is formed using a static field name, EMPLID, and the static value 00001. At runtime, the drilling URL is expanded as:

    http://myserver.example.com:8920/psp/ps/EMPLOYEE/QE_LOCAL/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL? Action=U&DEPTID=10300&SETID=QEDMOEMPLID=00001

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

    In this example, the appended field name and value pairs are from two query result columns, A.SETID and A.DEPTID. At runtime, the actual values that are returned in the query results for the A.SETID and A.DEPTID columns replace the %A.SETID% and %A.DEPTID%, as in this URL:

    http://myserver.example.com:8920/psp/ps/EMPLOYEE/QE_LOCAL/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&DEPTID=10300&SETID=QEDMO&A.SETID=QEDMO&A.DEPTID=10300

Note: The Expression Text field in the Edit Expression Properties page is not editable. You can modify existing drilling URLs or define new drilling URLs using available drilling URL widgets.

At runtime, the query drilling URL feature is able to process all drilling URLs that are created in PeopleTools releases later than 8.50. For example, when you define the same drilling URL:

  • PeopleTools 8.50 forms the drilling URL as

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

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

  • PeopleTools 8.51 forms the drilling URL as

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

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

  • PeopleTools 8.52 and later forms the drilling URL as

    'psp/EMPLOYEE/QE_LOCAL/q/?ICAction=ICQryNameURL=
    PUBLIC.QE_EMP_DESTINATION&BIND1=%A.DEPTID%: A.DEPTID'

'psp/EMPLOYEE/QE_LOCAL/c/QE_SAMPLE_APPS.QE_DEPT_TBL.GBL?Action=U&
    DEPTID= %A.DEPTID%&SETID=%A.SETID%:A.SETID’

Note: PeopleSoft introduced query drilling URL in PeopleTools 8.50 and added more features in later releases, so drilling URLs created in different releases will look slightly different. However, runtime processing is backward compatible and drilling URLs that have been defined in previous release will be expanded correctly in newer release without the user's need to redefine them.

In PeopleTools 8.50, drilling URLs must be expanded correctly. When you modify a drilling URL that was created in PeopleTools 8.51 or later and then save it in PeopleTools 8.50, the system uses new format and syntax to form the URL. In PeopleTools 8.51 or later, when you run a query with the drilling URLs that were directly upgraded from PeopleTools 8.50, the runtime converts the format differences between the different releases.

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. Optionally, define the portal name, node name, or content type.

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

  9. Select the format of report results that will be generated from the drilling URLs.

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

  11. Optionally, define query result columns for existing drilling URLs:

    1. Click the Select Field button to display the Select Query Column section.

    2. Select query result columns to form fieldname and value pairs and to define the order of the appended value pair in the URL.

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

  13. 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 with the Use psc option is not selected, and the Portal Name and Node Name fields have no values. Notice that, %PORTAL and %NODE values will be used to build URLs at the report runtime:

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

    In this example, 'psc// is your selected portal name, node name, and content type; 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.

    This is an example of a drilling URL with the Use psc option is selected, portal CUSTOMER, and node QE_LOCAL:

    'psc/EMPLOYEE/QE_LOCAL/q/?ICAction=ICQryNameURL=PUBLIC.QE_EMP_DESTINATION:
    A.QE_EMPLOYEE_NAME'

    In this example, 'psc/EMPLOYEE/QE_LOCAL is your selected portal name, node name, and content type.

    Note that, a query drilling URL:

    • Always includes /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.

  14. Save your query.

  15. Select Reporting Tools > Query > Query Manager.

    Alternatively, select Reporting Tools > Query > Query Viewer.

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