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.

XML Parser

XML related API has been around for some time now in PeopleSoft and this post is not discussing anything new. Just something that I wanted to pen down for future reference. 

So recently I have been working on a requirement where in I have to parse out a XML response and fetch data values in order to process them further. In this case the same tag element names repeat throughout the XML message and there are multiple levels in the message. The requirement was to traverse each "item" tag and fetch the value for "key" and "value" tags.

Format of the XML message is as shown below.

<?xml version="1.0" encoding="UTF-8"?> <root> <level1> <level2> <level3> <item> <key>FieldName1</key> <value>Value1</value> <type>String</type> </item> <item> <key>FieldName2</key> <value>Value2</value> <type>Date</type> </item> <item> <key>FieldName3</key> <value>Value3</value> <type>Number</type> </item> </level3> </level2> </level1> </root>



Wrote two methods, first one is way longer than the second one.
For both methods I am passing the "field_name_value" and getting back the &fieldvalue value.


Method one - longer.


Local XmlDoc &NewHireXMLDoc;
Local XmlNode &root, &level1, &level2, &level3, &item;
Local number &z, &y, &x, &w, &v;
Local string &sResp, &fieldvalue;
Local boolean &bRet;


rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);

&rootNode = &NewHireXMLDoc.DocumentElement;

For &z = 1 To &rootNode.ChildNodeCount
   If &root.GetChildNode(&z).NodeName = "level1" Then
      &level1= &root.GetChildNode(&z);
      For &y = 1 To &level1.ChildNodeCount
         If &level1.GetChildNode(&y).NodeName = "level2" Then
            &level2= &level1.GetChildNode(&y);
            For &x = 1 To &level2.ChildNodeCount
               If &level2.GetChildNode(&x).NodeName = "level3" Then
                  &level3 = &level2.GetChildNode(&x);
                  For &w = 1 To &level3.ChildNodeCount
                     If &level3.GetChildNode(&w).NodeName = "item" Then
                        &item = &level3.GetChildNode(&w);
                        For &v = 1 To &item.ChildNodeCount
                           If &item.GetChildNode(&v).NodeName = "key" And
                                 &item.GetChildNode(&v).NodeValue = "field_name_value" Then
                              &fieldvalue = &item.GetChildNode(&v + 1).NodeValue;
                              &v = &v + 1;
                           End-If;
                       End-For;
                     End-If;
                  End-For;
               End-If;
            End-For;
         End-If;
      End-For;
   End-If;
End-For;



Method two - shorter

Local XmlDoc &NewHireXMLDoc;
Local string &sResp, &fieldvalue;
Local boolean &bRet;

Local array of XmlNode &items;
Local number &y, &z;

rem &sResp is the XML message in string form;
   
&NewHireXMLDoc = CreateXmlDoc();
&bRet = &NewHireXMLDoc.ParseXmlString(&sResp);


&items = &NewHireXMLDoc.DocumentElement.FindNodes("level1/level2/level3/item");
   
   For &z = 1 To &items.Len
      For &y = 1 To &items [&z].ChildNodeCount
         
         If &items [&z].GetChildNode(&y).NodeName = "key" And
               &items [&z].GetChildNode(&y).NodeValue = "field_name_value" Then
            &fieldvalue = &items [&z].GetChildNode(&y + 1).NodeValue;
            Break;
         End-If;
      End-For;

   End-For;

Query Transformations

I rarely do much work in PS Query, but while doing some other troubleshooting for PS Query related issues, I noticed the "Transformations" tab in Query Manager. I am running PT 8.56.x in HCM 9.2, but looks like this has been around since 8.53 or 8.54. 

So decided to do a quick test to see what this does and how it works. So the benefit here is that the output can be transformed to create reports right out off the output delivered by PS Query.

So as part of this POC used any existing query. Transforming the output to HTML and using a XSL transformation to highlight the row when a certain condition is matched.

On the Transformation tab within Query Manager, click on Add XSLT which opens a window to key in free-form XSLT text. It would have been nice if there is a wizard of some sort to build the XSLT. 

Gave it a Name and in the "Output File Type" lookup selected HTML. Following are the available choices - 001, A01, ABA, AET, APC, CSS, CSV, D01, DAT, DTD, DTL, ERR, GIF, GIR, HTM, HTML, IDX, JCD, JS, LIS, LOG, MDL, MT9, N01, NVT, NXT, OUT, PS, SQL, STDOUT, SYS, TMP, TRC, TRN, TXT, XLSM, XLSX, XML, XSD, ZIP

Not sure what is the difference between HTM and HTML, but I selected HTML. My sample XSLT is designed as follows.

<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <html>
<!-- defined a style for the row that is going to be highlighted -->
<style type="text/css">
tr.redRow td {
font-size: 100%;
font-family: calibri;
font-weight:bold;
background-color: #FF2626;
padding-right: 10px;
color:#000;
}
</style>
<body>
<!-- in the html body defined a html table --> <table border="1">
<!-- Added a heading --> <font color="blue"> <h2>Sample Transformation test</h2> </font>
<!-- defined column headers --> <tr> <th>Employee ID</th> <th>Last Name</th> <th>First Name</th>
<th>Location</th> </tr>
<!-- Here I am selecting the data from PS Query --> <xsl:for-each select="query/row"> <tr>
<!-- defined a if condition to check if -->
<!-- value is McLean then highlight the row --> <xsl:if test="L.DESCR='McLean'"> <xsl:attribute name="class">redRow</xsl:attribute> </xsl:if>
<!-- Output the PS Query results --> <td> <xsl:value-of select="A.EMPLID" /> </td> <td> <xsl:value-of select="C.LAST_NAME" /> </td> <td> <xsl:value-of select="C.FIRST_NAME" /> </td> <td> <xsl:value-of select="L.DESCR" /> </td> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

