While attending Open World last week, I was reminded in Robert Taylor’s session on reporting, about the Query Drilling URL feature in PeopleTools 8.5. I quickly decided to spend some time playing around with it to see how it works and what it does.
Although drilling from queries is one of the first topics we’ve presented at user group meetings, there have been some significant improvements in PeopleTools 8.5 (see blog entry for powerpoint with the drilling techniques supported in versions prior to 8.50)
Benefits of this feature in PeopleTools 8.5
Although it was possible to embed drilling hyperlinks in to queries since PeopleTools 8.45, there were several limitations in how it was accomplished that made it difficult to design, manage, support, and even use.
Fortunately, the features in PeopleTools 8.5 address these limitations
How to Use it
To take advantage of this feature, you will want to create an expression for each drilling URL you will want to put in your query. You first go to the Expressions tab in Query and add the expression. When you ge there, there will be a new expression type:
Depending on the type of expression you want to define, you can click on a link that helps you build the appropriate syntax for the URL.
Here is what is displayed when you click on link to specify a query URL
Here is what is displayed when you click on the link to specify a component URL
These pages are very helpful in understanding how to specify the pieces needed to drill to a query or component, what is needed by the query or component as parameters, as well as what is available in your query to pass as parameters.
Going a little deeper
However, it’s interesting to note that all the pages really do is create the syntax for the expression (and the expression is stored in the same table as other expressions). Why don’t we look at this syntax just to understand a bit more about it?
Syntax for drilling to a page
The first thing you’ll see is that the first part of the expression is a standard PeopleSoft URL to a page starting from the /c/ and going to the end of the &Action=U. If you were to supply the host, site, portal, etc. information from a common hyperlink, you could easily type it in the address bar of your browser and go to the page.
The second thing you’ll see is the reference between the field in the search record EMPLID= and the field in the query A.EMPLID. You may also notice that you didn’t have to exit out of the quote of the text in order to enter this like in previous versions.
Finally, you may notice the syntax of :A.EMPLID:A.NAME_DISPLAY. This syntax tells you which fields to attach the URL to (colon first, then query field second).
Syntax for drilling to a query
In a manner similar to pages, everyghing up to and including the query name is a standard URL that could be typed into the browser once adding the host, site, portal, etc. to the beginning. It also uses the standard PeopleSoft syntax of BIND2, BIND2 for each expression. Finally, it uses the same syntax of binding to query fields for both passing parameters and attaching the URL to a field.
Things that I found in playing around
As I played around with this feature, I found that you definitely don’t want to stray too far outside of the expected syntax generated by the pages if you key in values manually.
There were a few situations where I wanted to pass a literal value into a page that didn’t exist in my query (for example, drilling from an employee into the Job table, where the job component needs EMPL_RCD as a parameter). I tried a couple of ways to pass in a value of 0 for it, and managed either to crash the app server or put it into an infinite loop (as I am working with one of the first releases of this, I am sure that this is already addressed in one of the patches that have recently come out). To pass the literal, though, I didn’t even need to try to hard-code it in my expression. Instead, I simply created a number expression with a value of 0 and then used the expression as my parameter for EMPL_RCD.
I was also hoping that I could drill directly to excel when drilling from a query to another query. I modified the ICQryNameURL to ICQryNameExcel, but the resulting link ended up still being ICQryNameURL. I have a hunch that the way the expression works is to find the appropriate tokens and pull them out versus taking the URL as keyed, and that this will make sure that there aren’t injection security risks created by this feature.
In the end, I am very pleased with this feature, and think that customers will benefit greatly from it.