An Oracle blog about BI Publisher

Wheres My Total?

Theres a long thread out on the forum concerning 'totals' or more accurately 'sub totals' and the fact that they are not showing or they are incorrect. I'll get to that but the crux of the problem is to first understand the structure of the data you are using. Back in the heady years of release 4.5 - not so long ago really, it was November 2004. We were like a teenager on a first date, a little awkward, full of energy and ready to impress - but we had a problem. You could build your layout templates in Word but there was no help i.e. no template builder. It meant that you had to build the templates by hand - by today's standards, tedious to say the least.

There was one advantage thou - it forced you to look at the structure of the data to understand how to construct the layout to 'walk' that data hierarchy. Today, we do our best to hide all that and for 'flat' data we provide the means to restructure the rendered data in the output. Becasue you dont spend some time on the knowing the structure then when you are starting out with the template builder or even creating totals from scratch its a little tough to understand. Let's deal with the easier structure to understand first:

Say we have the following:

  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>

Looking at the data you can see we have invoices by vendor - the rest of the vendors are represnted. Let's assume we want to generate a an invoice total for each vendor. Let's also assume we have the following template layout:


This layout will generate a vendor name/address followed by a table of its invoices. We want a total at the bottom of the invoice table for each vendor. Notice in the table we have a 'for-each:G_INVOICE_NUM' in the first cell and and 'end for-each' in the last cell. This will force the template to loop over the invoice section of the data and render one on each row of the table.
To add a total for each vendor we need to add a field before the closing field for the G_VENDOR_NAME - we can either add a row to the table and insert the total (1) or put a value directly under the table (2).


The contents of the 'sumENT_AMT' in both cases will be:


because the field position is inside the end G_VENDOR_NAME field then the total is only calculated for the current level ie at the vendor level. If we out the field outside of the vendor field then we would get a total for all the vendors.

As I mentioned earlier the template builder plugin will do this for you but hopefully you can now see what its actually doing. Check out the 'by Example' document and the samples folder under the template builder install directory. You can get the files for the above example here.

 Next we'll take a look at 'dynamic totals' when we re-group the data in the layout template ...

Join the discussion

Comments ( 1 )
  • Imtiaz Monday, September 27, 2010
    Can we sort on the ENT_AMT here. Bcz in my case, am getting the NaN on the top and then the sorting is taking place. How to solve this?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.