Yesterday, I participated in a very interesting conference call with a PeopleSoft customer who has been struggling with certain aspects of drilling in nVision. Although I referred to several of the components of this previous posting on drilling in nVision, I realized that there are other aspects that weren’t discussed that warrant a follow-on posting.
For those who read the previous drilling posting and downloaded the Journal drill layout that was part of that posting, you will see a “working” example of this. I forwarded a similar layout to the PeopleSoft Sales Support organization, so that they can use it as a template for building out examples of drilling to a peoplesoft page. One thing to note is that this drilldown layout is dependent on a defined name that is only populated when a report is run through the web (not on the client), so it will not work when you drill in 2-tier.
When enabling a layout to drill to a page, you will be using a feature that the PeopleSoft Portal uses for navigation: the URL syntax for opening a page with a piece of data. When scoping PeopleTools 8.4, we realized that although the syntax existed, it was relatively cumbersome to come up with this URL on your own. Therefore, we added a “copy URL” icon to the upper right of every PeopleSoft page (which shows up as an “HTTP” icon on the upper right). When you open a PeopleSoft page with a value (such as a purchase order), you can click on the image and the URL will be copied to the clipboard. You can then paste it into notepad or another editor to see what is needed to access the page with that piece of data.
When reviewing the URL, you will see the standard web server information (or URI), as well as the query string. Any needed parameters will show up as something similar to “&BUSINESS_UNIT=US001&PO_ID=10122274”. When setting the layout up to drill to a specific value, you will be substituting cell references that contain the values you need in the right side of the each = sign.
Now that you know what the URL needs to look like, it is now time to add the links to your drilldown layout. The easiest way to do this is to follow a technique similar to that posted in the “enabling macros” posting in this blog. You use the drilldown layout to get real data in it (so that you can ensure that the references will give you the right values). When adding the links, you will be using the =hyperlink() function in excel, and then concatenating the string for the hyperlink.
Keep in mind that the function must be put in the total row of the layout, and that you need to make sure that the column that contains the hyperlink function has the copy formulas setting in the nVision dialog, so that your formula will be copied to each row of data.
Obviously, if the drilldown result doesn’t have enough information to pass to the hyperlink, you may have to create a new drilldown layout (or extend the existing one). This may require adding fields to the query supporting the drill if the query doesn’t have that data.
Drilling from nVision to a query is almost the same as drilling from nVision to a page. There is a URL syntax for running PS/Queries, just like there is a URL syntax for opening pages. Therefore, you can drill from an nVision report using the same techniques listed above, as long as you know how to construct the URL for running a query.
This technique is put to use in PeopleTools 8.4 in the security queries. When navigating into the Security administration pages, there’s a page that has hyperlinks to run queries to tell you information about what users are in what roles, what permission lists have what pages and roles, etc.
The easiest way to find these hyperlinks is to use the Query Viewer, and right click on the “view” link for one of the queries in a search result to copy the shortcut. You can then copy it into notepad or another tool. This will give you the foundation for the hyperlink, but is missing the components of the URL that contain the runtime parameters (or prompts) for the query. In order to add the needed parameters, you merely append to the URL those parameters. In query, these parameters are identified by a BIND#, where the # is a sequential number. In other words, a query with 3 parameters will have the following appended to the end of the URL: “
=hyperlink("http://servername.peoplesoft.com/psp/... &BIND1="&C5&"&BIND2="&F5&"&BIND3="&H5,"Drill to Query")
The general limitations in this approach are as follows:
You must hard code the URLs into the layout (which can be an issue when you have both a development and test environment… A technique to minimize this issue is to put both URLs into each spreadsheet in different cells, and then switch the one you use as part of the move to production (using a defined name to accomplish this will minimize the number of cells you have to touch).
You need to have a different column for each target you want to go to (and, again, this is hard-coded into the report).
Products we’ve created to address these limitations
Due to the interest in this posting, we’ve created a product that dramatically improves drilling in nVision with minimal impact to your environment. Our nVision drilling snap-on allows drilling to pages and queries without requiring you to modify existing reports as listed above.
Take a look at the product page and even watch a pre-recorded demo of it in action.
If you want tighter integration between pages and reports, you’re probably interested in our ERI products. The linkage is completely external from the drilldown layouts, and the menus are generated dynamically, based on the context. In addition, the ERI toolkit allows drilling from a PeopleSoft page back into reports without requiring modification of the PeopleSoft page. However, if you have a limited number of places to drill, and a limited number of drilldown layouts to use, ERI may be overkill.
Labels: Drilling, nVision