XML Publisher Report from XML Data Template – Part 3

This is the 3rd in a series of blogs on XML Publisher Reports from XML Data Template. Today we are going to look at a few nifty tricks that we can achieve using the XML Data Template. Also check out the Part 1 and Part 2 of this blog to get more details.

1. Group under a Group in the XML Output
2. Using the SUM Function to calculate sum of a field

Group under a Group in the XML Output

So we can generate a Parent Group and a Sub Group using the XML Data Template. This feature is very useful and mostly used in Parent – Child relationship scenarios.
The following examples fall into this category:

1. Supplier and Supplier’s invoices
2. Customer and Customer’s AR Invoices
3. Supplier and Supplier Sites
4. Department and Employees belonging to the Department

So in our example let us look at the below business requirement:

1. List all the Invoices belonging to a Supplier
2. At Supplier level we need the SUM of the invoice amounts

So basically we are looking at the below structure.

Let us look at the XML Data Template that we need to create. We have two parameters – Org Id and Vendor Id

Parameters Section has the parameters that will be used in the SQL Query.
Data Query Section has the SQL Statement. The SQL Statement selects the Supplier and Invoice Details based on the parameters.
Group: I have defined two groups. G_SUPPLIER is the parent group and G_INV is the child group.

Parent Group:

Child Group:

Using the SUM Function to calculate sum of a field

If you notice the G_SUPPLIER group, the TOTAL_INV_AMOUNT field is the sum of all the child invoice amounts belonging to a particular supplier.

The value is picked from the Amount field in the Child Group, i.e., G_INV.AMOUNT. The child values are added together using the function SUM.

Save the XML Data template as an XML File and create the Data Definition. Follow the steps described in the Part 1 of the blog.
When you run the concurrent program the XML output will be similar to what is shown below:

You can use this XML output to design the RTF Template. Hope this helps. Check out the Part 1 and Part 2 of this blog to get more details on how to generate XML Publisher Reports using XML Data Template.

Hope this helps. Let me know your feedback. As always your feedback and suggestions are most welcome. Feel free to ask any questions that you might have.