10 Sep 2013
4 MINS READ
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
<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 select=”ADDRESS2″>
<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: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>
Enterprise Solutions
20 Sep 2018
18 Sep 2018
26 Oct 2015
28 Sep 2015
26 Aug 2015
11 Jun 2014
13 Feb 2014
12 Feb 2014
Enterprise Solutions, Manufacturing and Consumer
31 Jan 2014
27 Jan 2014
03 Jan 2014
03 Dec 2013
28 Nov 2013
19 Nov 2013
03 Oct 2013
01 Oct 2013
30 Sep 2013
24 Sep 2013
19 Sep 2013
27 Aug 2013
19 Aug 2013
25 Jun 2013
17 Jun 2013
11 Jun 2013
Digital Assurance, Enterprise Solutions, Others
26 Apr 2013
12 Apr 2013
18 Mar 2013
Digital Assurance, Enterprise Solutions
05 Mar 2013
27 Feb 2013
18 Feb 2013
14 Feb 2013
BI & Analytics
19 Mar 2009
26 Nov 2010
Digital Assurance
02 Jan 2012
17 Feb 2012
Digital IT Ops
02 Mar 2012
03 Jan 2013
04 Feb 2013
06 Feb 2013
21 Feb 2013
Others
01 Mar 2013
04 Mar 2013
22 Mar 2013
29 Apr 2013
13 May 2013
26 Aug 2013
03 Sep 2013
26 Sep 2013
01 Nov 2013
20 Dec 2013
22 Jan 2014
20 Mar 2014
24 Mar 2014
17 Apr 2014
Manufacturing and Consumer
26 Jun 2014
30 Jun 2014
10 Jul 2014
15 Jul 2014
16 Jul 2014
18 Jul 2014
28 Oct 2014
13 Jul 2015
06 Aug 2015
07 Oct 2015
07 Mar 2016
22 Mar 2016
13 May 2016
23 May 2016
Cloud Transformation
11 Jul 2016
25 Aug 2016
03 Sep 2016
14 Sep 2016
15 Nov 2016
22 Nov 2016
25 Nov 2016
Business Process Services
25 Apr 2017
Banking and Financial Services
18 May 2017
30 May 2017
23 Jun 2017
27 Jun 2017
18 Jul 2017
26 Oct 2017
Healthcare, Insurance
28 Nov 2017
11 Dec 2017
25 Jan 2018
21 Feb 2018
14 Mar 2018
( Mandatory field * )
The information you provide will be used in accordance with our terms ofPrivacy Policy
Please Check on "I Agree" to register for the blog.