PS Query - Employee Photo

There are couple of options to include images in query output. In this example I am displaying employee pictures which are stored in the database (under the identification data component). I am running PT 8.59.x on HCM 9.2.

The simplest method is to include the table EMPL_PHOTO in the query and join the EMPLID field and display the EMPLOYEE_PHOTO field. On the query properties select "Image Data" that way the image is displayed in the output.

The EMPL_PHOTO table has a field called as PHOTO_SIZENAME and stores each image in 4 different sizes, CARD, LIST, ORCH and PAGE. Hence it is advisble to add a condition/filter to select the appropriate size. In my case I am filtering based on LIST.

The other option is to use drilling URL expression type. 

In this scenario we have to build two queries. The first query will query data from EMPL_PHOTO table and the query properties have to set to "Image Huperlink".

The second query will include EMPL_PHOTO and other tables. Add an expression of type "Drilling URL" and select "Image URL"

In the next dialog box, provide the details of the first query and verify the two key fields EMPLID and PHOTO_SIZENAME are peopulated correctly. The field PHOTO_SIZENAME has to be in the "field list" so will be displayed in the result. So while defining the Image URL you can map the result to this field. This way the crypting URL (expression) need not be displayed as a field in the result but the values in the PHOTO_SIZENAME column will appear hyperlinked. Clicking on this field will display the image.   

Composite Query

Composite Query enables us to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result. There is good amount of info on how these work, limitation etc so I am not going to cover those here. I found them pretty useful to create inline SQL or query which isn't  possible via the regular PS Query (Query Manager) or even via Connected query feature in PeopleSoft.

So for this POC I am running PT 8.58.05 on HCM 9.2 (PUM 35). The SQL or output that I am trying to re-produce is as follows. 

I am listing employees from PS_JOB but I want to list their min ORIG_HIRE_DT value regardless of their current relationship in the orignatization. 

So I created two simple queries in Query Manager as follows.

Parent_Query

SELECT A.EMPLID, A.EMPL_RCD, (CONVERT(CHAR(10),A.EFFDT,121)), A.EFFSEQ,  (CONVERT(CHAR(10),A.LAST_HIRE_DT,121))
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A.EMPL_RCD = A1.EMPL_RCD
    AND A1.OPRID = 'PS'
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT) ))

Inline_Query 

SELECT A.EMPLID, A.ORG_INSTANCE_ERN, MIN( (CONVERT(CHAR(10),A.ORIG_HIRE_DT,121)))
  FROM PS_PER_ORG_INST A, PS_PERS_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A1.OPRID = 'PS')
  GROUP BY  A.EMPLID,  A.ORG_INSTANCE_ERN

Navigated to Reporting Tools > Composite Query > Composite Query Manager and stepped through the wizard.

1. Gave a name to the composite query

2. Provided the Query 1 and Query values. So in my case Query 1 = Parent_Query and Query 2 = Inline_Query. Unfortunately we cannot have more than one inline SQLs, so this looks like a limitation.

3.   Sepcified Query Joins as shown below.

4. On the Output fields page I made the selections as shown below.

5.  On the final page set the status as active and saved the page.

Hovering over the gear icon next to the SQL button presents the option to Run/Preview the data or ouput.

PeopleSoft Reporting Web Services

While browsing through Peoplebooks for API syntax I ran into this topic related to Reporting web services and I was pleasantly surprised that this feature is available out of the box but I don't think is used frequently. Looks like it has been around since 8.56.x  or maybe even before. So PeopleSoft provides SOAP as well as REST based web services to expose PeopleSoft data based on PS Query or Connected query to external systems. I think this is pretty cool. So as long as the data set can be constructed in PS Query or Connected Query it can be exposed out to a 3rd party service using out of the box infrastructure. This greatly simplifies elaborate design and development time of application engine programs which are generally used for building interfaces.

PeopleSoft provides QAS_QRY_SERVICE which is the service for Query Access Manager.

and PROCESSREQUEST which is the service for Process Scheduler. Via these services once can create and get query and process request type items. I tried out the query service to execute a query via Postman and it works like a charm.

There is lot of good info in PeopleBooks so I won't repeat all of that here. So I currently have two RSS feeds based on PS Query which I use for monitoring the status of process monitor processes and jobs and another one which monitors the asynchronous  services. I used these as the test cases to fetch data using the web services instead of RSS. Also setup a user which has access to the service operation and to the queries and can only run the queries. 

As I have the queries already built I tested out the REST service operation QAS_EXECUTEQRY_REST_GET to execute the queries. 

Example of the URI Template:

