Sum function in xml data Template

Sum function in xml data Template

Hi Friends, We are going to discuss about the Sum function in xml data Template . We will show you the method or way to use the sum function in xml data template. We will share some example to use the sum function in the sql query of xml data template. We will create xml report using data template and then will try to use the sum function in the sql query itself in data template. Sum function we do use if we want to show the summary amount. In Data Template , We can use the sum function in the sql query to show the summary values. Please find below the detail about Sum function in xml data Template.

Steps to use the Sum function in xml data Template

Step 1:-  Here below , we are sharing the xml date template file in which we are using the sum function at data/sql query level.

Sum function in xml data Template

Step 2:- Here is the data template xml file code itself to use the sum function in the xml data template as below.



<?xml version="1.0" encoding="UTF-8" ?>
 <dataTemplate name="SupInvData" description="Supplier Invoice Details"
   Version="1.0" >
 <properties>
  <property name="debug_mode" value="on"/>
 </properties>
 <parameters>
  <parameter name="P_VENDOR_ID" dataType="character" />
 </parameters>
 <dataQuery>
  <sqlStatement name="Q1">
  <![CDATA[
   select A2.VENDOR_NAME,A1.INVOICE_NUM,A1.INVOICE_DATE,A1.INVOICE_AMOUNT from ap_invoices_all a1,po_vendors a2
where a1.vendor_id=a2.vendor_id
and a2.vendor_id=:P_VENDOR_ID
   ]]>
   </sqlStatement>
  </dataQuery>
  <dataStructure>
  <group name="G_SUPP" source="Q1">
   <element name="VENDOR_NAME" value="VENDOR_NAME" />
   <element name="VENDOR_INV_AMT" value="G_INV.INVOICE_AMOUNT"
    function="SUM()" />
   <group name="G_INV" source="Q1">
    <element name="INVOICE_NUM" value="INVOICE_NUM" />
    <element name="INVOICE_DATE" value="INVOICE_DATE" />
    <element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT" />
   </group>
  </group>
  </dataStructure>
 </dataTemplate>

Sum function in xml data Template

Step 3:-

Here below we are creating the "XX SUPPLIER INVOICE DETAIL' report.
Navigation :Concurrent ==> Concurrent Program.

Important Note : In Executable , We need to define 'XDODTEXE' .

Output Type : XML
Click on the Parameters button to create report parameter.

Sum function in xml data Template

Step 4-
As below , Copying the Report parameter for Concurrent Program from Data Template.

Sum function in xml data Template

Step 5:- As below , P_VENDOR_ID column got created.

Sum function in xml data Template

Step 6:-

Now we need to goto the XML Publisher Administrator responsibility.

Home ==> Data Definitions

Sum function in xml data Template

Step 7:- 

Here below , We need to create the report Data Definition.

Note : We need to use the Short Name of Concurrent Program in Step 5 in Data Definition code field as below.


Click Apply.

Sum function in xml data Template

Step 7:- Here below , We need to upload the Data template file created in Step 2 & 3.In Data Template as highlighted below and Click on add file.

Sum function in xml data Template