So saved this and hit the preview button to preview the results. My query uses runtime prompts so those pop'ed up first, provided values for them and results are displayed in a new window (disable pop-up blocker or allow pop-up for this site).

Sample Transformation test

Employee ID	Last Name	First Name	Location
12345	Doe	John	Madrid
13244	Doe	Jane	Egypt
43423	Hufton	Abcde	London
42343	Fleming	Jill	McLean
34423	Tibold	Jozsef	London
67554	Farrell	William	Johannesburg
43556	Myers	Tony	Ireland
75757	Kramer	Nicole	McLean
27341	Wada	Mark	New York
27045	Roger	Wai	McLean
27098	Wayne	Jessica	New York
27125	Garcia	Guillermo	New York
75688	Deepak	Shirguppi	Maryland

If this query is scheduled using Query Scheduler option then a new format of XFORM is available and then I can pick any transformations (can define n number of transformations) that I have defined on the query.

Sending and Receiving MTOM-encoded binary data

PeopleSoft supports the MTOM protocol for sending and receiving binary data using service operations. While you can send and receive binary data using SOAP, doing so requires that you Base64-encode the data, which can increase message size by 33 percent or more. The MTOM protocol enables you to send and receive binary data in its original binary form, without any increase in size due to encoding.

For sending or receiving MTOM-encoded binary data, we have to use message segments to store the data. The SegmentContentType property of the message object is used to set or read the content type of each message segment.

Following is a test that I did to send a XML file as an attachment in a SOAP message and then read the attachment that is sent by the 3rd party system that I am interacting with. I am running PT 8.53.22.

Sending:

Request message is as shown below. For this test I am storing this in a html object called as MY_MESSAGE but this can be generated dynamically as needed using SOAPDoc or XMLDoc classes. The request message defined on the service operation is nonrowset based. 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

<soapenv:Header/>

<soapenv:Body>

<submitLargeDocument>        

    <Document>  

         <PsftXopInclude SegmentNumber='1'/> 

    </Document>      

</submitLargeDocument>

</soapenv:Body>

</soapenv:Envelope>

document.xml is my payload which I have created in a different routine and I am just using it here. Its just like creating any other XML file in PeopleSoft. Payload can be a binary file like a pdf or a image file.

Sender node is my default local node and receiving node is the delivered WSDL_NODE. You can always create a custom receiving node if needed. Routing is using local gateway and HTTPTARGET connector. Under routing, connector properties content-type is set to text/xml as my payload is a xml file, HTTPPROPERTY MTOM is set to Y, METHOD is POST, SOAPUpContent is set to N as I have already built the SOAP wrapper in my html object above. If you need IB to create the wrapper then set this property to Y. Provided PRIMARYURL to destination 3rd party application. Took all other defaults.

PeopleCode:

&str = GetHTMLText(HTML.MY_MESSAGE);

&requestXMLDoc = CreateXmlDoc();

&ret = &requestXMLDoc.ParseXmlString(&str);

&request = CreateMessage(Operation.SEND_OPERATION);

&request.SetXmlDoc(&requestXMLDoc);

&MTOMFile = GetFile("C:\temp\document.xml", "R", %FilePath_Absolute);

If &MTOMFile.IsOpen Then   

   &theBase64encodedString = &MTOMFile.GetBase64StringFromBinary();   

   &MTOMFile.Close();

End-If;

&request.CreateNextSegment();

If (&request.SetContentString(&theBase64encodedString)) Then   

    &request.SegmentContentType = "application/xml";  

    &request.SegmentContentTransfer = %ContentTransfer_Binary;

End-If;

&response = %IntBroker.SyncRequest(&request);

Receiving:

Response message defined on the service operation is a non-rowset based message. Sender node is the default local node and receiving node is WSDL_NODE. Using local gateway and HTTPTARGET connector. Setting HEADER properties Content-Type to text/xml as the response attachment that I am receiving is a xml file, sendUncompressed is Y, HTTPPROPERTY Method is POST and SOAPUpContent is Y and finally the PRIMARYURL to the 3rd party service.

On the weblogic webserver, in the integrationGateway.properties file enable the MTOM Listening Connectors. 

ig.MTOM.enablePeopleSoftServiceListeningConnector=true

ig.MTOM.enableHttpListeningConnector=true

Bounce the webserver after making this change.

PeopleCode:

This is pretty straightforward. Once the request is made, read the response and parse out the document.

&response = %IntBroker.SyncRequest(&request);

&responseXMLDoc = &response.GetXMLDoc();

If (&response.ResponseStatus = 0) Then

     &dataNode = &responseXMLDoc.DocumentElement.GetElementByTagName("data");

     &theData = &dataNode [1].GetCDataValues();

     &responsestr = &theData.Shift();

End-If;

The &responsestr string variable will have the response SOAP envelope as well as the attachment separated by message segments as shown below. Parsed it out using string functions.

<?xml version="1.0"?>

<data psnonxml="Yes">

  <![CDATA[

------=_Part_624_1792156364.1458048147094

Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"

Content-Transfer-Encoding: 8bit

Content-ID: <soap.xml@xfire.codehaus.org>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

<soap:Body>

.......

.......

.......

</soap:Body>

</soap:Envelope>

------=_Part_624_1792156364.1458048147094--

------=_Part_724_1792156364.1458048147094

Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"

Content-Transfer-Encoding: 8bit

Content-ID: <soap.xml@xfire.codehaus.org>

<Document>

........

........

........

</Document>

------=_Part_724_1792156364.1458048147094--

]]>

</data>