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.   

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.