{OwnerType}/{QueryName}/{OutResultType}/{OutResultFormat}?isconnectedquery=
{isConnectedQuery}&maxrows={MaxRow}&prompt_psqueryname={Prompt_PSQueryName*}
&prompt_uniquepromptname={Prompt_UniquePromptName*}&prompt_fieldvalue=
{Prompt_FieldValue*}&filterfields={FilterFields*}&json_resp={json_response}

Provided QueryName, OwnerName is PUBLIC as the query I am using is public query, OutResultType is JSON as I want the results back in JSON format, OutResultFormat is NONFILE as I want the response in message object, isconnectedquery = N, maxrows set it to 100 ( I would never get so many rows back), json_resp set it to true, did not set anything for rest of the URI parameters. 

HTTP GET https://myserver/PSIGW/RESTListeningConnector/PSFT_HR/ExecuteQuery.v1/PUBLIC/N_MY_QUERY/JSON/NONFILE?isconnectedquery=N&maxrows=100&prompt_psqueryname=&prompt_uniquepromptname=&prompt_fieldvalue=&filterfields=&json_resp=true 

The response is received as follows:

{

   "status":"success",

   "data":{

      "query":{

         "numrows":3,

         "queryname=":"N_MY_QUERY",

         "rows":[

            {

               "attr:rownumber":1,

               "PRCSINSTANCE":558373,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"PSJob",

               "PRCSNAME":"MYPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":2,

               "PRCSINSTANCE":558374,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Process",

               "PRCSNAME":"MYSQRPRCS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Processing",

               "DISTSTATUS":"Scheduled"

            },

            {

               "attr:rownumber":3,

               "PRCSINSTANCE":558375,

               "MAINJOBINSTANCE":558373,

               "PRCSJOBNAME":"MYJOB",

               "PRCSTYPE":"SQR Report",

               "PRCSNAME":"MYPROCESS",

               "OPRID":"PS",

               "RUNCNTLID":"MYRUN",

               "RECURNAME":"Daily",

               "RUNDTTM":"2020-07-04T12:15:00-0400",

               "RUNSTATUS":"Pending",

               "DISTSTATUS":"Scheduled"

            }

         ]

      }

   }

}

Following is an example when query does not return any results.

{

   "status":"success",

   "data":{

      "query":{

         "numrows":0,

         "queryname=":"N_MY_QUERY",

         "rows":[

         ]

      }

   }

}

Query Transformations

I rarely do much work in PS Query, but while doing some other troubleshooting for PS Query related issues, I noticed the "Transformations" tab in Query Manager. I am running PT 8.56.x in HCM 9.2, but looks like this has been around since 8.53 or 8.54. 

So decided to do a quick test to see what this does and how it works. So the benefit here is that the output can be transformed to create reports right out off the output delivered by PS Query.

So as part of this POC used any existing query. Transforming the output to HTML and using a XSL transformation to highlight the row when a certain condition is matched.

On the Transformation tab within Query Manager, click on Add XSLT which opens a window to key in free-form XSLT text. It would have been nice if there is a wizard of some sort to build the XSLT. 

Gave it a Name and in the "Output File Type" lookup selected HTML. Following are the available choices - 001, A01, ABA, AET, APC, CSS, CSV, D01, DAT, DTD, DTL, ERR, GIF, GIR, HTM, HTML, IDX, JCD, JS, LIS, LOG, MDL, MT9, N01, NVT, NXT, OUT, PS, SQL, STDOUT, SYS, TMP, TRC, TRN, TXT, XLSM, XLSX, XML, XSD, ZIP

Not sure what is the difference between HTM and HTML, but I selected HTML. My sample XSLT is designed as follows.

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <html>
<!-- defined a style for the row that is going to be highlighted -->
<style type="text/css">
tr.redRow td {
font-size: 100%;
font-family: calibri;
font-weight:bold;
background-color: #FF2626;
padding-right: 10px;
color:#000;
}
</style>
<body>
<!-- in the html body defined a html table --> <table border="1">
<!-- Added a heading --> <font color="blue"> <h2>Sample Transformation test</h2> </font>
<!-- defined column headers --> <tr> <th>Employee ID</th> <th>Last Name</th> <th>First Name</th>
<th>Location</th> </tr>
<!-- Here I am selecting the data from PS Query --> <xsl:for-each select="query/row"> <tr>
<!-- defined a if condition to check if -->
<!-- value is McLean then highlight the row --> <xsl:if test="L.DESCR='McLean'"> <xsl:attribute name="class">redRow</xsl:attribute> </xsl:if>
<!-- Output the PS Query results --> <td> <xsl:value-of select="A.EMPLID" /> </td> <td> <xsl:value-of select="C.LAST_NAME" /> </td> <td> <xsl:value-of select="C.FIRST_NAME" /> </td> <td> <xsl:value-of select="L.DESCR" /> </td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

