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.