Monday, October 21, 2013

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><AMOUNT>100.12</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>67890</EMPLID><AMOUNT>200.50</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>22112</EMPLID><AMOUNT>0</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>33121</EMPLID><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

Hope this helps.