Extending PSQuery productivity with Drilling Url

PSQuery is an online PeopleTools functionality heavily used by business analysts and key users to gain information from the PeopleSoft application without the need of development or database SQL knowledge. The biggest con of PSQuery is that you end up with one static list of data, which is usually exported to Excel for further enhancement and analysis.

From PeopleTools 8.50, you now have the ability to create more in-depth analyses and productivity using drilling URLs.

When you build a query using Query Manager, you can define drilling URLs that are associated with this query using the expression option. These settings are saved into the database, along with prompt, criteria, and so on, as part of the metadata for this query. When you execute this query through Query Manager or Query Viewer, the query results page shows results as links, which you can click to be redirected to a different page in a new browser. You can create URLs for:

• Other PSQuery, for more in-depth information about a subject, actual drilldown functionality


• PIA Component, direct link to components where the keys for the search page of the component are extracted from the selected columns in the query


• External URL, non PeopleSoft URLs, for example Vendor sites


• Attachment URL, direct link to an attachment that is stored in the database or file system


• Free Format URL, any type of URL you want to construct, can be used for example to deep link to other non-PeopleSoft applications

So how does one create a drilling URL?

You start off by creating an ordinary query as you would usually do. For this example I created a query on table JOB. I then joined JOB with table NAMES, to retrieve the name of the employee and joined JOB with tables DEPT_TBL and JOBCODE_TBL to retrieve the descriptions of the jobcode and the department. I selected the following columns:

• JOB.EMPLID

• JOB.EMPL_RCD


• NAMES.NAME


• JOB.JOBCODE


• JOBCODE_TBL.DESCR


• JOB.DEPT_ID


• DEPT_TBL.DESCR

Now lets create a drilling URL to Component JOB_DATA and map this URL to the JOBCODE_TBL.DESCR field, so that when we click on the Jobcode description, we will be redirected to the JOB_DATA component for the employee record showing in the query.

You start off by selecting tab Expression and click on the button Add Expression and from the dropdown menu you select Drilling URL.

Now select link Component URL.

Use the link Add Content Reference Link, to select the component where we want the drilling URL to redirect to. After you select the Add Content Reference Link you will be presented with a tree of the PeopleSoft menu content reference. From the tree select contenct reference Job Data.

Once you select the content reference you will go back to the previous screen and there you will see that the fields that will produce the link to the component are now filled in, except the Page field is not filled in. Use the prompt icon to select which page you want the drilling URL to navigate to.

After this, click on the button Search Keys. This will bring up a grid with the search keys for the component. Select the query result fields corresponding with the component search keys. You can also choose to fill in a constant value instead of the query result fields.

Finally click on the Map Columns button to change the query result field JOBCODE.DESCR to the drilling URL, otherwise you can add the drilling URL as a seperate result field in the query, but personally I don't find this so appealing.

Now do the same trick to create a drilling URL to component PERSONAL _DATA and map this to EMPLID and NAME and create a drilling URL to component JOB_CODE_TBL and map this to query result field JOB.JOBCODE.

You will now end up with a query with four result fields presented as direct links to PeopleSoft components corresponding to the query results.

Now lets take it a level higher.

I would like to see a list of departments with the headcount and when a select a department I would like to see the employees of that department (query created above).

First create prompts SETID and DEPTID for the query created above and add these prompt the criteria list.

Now create the high level query with the list of departments. I created a query on JOB and DEPT_TBL and selected fields:

• JOB.DEPTID


• DEPT_TBL.DESCR


• JOB.EMPLID (count)

Now select tab Expression and create a Drilling URL of type Query and select the Job details query created prior.

After clicking on Prompt Keys select the query result fields from this query which will be used to fill the prompt values of the lower query. Notice that in the above screen I chose to have DEPTID bind to the query result field and have SETID bind to constant value SHARE. Finally map the drilling URL to query result field DEPT_TBL.DESCR.

You end up with:

Without any coding and all done with online PeopleSoft pages (in a few clicks!) you have created a high level query with a list of headcount per department with a drill down possibly to further detail on the employees residing on these departments and from here a more in depth deep link to the individual transaction data.

Another great feature of PeopleSoft Query is the ability to export the query results to an Excel spreadsheet. When the query contains drilling URLs these are also exported to Excel as hyperlinks. This results in a spreadsheet with deep links to PeopleSoft transaction data.

Imagine the possibilities without the need of customizations or development ...

Finally also have a look at this viewlet which shows extending PSQuery with drilling URLs, publishing as Feeds and Pagelets.