Friday, 31 July 2015

Date formatting in PeopleSoft Pagelets

Following on from last weeks post on number formatting in Query you may have noticed that date and datetime fields display with different formats in PeopleSoft Query and Query Pagelets .  See Fig. 1 & 2 below for examples of this difference.

Fig. 1. Pagelet Date Formats (note the raw native dates)

Fig. 2. Query Date Formats (note the correct formatting)

So, why the inconsistency in formatting?  Well, I think this has to do with the XML document generated as source data for the Pagelet Wizard.  One of the issues with this XML is that it contains no field data type declarations.  Consequently the Pagelet Wizard XSL cannot distinguish XML element values that are dates from any other type of field.  See example XML source data in Fig 3.

Fig. 3. XMLQuery source data (no field type declarations)
You may have noticed that when running a PS Query to XML through Query Manager/Viewer there is also an absence of field declarations and also has date time values in native, raw unformatted text.  However, Query Manager/Viewer takes care of the date formatting for us.

So, if you want to format dates in your Pagelet results then you can use the same tag as used to format numbers.   PeopleBooks describes XSL ost transformation processing  here.

We first need to write some XSL which will identify the field you want to format.  What you write will depend on the method used by the XSL to process the XML.  For example if you are using the delivered Bulleted List XSL template then there is an iteration of the XML document using an
 <xsl:for-each ...=""> </xsl:for-each>  
construct.  This will apply the same XSL to every element in the querdata result set.  So to pickout just the date fields we need to test each field.   So, add an
 <xsl:when ...="" condition="" test="expression"> </xsl:when>  
 logic to identify the nth field.  An example of this is shown in Fig 4.

Fig. 4. Bulleted List iteration of XML querydata
In addition to testing column ordinal position (which of course could change if you add/remove fields from the). you can test for field names using the code in Fig.5. The field name is stored as an  attribute of the querydata element.  (read more on XPATH functions like position() here https://developer.mozilla.org/en-US/docs/Web/XPath/Functions/position).

Fig. 5. Evaluate fieldname attribute in the querydata element
If you're using the table layout XSL template then the XSL becomes a little more involved.  When working with XML I always like to format and syntax highlight.  I use these tools often


Anyway, back to XSL for Table based layouts in pagelets.  You'll find a block of XSL for each column so you won't need to try and work out which field your processing.  This makes targeting only the date fields easier.  Fig. 6 shows the PSFORMAT tag added into the XSL for table based Pagelet.

Fig. 6. PSFORMAT added to table based XSL template (note the position()=2 selector)

Hope you find this helpful and have as much fun with it as I did.

Monday, 20 July 2015

Number formatting in Pivot Grids and Pagelet Wizard

(1) Pivot Grid without number formatting
I recently had to solve a Pivot Grid and Pagelet Query number formatting problem.  By default numbers are not formatted to show thousand separator or currency symbols.  Without this common styling it's much harder to read large numbers.

I found this problem exists in PeopleSoft Query, Pivot Grids and Pagelets that render Query output.


Number Format in Pivot Grid

To control number formatting in a Pivot Grid model you need to include a Currency Code field in your Pivot Grid Query.  The value in this field is then used to drive currency symbol and thousand and decimal characters.  This field can then be used in Step 3 of the Pivot Grid Wizard to control currency symbol and/or thousand separator.  The PeopleSoft 3 character currency code is used to lookup currency symbol and the precision level at runtime from table CURRENCY_CD_TBL.

(2) Set Currency Control Field in Formatting Options.

Number Format in Pagelet Query

The Pagelet Wizard allows you to customise the XSL used to transform the Query XML data into HTML.  So, the obvious solution here is to use XSL language to handle the number formatting. After  all that's what XSL is for.  We could use an XSL function format-number() however in testing this in PeopleTools 8.54.08 I found it didn't have any effect (See example in screenshot (5) below) .  If I take the XML and XSL and do the transform outside of PeopleSoft using something like http://www.freeformatter.com/xsl-transformer.html or XML/Spy then it works just fine.  I don't know why the PeopleSoft XSL transform engine is not correctly processing this function.  So I asked around and a friend of mine pointed me to a blog entry by PeopleSoft Blogger David who refers to Pagelet Wizard Post-Transformation Processing in which there are some very useful post processing tags.  Using the delivered PeopleSoft PSFORMAT tag has a number of advantages over the standard format-number() function in that it is aware of PeopleTools meta data and can automatically deal with such things as inserting the correct currency symbol and the precision level (did you know that the Kuwaiti, Jordanian and Tunisian Dinar has 3 decimal places?)

 You can see the effect of this in the screenshots below (3).  I've also added align="right" to the table div so that it right aligns my numbers.  I've only targeted columns that I want to format (ie not Year values)  and these are in column 6 and 7.

       <xsl:choose>  
         <xsl:when test="position()=1">  
             <PSFORMAT TYPE="NUMBER" FORMAT="%10.2v"><xsl:value-of select="text"/></PSFORMAT>  
         </xsl:when>  
         <xsl:otherwise>  
             <xsl:value-of select="text"/>  
         </xsl:otherwise>  
        </xsl:choose>  



(3) Using the PSFORMAT post processing tag to format numbers

(4) Example of Formatted and Unformatted Pagelets
In the Table XSL template each field in the querydata collection is processed one at a time and are numbered in the XSL text.  However, other XSL templates such as the Bulleted List use  < xsl:for-each select="text" > to iterate through the querydata values.  Again, you'll need to test for specific columns to apply format to only the number fields.

(5) Use the position() function to identify specific columns