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.

• Because the only way it knew whether to turn data into a hyperlink was to evaluate the HTTP: at the beginning of the data returned from query column, references were hard-coded to a specific environment (meaning you have to fix the URI when moving between environments)
• The syntax for embedding references to pages, queries, and references to data to pass into the pages or queries was very cumbersome and depending on the platform, required knowledge of both trimming data and concatenating syntax.
• You could not attach a drilling link to data in the query. In other words, you have to display the drilling link in the result set (often a very large string) as its own entity. Ideally, you would like to be able to click on an EMPLID or DEPTID and drill to data for either of those

Fortunately, the features in PeopleTools 8.5 address these limitations

• The expression object in Query is extended with a new expression type called Drilling URL. Because it is a special type of expression, Query knows to turn it into a hyperlink regardless of the data in it.
• It supports relative references that begin at the content type (Query, Component, or External). This means that queries moved between environments do not need to be modified to fix URI values
• It allows linkage between bind values and data in the query easily without requiring meta-sql or other advanced SQL syntax
• It allows specifying which fields the URL should be attached to. This means that your queries look a lot better and are much more usable. These links are also embedded in all output types of query.

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

'/c/ADMINISTER_WORKFORCE_(GBL).PERSONAL_DATA.GBL?Page=PERSONAL_DATA1&Action=U&EMPLID=A.EMPLID:A.EMPLID:A.NAME_DISPLAY'

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

'/q/?ICAction=ICQryNameURL=PUBLIC.GS_EMPL_ACTIONS&BIND1=A.EMPLID:A.HIGHEST_EDUC_LVL'

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.

Conclusion

In the end, I am very pleased with this feature, and think that customers will benefit greatly from it.

Labels: Drilling, PT850, Query