Make Your Reports More Active

=Make Your Reports More Active=

When asked for a new report, I often ask the question, "what will you do with the information?". The answer can often lead to an interesting discussion and explores what is really required.

With operational reports and exception reports, the answer is typically, "I take a look at the records and then decide on what needs to be done".

PeopleSoft Query has a great piece of functionality that can accelerate this process for the report recipient - drilling links (or Drilling URLs). A simple device - the ability to put a link straight to a page with all the relevant details. No more copy and paste, no more using a ruler to remember which lines have been checked. These links will also appear in the Excel output.

==How do you add links to a Query?==

Open your query, click on the "Expressions" tab, add a new expression.
You should now see the new expressions dialog. Select "Drilling URL" from the "Type" drop down (that normally starts with "Character").

The screen will change, and you will get a screen with a set of links describing the different types of links you can use:

Component URL - a link to a particular page - with keys supplied from the Query.
Query URL - a link to another Query - if that query has prompts, you can supply those from the parent query.
Attachment URL - a link to an attachment held within the PeopleSoft system.
External URL, Free Form URL - for connecting to external systems.

If you click on the Component URL, you get a page which presents you with the following options:

Portal Name - leave blank to use the one in use by the system or find this in the URL - normally "EMPLOYEE" for HRMS.

Node Name - leave blank or, in most cases, set to the local default node - click the search button and sort the results by default local node (double click the "Default Local Node" heading). There will be one node with the value "1" in this column - that's our man!

Psc and content reference - at first leave these blank.

The Menu Name, Market, Component and Page can be obtained navigating to the page in question and pressing Cntrl-Shift-J.

Once these items have been entered, you can then set up the connection between fields in the query and the fields that you would enter on the component search page. Without these values, the link simply sends you to that search page.

To really get the most out of this feature, you will want each link to connect to something on that row. Time to use the buttons at the bottom of the page.

The first of these is "Search Keys". This button produces a grid of the search keys for the page in question, you can then enter the fields or expressions from your query that you want to use to complete the search dialog. Just as when you complete a search page by typing in the values, you can leave some of the fields blank. If the keys are not complete, then the link will show the half completed search page, rather than taking you directly to the targeted page.

Note: in some versions of PeopleTools, you need to check the "Selection Flag" for the row of the grid you want to complete, otherwise the "Field Name" and "Key Values" get muddled.

The other button to use is the "Map Columns" button. This allows you to associate the link with particular columns in the output. You could have different links for different columns, but too many and confuse poor Yuri the User as to which link does what (was it Employee ID that took me to the Job page or was it Department ID?). If you do want to have many links from one query, you could add columns that describe where the link would take the user. You can also add the Drilling URL expression as a field, but they have a tendency to be very long and unwieldy.

Another way around this is to establish a mini convention - Employee ID always takes you to the Biographical Details, Employee Record Number takes you to Job page etc.

As seen before, you can also run another Query, show an attachment, or connect to another system. All this from the results which could be in HTML format, PDF or Excel. The Query could be scheduled and the Excel results emailed to the user - and so the spreadsheet reconnects the recipient with PeopleSoft - enabling them to continue the next step in the process seamlessly. Wow.

Try it - I'll bet you will always want to make your reports more active.