Adding Drilling URLs to PSQuery

• By psgeek
• May 4, 2019
• query

Drilling URLs allow you to attach hyperlinks to a field in the PS Query results.

The hyperlink (url) can link to another query, a PeopleSoft page (component) or an external website (as well as a few other types). Drilling URLs lessen the amount of time spent researching and copying/pasting values to find more information. This feature became available with PeopleTools 8.50.

Drilling URLs are built under the Expression tab in the PeopleSoft Query Manager tool.

While creating/editing a query in Query Manager:

1. Go to the Expression tab.
2. Create a new Expression, and pick the Drilling URL.
3. Pick the Drilling URL Type (Query and Component are the most popular).
4. The Portal and Node name are optional for most sites.
5. Specify the query or component.
6. Define the Prompt/Search Key fields.  Use the checkbox!
7. You can skip the Select Field – Useful if the field/field value pair need to be in the url.
8. Click Map Columns to designate which fields in the query will have the hyperlink.

A helpful YouTube video has been built, to walk you through this.

Aggregate Data Into a Single Column

• By psgeek
• May 3, 2019
• query

Use LISTAGG as a query expression, to string several rows into a single row, in a single column.

This is a trick for PSQuery, for Oracle Databases only (at this time). We’ll be using the LISTAGG function. Read more about it at
https://www.geeksforgeeks.org/sql-listagg/

In Query Expression, you can add this LISTAGG function.

The below is an example for the Vendor Address phone number table in PeopleSoft FSCM. The intended desire is to display each SETID and VENDOR_ID on its own row, but only once per combination. Then we want a third field to put all phone numbers into a single column. If a vendor has 1 or 20 phone numbers, we just want one row of data, but list all phone numbers in the third field in our results.

Steps to produce this in FSCM

1. Create a new Query in Query Manager.
2. Insert record VENDOR_ADDR_PHN.
   • The Effective Dated logic will be automatically added. This is fine.
3. Select the following fields to be displayed: SETID and VENDOR_ID.
   • Use the checkboxes on the Query tab.
4. Go to the Expressions tab.
5. Create a new Expression that has the following (screenshot found later in this post):
   • Expression Type: Long Character
   • Aggregate Function: Turn this checkbox on.
   • Expression Text: LISTAGG(A.PHONE, ‘ , ‘) WITHIN GROUP (ORDER BY A.PHONE)
   • (If you are using this trick with other tables already added, be sure to use the proper alias and field name. Our example assumes the field PHONE is from record alias “A”.)
6. Click OK, to save the new expression.
7. Choose “Use as Field“, for our new expression.
   • This adds our new expression to the Fields tab.
8. Modify any heading text on the Fields tab.
9. Run to view results.
10. You should find that each SETID and VENDOR_ID have their own row, with the third field (Phone) having 1 or more phone numbers, separated by commas.

Bonus

You can also try out this Expression Text. It adds the Phone Type.

LISTAGG(A.PHONE_TYPE %CONCAT ':' %CONCAT A.PHONE, ' , ') WITHIN GROUP (ORDER BY A.PHONE_TYPE %CONCAT ':' %CONCAT A.PHONE)

Results

Finding Number of Days Between Two Dates

• By psgeek
• April 30, 2019
• query

In this post, we’ll cover how to find the number of days between two dates, using %DATEDIFF. This can be placed in a Query Expression.

Some users will use expressions that are specific to the database, such as Oracle DB or MS SQL Server DB. Instead, use MetaSQL whenever possible.

To find the difference in days between today and a date, use the following:

%DateDiff(%datein(A.REQ_DT),%CurrentDateIn)

To find the difference between two dates, when you don’t need today, use the following:

%DateDiff(%datein(A.DATE1),%datein(A.DATE2))

You have to use %datein to format the date correctly. PeopleSoft by default turns your date into a string; %datein will turn it back into a date.

Listing of Date Math MetaSQL we find most useful:

• %DateAdd(from_date, nbr_days_to_add) – You can use negative numbers in the second parameter.
• %DateDiff(from_date, to_date)
• %DateTimeDiff(from_datetime, to_datetime) – Gives the difference between two date/times in minutes.