Generating excel output with multiple sheets using RTF Template

So as the title suggests, in this exercise I am testing PeopleSoft's BI Publisher reporting engine to output an excel file, while using a RTF template. In addition to producing the output in excel format, I have to split the result into multiple sheets within the same excel workbook. So lets get started.

For this POC I am using Oracle BI Publisher Desktop 12.2.1.4.0. 

I am displaying a simple table with two columns on each sheet within the workbook. In order to name the sheet within the workbook I am using the following form field tag

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?> where N_SHEET_NAME is a data element in my XML data source.

My XML data source is structured as shown below.

 

In my RTF template, I have the following two tables separated by a page break. So the first table is on page 1 of the RTF file and the second table is on page 2.

Table 1 form field definitions have been defined as below.

F = <?for-each:N_PERSON_DATA?> 

E = <?end for-each?>

Below the table I have defined the following tags.

<?for-each-group:N_PERSON;./N_SHEET_NAME?>

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?>

<?end for-each-group?>

After this there is page-break which was created by Ctrl + Enter.

Table 2 follows a similar construct as explained below.

F = <?for-each:N_ADDRESS_DATA?>

E = <?end for-each-group?>

<?for-each-group:N_ADDRESS;./N_SHEET_NAME?>

<?spreadsheet-sheet-name: {.//N_SHEET_NAME}?>

<?end for-each-group?>

Excel output below.

Sheet 1

Sheet 2

XMLP BI Publisher sub-templates

Recently was presented with this challenge which turned out to be good enough exercise and hence warranted this post. Request was to display appropriate logo in the report header based on data available in the source XML file. 

For this exercise I am running PT 8.58.05 and BI Publisher 12.2.1.4.0 and rtf templates. My main report is called parent.rtf, sub-template is called headers.rtf and data source xml file is data.xml. The goal is to print or display the appropriate logo in the final report based on the data value provided in the XML file. 

In the main report, under MS Word's heading section added the following code. This is in regular text and not in any form field. Instead of choose (which works like peoplecode evaluate) I could have also used "If - Then - Else", but I wanted to try how "choose" works in XMLP.

<?import:file:C:///temp/headers.rtf?>
<?choose:?>
<?when:logo='Oracle'?>
    <?call:Oracle?>
<?end when?>
<?when:logo='Microsoft'?>
    <?call:Microsoft?>
<?end when?>
<?when:logo='Apple'?>
    <?call:Apple?>
<?end when?>
<?otherwise:?>
    <?call:Default?>
<?end otherwise?>
<?end choose:?>  

While testing this rtf locally or via Template Viewer the following import command will work, provided the headers.rtf file is available under c:\temp folder.

<?import:file:C:///temp/headers.rtf?>

But once we are ready to deploy and run this in the PIA environment, the line has to be changed as shown below. Here "STDHEADER" is the name provided while creating/uploading the headers rtf template in PIA under content library.

 

<?import:psxmlp://STDHEADER?>

In the headers.rtf I have defined the various headers as shown below. The XML tags are in regular text and not form fields.

<?template:Oracle?>

Oracle image was added here.

<?end template?>

<?template:Microsoft?>

Microsoft image was added here.

<?end template?>

<?template:Apple?>

Apple image was added here.

<?end template?>

<?template:Default?>

A default image was added here.

<?end template?>

My data source data.xml has the following structure where in I have the value for the logo to be used.

<?xml version="1.0" encoding="UTF-8"?>

<Start>

   <Heading>

      <logo>Apple</logo>

   </Heading>

   <ReportData>

      ...

   </ReportData>

</Start>

If you are receiving errors while testing this solution locally then the following has to be done. Under your local workstation install of the BI Publisher plugin you will have to modify the xdo.cfg file and add the following line under the properties section.

if no xdo.cfg file exists then make a copy of xdo example.cfg as xdo.cfg and add the line. This property is available in Template Viewer, so you can just set it there if testing via template viewer.

<property name="xdk-secure-io-mode">false</property>

That's it works like a charm.

PeopleSoft BI Publisher Pivot Wizard

The following explains how the crosstab wizard builds the crosstab and what the fields inside the resulting template structure are there for.

To create the crosstab a new XDO command "<?crosstab:...?>" has been created.
XDO Command: <?crosstab: ctvarname; data-element; rows; columns; measures; aggregation?>

Ctvarname - Crosstab variable name. This is automatically generated by the Add-in. e.g. c72515

data-element - This is the XML data element that contains the data. e.g. "//Payroll"

Rows - This contains a list of XML elements for row headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the row header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
e.g. "EMPLID{,o=a,t=t}, NAME{,o=a,t=t}"

In the example, the first row header is "EMPLID". It is sort by "EMPLID", order is ascending, and type is text. The second row header is "NAME". It is sort by "NAME", order is ascending, and type is text.

Columns - This contains a list of XML elements for columns headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the column header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.

There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
e.g. "CHECK_DT{,o=a,t=t}"

In the example, the first column header is "CHECK_DT". It is sort by "CHECK_DT", order is ascending, and type is text.

Measures - This contains a list of XML elements for measures. e.g. "ANNUAL_RT"

Aggregation - The aggregation function name. Currently, we only support "sum" and “count”. e.g. "sum"

Using the Oracle BI Publisher Template Builder for Word add-in, we are able to construct the following Pivot Table:

Row layout can be Inline or Outline.

Row/Column totals

Table defined in RTF template

C H ACCEMPLID	NAME	G CHECK_DTE	Total
G RS EMPLID	G NAME	G 999E	999E
		G 999E	999

Code generated by the Pivot Wizard

XML file

……

……

……

Output

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.