×
UX/Mobile/Responsive

Query Drilling URL Feature in PeopleTools 8.5 Review

By Larry Grey • October 18, 2006

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.

c4

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:

c1

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

c2

Here is what is displayed when you click on the link to specify a component URL

c3

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.

Stay Updated

9 Replies to “Query Drilling URL Feature in PeopleTools 8.5 Review”

  1. My post of Dec 10,2010 is incorrect – works fine in XMLP as well. just failed to RTFM!!!

  2. This drill thru capability is lost when you use the query as a datasource for an XMLP report. So its a kinda useless in terms of its integration across Oracles reporting offerings ie works in PSquery but not XMLPublisher – pretty poor imho!

  3. This is a cool new feature. I just started playing around with it. I was trying the External URL type. It seems to open the results in the same window, which overwrites the query results (you can use the back button to get back). Is it possible to add some code to get it to open in a new window?

  4. The drilling works great but when i create a query pagelet based on the query with the url drilling, the links disappear! i was quite bumped. Is there any workaround for the links to show up in the query pagelet?

  5. the query drill down is not working fine, instead of filtering by the bind parameters that were passed thru the selected data value in the query, it is showing all the information retrieved by a query without bind parameters. any pointers would be of great help

    thnx @dpolice

  6. It works perfectly, the drilling. But when I export the query result set to excel there is a hyperlink in the excel worksheet that should lead back to the same peoplesoft component. But all that does is closing down the PS session that is already open. Is there some other sort of configuration or security setting?

    Best wishes,

    Jerry van Beers

  7. Thank you for this article. It was very informative. We are now starting our upgrade, and I just started exploring this feature. I have a question; I am trying to creat an expression, type Drilling URL, Query URL, and I need to pass two variables to open the second query results (for example SETID_JOBCODE and JOBCODE. How do I pass two variables at once (both SetID and JObode)? These are my expressions to pass one variable at a time for, SetID and Jobcode:
    ‘/q/?ICAction=ICQryNameURL=PUBLIC.PHS_HS_ALL_JOBCODES&BIND1=B.SETID_JOBCODE:B.SETID_JOBCODE’
    ‘/q/?ICAction=ICQryNameURL=PUBLIC.PHS_HS_ALL_JOBCODES&BIND2=B.JOBCODE:B.JOBCODE’

    Thank you
    Gabrielle

  8. Could I use this feature to select across multiple Environments, lets say across HCM & FIN. Can I build a query in HCM to pull me data from a FIN table etc. If so is is there any Setup that needs to be done to enable it.

    Thanks in advance,
    RajKumar John

Comments are closed.

Request a Demo