ERP BI - Case study 1 - Create RTF Template

Here we will create a rtf template based on a data model xml payload and create a pdf BI report based on the created rtf template.

Please follow my previous blog to download BI publisher desktop tool and add-ins in ms word.

https://soalicious.blogspot.com/2022/03/erp-bi-publisher-installation-and.html

Implementaion steps:

Create a data model and export the xml data to create RTF template and save the sample payload and save the data model.

Export the xml data and save As sample data.

Based on the export xml data we will create rtf template in ms word.

Save the ms word in .rtf

Insert  >> header 

Insert >> table

BI Publisher >> sample XML

Table wizard

Preview >> PDF

Create BI report using the created rtf template:

ERP - BI report XML output using XSL template

Usecase: 

Here, we will create a custom fields XML Output BI report using XSL template.

Check my previous blog how to create BI report using xsl template

https://soalicious.blogspot.com/2022/03/create-bi-report-using-xsl-template.html

.xsl template used:

Syntax1:

<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">

<document>

<xsl:element name="SupplierData">

<xsl:for-each select="DATA_DS/G_1">

<xsl:element name="Supplier">

<xsl:element name="SUPPLIER_NAME">

<xsl:value-of select="SUPPLIER_NAME"/>

</xsl:element>

<xsl:element name="SUPPLIER_NUMBER">

<xsl:value-of select="SUPPLIER_NUMBER"/>

</xsl:element>

<xsl:element name="SUPPLIER_ID">

<xsl:value-of select="SUPPLIER_ID"/>

</xsl:element>

<xsl:element name="COUNTRY">

<xsl:value-of select="COUNTRY"/>

</xsl:element>

<xsl:element name="CITY">

<xsl:value-of select="CITY"/>

</xsl:element>

<xsl:element name="STATUS">

<xsl:value-of select="STATUS"/>

</xsl:element>

</xsl:element>

</xsl:for-each>

</xsl:element>

</document>

</xsl:template>

</xsl:stylesheet>

Syntax2:

<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/">

<document>

<SupplierData>

<xsl:for-each select="DATA_DS/G_1">

<Supplier>

<SUPPLIER_NAME>

<xsl:value-of select="SUPPLIER_NAME"/>

</SUPPLIER_NAME>

<SUPPLIER_NUMBER>

<xsl:value-of select="SUPPLIER_NUMBER"/>

</SUPPLIER_NUMBER>

<SUPPLIER_ID>

<xsl:value-of select="SUPPLIER_ID"/>

</SUPPLIER_ID>

<COUNTRY>

<xsl:value-of select="COUNTRY"/>

</COUNTRY>

<CITY>

<xsl:value-of select="CITY"/>

</CITY>

<STATUS>

<xsl:value-of select="STATUS"/>

</STATUS>

</Supplier>

</xsl:for-each>

</SupplierData>

</document>

</xsl:template>

</xsl:stylesheet>

Dont forget to change the default format from html to xml.

Output report:

For multi group sample:

ERP - Create a BI report text output using XSL template

Usecase: 

Here, we will create a text Output BI report using XSL template. 

Why we should use xsl template?

• XSL Stylesheet is generally used to generate text, xml, html and FO.
• Rich in xslt function like format-number(), count(), sum() etc.
• user friendly syntax in xml format.

.xsl template:

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

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:exslt="http://exslt.org/common"

xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.xdo.template.rtf.XSLTFunctions"

xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>

<xsl:template match="/">

<xsl:text>supplier_name</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>supplier_number</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>supplier_id</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>country</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>city</xsl:text>

<xsl:text>,</xsl:text>

<xsl:text>status</xsl:text>

<xsl:text>&#xa;</xsl:text>

<xsl:for-each select="DATA_DS/G_1">

<xsl:value-of select="SUPPLIER_NAME"/>

<xsl:text>,</xsl:text>

<xsl:value-of select="SUPPLIER_NUMBER"/>

<xsl:text>,</xsl:text>

<xsl:value-of select="SUPPLIER_ID"/>

<xsl:text>,</xsl:text>

<xsl:value-of select="COUNTRY"/>

<xsl:text>,</xsl:text>

<xsl:value-of select="CITY"/>

<xsl:value-of select="STATUS"/>

<xsl:text>&#xa;</xsl:text>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Data model query used:

Select hp.party_name supplier_name,

poz.segment1 supplier_number,

poz.vendor_id supplier_id,

hp.email_address,

hp.country,

hp.city,

hp.status

from POZ_SUPPLIERS poz, HZ_PARTIES hp where 1=1 

And poz.party_id = hp.party_id

And rownum<=10

Detailed steps with Screenshots:

WSDL - Generic interface for anytype xml

 Generic interface for parent flow. The wsdl should support any type of payload:

<definitions xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://www.w3.org/ns/ws-policy" xmlns:wsp1_2="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsam="http://www.w3.org/2007/05/addressing/metadata" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:tns="http://mock.service.com/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.xmlsoap.org/wsdl/" targetNamespace="http://mock.service.com/" name="EchoMultiOpsImplService">
  <types>
    <xs:schema xmlns:tns="http://mock.service.com/" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0" targetNamespace="http://mock.service.com/">
      <xs:element name="echoAny" type="tns:echoAnyType"/>
      <xs:element name="echoAnyResponse" type="tns:echoAnyType"/>
      <xs:complexType name="echoAnyType">
        <xs:sequence>
          <xs:element name="xml" type="xs:anyType"/>
        </xs:sequence>
      </xs:complexType>
    </xs:schema>
  </types>
  <message name="echoSingle">
    <part name="parameters" element="tns:echoAny"/>
  </message>
  <message name="echoSingleResponse">
    <part name="parameters" element="tns:echoAnyResponse"/>
  </message>
  <portType name="EchoAny">
    <operation name="echoAny">
        <input wsam:Action="http://mock.service.com/echoSingle" message="tns:echoSingle"/>
        <output wsam:Action="http://mock.service.com/echoSingleResponse" message="tns:echoSingleResponse"/>
    </operation>
  </portType>
</definitions>