PeopleSoft Query is an end user reporting tool for PeopleSoft users to enable them with precise information which exists in databases. By using PS Query reporting, users can create simple as well as complex queries without the use of SQL and database tools. This is one of the most common reporting tools used by PeopleSoft users to get desired results for every single or multiple ad-hoc queries repeatedly. Queries created using PS Query tool serves as a critical data source for various other reporting tools such as Crystal reports, BI Publisher and nVision.
New features have been added in PeopleTools 8.53 to provide more flexibility in generating reports using PS Queries. For example, in situations where users reporting needs are based on complex SQL which might include more than 26 records, PS Query under the latest PeopleTools version 8.53 addresses this business need which was not part of earlier versions. The earlier versions faced limitations pertaining to the restriction of maximum 26 records. (including records used in union and sub queries). The record alias goes from A to Z from record number 1 to 26. If users require such kind of complex reports then they would need to go with other reporting tools such as SQR Reports which requires expertise in that reporting tool.
PeopleTools 8.53 allows usersto select and add up to 676 records for each query. The record alias goes from A to Z for record number 1 to 26. When you add record number 27 and after, the record alias starts at AA and follows the pattern AB, AC, AD, and so on, ending with ZX, ZY, ZZ. This new flexible feature provides add on comfort to users rather than depending on other reporting tools for their requirement.
Similarly if any user require amendments in any existing PS query in such a way that they use the left outer join then in earlier versions of PS Queries users were restricted in using this join only with the last record which exists in that PS Query. If they want to use the left outer join with the record which is not the last record in the PS Query then they need to delete the record, and add this to query again to make it the last record.
This functionality limitation has been addressed inPeopleTools 8.53 version, enabling users to use left outer join with any record that exists in the PS Query.
In PeopleTools 8.53 users can generate output using PeopleSoft Query in user-defined output format with XFORM, which results a transformation (XSLT). Users must provide the XSLT to define the desired transformation of the extracted data. After defining transformation to generate the transformed output and distribute the data results, users can schedule the query process to run the same using the Process Scheduler Request page and select XFORM as the output format. If users want to send the transformed output to an external IB node using the Integration Broker frameworks, then they need to schedule the query process to run it using the Process Scheduler Request page, select the IB Node as the output type and XFORM as the output format. New schedule page also features a new ‘Approval Required’ option that enables users to review and approve the transformed output before distributing it to the target audience.
Below is one of the examples for leveraging XFORM, users can easily generate mailing addresses for vendors which could be used for sending any mailers or documents
Steps to create PS Query using Transformations feature of PeopleTools 8.52
- Create PS Query
- Go to Transformations tab and click on “Add XSLT” button and provide “Name”, “Output File Type” and “XSLT” code and click on OK button.
- XSLT code should be valid. Below is the sample XSLT code for the Demo Query.
| <?xml version=’1.0′?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”https://www.w3.org/1999/XSL/Transform”>
<xsl:output method=”text”/>
<xsl:template match=”query/row”>
<xsl:for-each select=”NAME1″>
To, <xsl:value-of select=”.”/>
</xsl:for-each>
<xsl:text>
</xsl:text>
<xsl:for-each select=”ADDRESS1″>
<xsl:value-of select=”.”/>
</xsl:for-each>
<xsl:text>
</xsl:text>
<xsl:for-each select=”ADDRESS2″>
<xsl:value-of select=”.”/>
</xsl:for-each>
<xsl:text>
</xsl:text>
<xsl:for-each select=”CITY”>
<xsl:value-of select=”.”/></xsl:for-each><xsl:for-each select=”STATE”> , <xsl:value-of select=”.”/></xsl:for-each>
<xsl:for-each select=”POSTAL”>
PIN: <xsl:value-of select=”.”/></xsl:for-each>
<xsl:text>
</xsl:text>
<xsl:for-each select=”COUNTRY”>
<xsl:value-of select=”.”/></xsl:for-each>
<xsl:for-each select=”COUNTRY_CODE”>
PHONE:<xsl:value-of select=”.”/></xsl:for-each><xsl:for-each select=”PHONE”> – <xsl:value-of select=”.”/></xsl:for-each>
</xsl:template>
</xsl:stylesheet>
|
- Once you click on OK you can see added user defined output getting listed.
- Click on the Preview button to review output of the PS Query in the user defined format.
- Getting Query Result in Defined Transformation form:
- While scheduling this kind of PS Query, you can chose output format as “XFORM” . Once you choose the output format as “XFORM” all the transformations which you have added for the PS Query would be listed in the new field “Transformation”. Choose any of them to your desired output.
- Once the process is complete you can see the output of PS Query which was created in the format selected while creating transformation
