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

Comments

Post a Comment