So saved this and hit the preview button to preview the results. My query uses runtime prompts so those pop'ed up first, provided values for them and results are displayed in a new window (disable pop-up blocker or allow pop-up for this site).

Sample Transformation test

Employee ID	Last Name	First Name	Location
12345	Doe	John	Madrid
13244	Doe	Jane	Egypt
43423	Hufton	Abcde	London
42343	Fleming	Jill	McLean
34423	Tibold	Jozsef	London
67554	Farrell	William	Johannesburg
43556	Myers	Tony	Ireland
75757	Kramer	Nicole	McLean
27341	Wada	Mark	New York
27045	Roger	Wai	McLean
27098	Wayne	Jessica	New York
27125	Garcia	Guillermo	New York
75688	Deepak	Shirguppi	Maryland

If this query is scheduled using Query Scheduler option then a new format of XFORM is available and then I can pick any transformations (can define n number of transformations) that I have defined on the query.

PeopleSoft Pivot Grid

PT 8.52 introduces the analytical tool called as Pivot Grid. Following is my first shot at using this functionality. The goal was to explore the functionality with minium setup configuration. As of now Pivot Grid supports only a PSQuery data source.

1. Created a query in Query Manager. My Query returns JPM Profile data for Employees in my HCM system.
2. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard and follow the steps in the wizard.

 















Only valid models are available from the Pivot Grid Viewer

Provide query name and select the columns from the query which will be part of this pivot grid.

At least one Axis and one Value member is required. Total column is available only for the data source column with column type Axis with data type not equal to date. For Char fields aggregate function defaults to Count and cannot be changed. If query has runtime prompts then you should see another section to provide defaults for runtime prompts. In my case there were not prompts defined on the query.

Selecting Grid only option. Specifying the Grid and Chart Axis. Kept the default Grid and Chart Options.



Preview the result. So as per my setup in Step 4 of the wizard, Name is a filter, ID and EFFDT are columns and rows are category and item description.

I can export just the specify data the grid is displaying (by clicking on grid icon in the grid title bar, next to the help icon) or all data to excel (which is similar to downloading results to excel from query).

Via Reporting Tools > Pivot Grid > Pivot Grid Viewer, an end-user can lookup the models. Though I had selected Grid only as the option, when creating my model, I do see a check-box to display a chart in addition to the grid.

 

Have the option to view it as a Bar, Line or Pie graph.

I did get some hard peoplecode errors while designing my model as I was flipping through various options, which tells me that this functionality is not robust as yet. The error messages are not at all user friendly for a functional person to understand.

So thats it for now.

Thanks

Deepak.



Drill Down Queries

PS Query in PT 8.51 now supports a new expression type called as Drilling URL. This expression type allows a user to drill down on values displayed in the result. Drill down can be done to a child query, external URL, component or attachment.
Following steps illustrate the drilling url feature using parent - child queries.

1. Create two queries, a parent query (MENU) and a child query (PANEL), so as to speak. My parent query, queries the PSMENUDEFN and PSMENUITEM tables to list the menuname, component etc and then child query lists the pages in a component (from PSPNLGROUP).
2. I will be using the drilling url expression and join Panelgroup between the two queries.
3. My child query (PANEL) takes a prompt value for the field PNLGRPNAME
4. In my parent query (MENU) I add a new expression of the type Drilling URL and choose the "Query URL" link, which takes me to the Query URL Widget
5. Specify the child query name which is PANEL, and then assign the prompt key, which is
   PNLGRPNAME.
6. Under "Map URL to Query Columns" grid I selected the PNLGRPNAME column as I want this column to be hyperlinked.
7. The expression text will look like below.

'/q/?ICAction=ICQryNameURL=PUBLIC.PANEL&BIND1=%B.PNLGRPNAME%:B.PNLGRPNAME'

Now save and run the parent query and the results will be displayed like so.

 Clicking on the component hyperlink will open the child query results like so.

Drilling URLs are supported only in RTF XMLP templates.

To use a drilling URL in a XML Publisher report:

1. Create the query with the drilling URL defined as a field. When you run the query the URL to the child query should be displayed in the results like so. 

    2. Create an RTF template. In the RTF template map the field that contain drilling URL.

• Highlight the field where you want to place the drilling URL.
• Select Insert (from the Word menu), Hyperlink or use Ctrl+K.
• In the Insert Hyperlink dialog box, enter the URL link in Address field.
  Each URL link should be defined as {URL_FIELD}, where URL_FIELD is the unique field name for the expression that contains a specific drilling URL.

• Use the Target Frame push button to select how this URL link will be opened, either in the same window or in the new window
  
  Note. The URL value does not need to be added to the report, as long as the unique field name (data file that contains the URL) is mapped to a field in the report.