PS Query | Adding a hyperlink to the results of a PS Query
A. What is a ‘Drillable Query’?
The idea of a drillable query – adding hyperlinks to the query results so that users can go directly to a PeopleSoft page – is something that excites end users greatly. And it’s not hard to see why. With the links in place, there is no need to copy-and-paste data from one page to another, or scribble down bits of information onto paper. Clicking the link will transfer the user directly to the underlying PeopleSoft page, with search data already filled in.
Fortunately for the users, there are a couple of ways to do this in PS Query. The older, traditional solution was the only choice in any version of PeopleTools prior to 8.5. This solution was slightly crude and clumsy, but for the most part, it still did an acceptable job. From PeopleTools 8.5 onwards, PS Query has included the concept of the ‘Drilling URL’. This adds much more power to the user’s ability to create hyperlinks.
We will explore both options in the remainder of this post. Incidentally, all of the examples assume that you have already created the main query and only need to add the hyperlink to finish off the task.
B. Traditional Approach – Static URL
To demonstrate the traditional approach, we will firstly add a static URL. We will then move onto a more complex example, passing in parameters to create a dynamic hyperlink.
Let’s start by adding a fixed URL (‘https://www.barefootps.org’) to each row in the query results. In the query, go to the ‘Expressions’ tab and create a new expression as follows:
Make sure you enclose the entire URL string in single quote characters. Also, don’t forget to specify a character length of 30.
After clicking ‘OK’ to save the expression, click the ‘Use as Field’ hyperlink to add the URL to the output results:
Now when you run the query, you should the static URL appearing as the final column:
Better still, the browser has detected that this value is a hyperlink, so has added the appropriate clickable property without us having the worry about HTML code.
C. Traditional Approach – Dynamic URL
In most cases, a static URL won’t achieve an awful lot. What the users really want is a hyperlink that is specific to the data being displayed. To get this working then, we firstly have to do a couple of things outside of PS Query.
Sticking with the Country example, let’s assume that the users want to transfer to the Country set-up page for the value being displayed. We therefore need to work out the URL for the Country page. We do this by going to the Country component and making a note of the URL that appears in the browser’s navigation bar. It should appear something like this:
https://xyz.local:7011/psp/dbname/EMPLOYEE/SA/c/SETUP_HRMS.COUNTRY_TABLE.GBL
To make a URL specific to a country, we simply append the search key information to the end of the URL. The complete URL should end up looking like this:
https://xyz.local:7011/psp/dbname/EMPLOYEE/SA/c/SETUP_HRMS.COUNTRY_TABLE.GBL?COUNTRY=GBR
At this point, you can test the URL by pasting it directly into the browser, and then making sure it takes you to the right place.
With these URLs in hand, we can now go back and add the hyperlinks to our query.
Return to the ‘Expressions’ tab and add the following expression:
The ‘Expression Text’ field consists of three pieces of information:
(1) The static URL text up to, and including, the country key field. This must be enclosed in single quote characters
(2) A double pipe symbol (||) to concatenate two strings
(3) The name of the field that contains the key information (this can be added by using the ‘Add Field’ option, or by typing in the information directly)
After clicking OK, add the field to the results by clicking the ‘Use as Field’ option. When running the query, you should see the URLs displayed, each specific to a country code:
Just to confirm, clicking on any of the hyperlinks opens up the Country page:
D. Drilling URLs
Although the traditional solution works well, it has its limitations. The main drawback is that the URL has to be specific to the database in which the query is being written. If you migrate the query to another database, you will most likely have to amend the URL string to cater for the new database. The more databases you have, the more time consuming this task becomes.
Fortunately, from PeopleTools v8.x onwards, you have another option, the ‘Drilling URL’. This provides similar functionality to the traditional approach described above, but with much greater flexibility and power. The link only needs to be set up once, and then it will work across all database instances.
We again start by going to the ‘Expressions’ tab. This time we will pick a field type of ‘Drilling URL’:
After selecting ‘Drilling URL’, the page displays a number of options for specifying the URL. For this example, we will stick to the most common option: ‘Component URL’.
Click the ‘Component URL’ link and enter the details specific to your component. If you’re unsure of these details, go to the page in question and click Ctrl-J to display the object details. After filling in the ‘Component URL’ page, you should end with a display as follows:
The top-half of this page is fairly straightforward, however the bottom half can be a little confusing. Therefore, to set the information properly, follow this approach:
• Enter the top half of the page, as per the example above
• Click the ‘Map Columns’ button.
• Select the fields that you wish to be changed to a hyperlink. In the example above, I have specified both the country code and the country description.
• Click the ‘Search Keys’ button
• Click the ‘Selection Flag’ checkbox next to each key field
• Click the prompt edit box for the ‘Key Value’ field and then specify the field in your query that contains the required information.
• Click OK to save and exit the page
Once you return to the expression, you should see something like this:
You don’t need to worry too much about the detail in the ‘Expression Text’ field, except to note that this is the URL, similar to what we had to do with the traditional approach.
Now save your query and then test the output. You should see the country code and description fields displayed as hyperlinks:
And again, just to test, we can make certain the country page appears after clicking the link:
See Also: