XSL Template in BI Publisher (PeopleSoft)

Here is a quick tip on how to create and use XSL template via BI Publisher in PeopleSoft.
For this test I am using PeopleTools 8.52.x but I am sure this is even possible in older releases like 8.49.x and above.

When you create a new Report defn, pick XSL as the template type.

1. Using XRFWIN as the data source create a new report defn

2. Pick XSL as the template type

3. For the XSL template, either you can manually create a file manually or some other web based app or use the following steps to generate one for you.

Take the delivered xrfwin.rtf RTF template.

Create a dir, lets say c:\temp\xmlp and copy it here. Copy the jlif dir from your local install of the BI Publisher Desktop. (C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib)

So you will have c:\temp\xmlp\jlib

Now via cmd line from under c:\temp\xmlp execute the following.

javaw -Duser.language=en -Duser.region=US -Xmx256M -jar ".\jlib\RTF2PDF.jar" "xrfwin.rtf" "dummy" "." "XSL" "TRUE" "en-US" "dummy" "dummy" "." ""

This will create two sub-dirs log and tmp. The xml file under tmp will be you xsl template. Upload this to report defn and verify.

eText Gotcha's

I have been using eText BI Publisher templates for generating extract files for PeopleSoft data for a while now. Generally I would do all the filtering/transformation of data within PeopleSoft and generate a well-formatted XML file to be read directly without much transformations done in the template itself.

Recently, I had to use the same XML file for an eText template as well as a RTF report template with slight differences in the output. After doing a lot of hunting around found a very good trick to perform data filtering within the eText template.

So in this case, I had to filter out data when the amount is less than equal to zero on the extract (but include that data in the report)

My XML file was pretty straight forward, had a header tag, detail tags and trailer tag, something like below.

<Start>
<N_HEADER>
<RUN_ID>PR13-07-05</RUN_ID><CREATION_TIME>12:36:36</CREATION_TIME>
</N_HEADER>
<N_DETAIL>
<EMPLID>12345</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>100.12</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>67890</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>200.50</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>22112</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>0</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>33121</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>6100.45</AMOUNT>
</N_DETAIL>
<N_TRAILER>
<TOTAL_AMT>6401.07</TOTAL_AMT>
</N_TRAILER>
</Start>

Following is my eText template (Detail record). Highlighted changes in red.

<LEVEL>		N_DETAIL [AMOUNT > 0]
<POSITION>	<LENGTH>	<FORMAT>	<PAD>	<DATA>	<COMMENTS>
<NEW RECORD>		Detail

.....
.....
.....

<END LEVEL>

N_DETAIL [AMOUNT > 0]

That did the trick, so EMPLID 22112 is automatically filtered out.

Another requirement was to report row counts in the trailer record. So instead of count it within the peoplecode and store it in the XML file, I could use the COUNT function as below.

8

5

Number

COUNT(Detail)

Total # of detail records in file

The argument "Detail" to the COUNT function is the New Record value defined for the detail section. If I have to count the header and detail rows also then I can do something like COUNT(Detail)+2, as I know I will have only one header and detail row in my file.

There are other functions like SYSDATE to print the current system datetime stamp.

3

10

Date, YYYY/MM/DD

SYSDATE

Current Date

So I could something like above and format it as YYYY/MM/DD to print the current date. I could also print current time as follows.

3

10

Date, HH24:MI:SS

SYSDATE

Current Time

This prints the time in 24hr clock. This is always GMT/UTC time.

Another function that I have used often is the FORMAT_NUMBER function. So I had a requirement to print number without the decimals, as a continuous string persay, with leading zeros.

FORMAT_NUMBER(AMOUNT*100,'0')

So with reference to the above example, if I had 9 positions to print the numerical amount value then

<EMPLID>12345</EMPLID><AMOUNT>100.12</AMOUNT>

will be printed as 000010012

Another requirement was to sum up the amounts from the detail and write the total to the trailer. 
One method is to compute the amount upfront and write to the XML file trailer tag. Another way (shown below) is to compute the value in the eText template.
Add a "Format Data Levels" table as shown below, after the "Format Setup".

Format Data Levels:

Hint: Define data levels that are needed in the format which do not exist in data extract…

<DEFINE LEVEL>	N_TRAILER
<BASE LEVEL>	N_DETAIL
<GROUPING CRITERIA>	CHECK_DT
<END DEFINE LEVEL>	N_TRAILER

....
....
....

<LEVEL>		N_TRAILER
<POSITION>	<LENGTH>	<FORMAT>	<PAD>	<DATA>	<COMMENTS>
<NEW RECORD>		Trailer
1	2	Alpha		‘T’	Record Type
3	9	Number		COUNT(Detail)	Record Count
12	17	Number		SUM(AMOUNT)	Total Amount from Detail

....
....
....

<END LEVEL>

N_TRAILER

Hope this helps.