Oracle BI Publisher Blog

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:

<INVOICELIST>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
  <G_INVOICE_NUM>
   <INVOICE_NUM>981110</INVOICE_NUM>
   <INVOICE_DATE>10-NOV-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
   <ENT_AMT>122</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>00s</INVOICE_NUM>
   <INVOICE_DATE>07-JUN-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>100</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>FI1009</INVOICE_NUM>
   <INVOICE_DATE>10-MAY-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>1220</ENT_AMT>
  </G_INVOICE_NUM>
 </G_VENDOR_NAME>
 ...
</INVOICELIST>

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:

<?sum(ENT_AMT)?>

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 ...

Last time we looked at adding totals, today we'll take a look from another angle and also look at one of the most powerful features of the RTF templates - that is 're-grouping'.

We need to look at and understand the re-grouping before we get to the totals. Lets assume we have the following XML data:

<ROWSET>
   <ROW num="1">
      <EMPNO>7810</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>7800</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
   <ROW num="3">
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <SAL>1100</SAL>
      <DNAME>RESEARCH</DNAME>
   </ROW>
   ... 
</ROWSET>

As you can see the the data is completely denormalized and flattened out. BIP standalone will give you this easy - even under Apps you can get this, this is all PeopleSoft query will give you to work with. A flat list output is great but it would be nice to get some structure on the report, say to group employees by their department and then generate departmental salary totals.

To achieve this we need to use the 'for-each-group:' command - this effectively imposes a 'break group' structure over the flat data but in the presentation layer - this is really powerful. The benefits are three fold:

1. You can simplify your extracts- if you only have to extract flat data then developing and maintaining those complex data structures becomes much easier 
2. Support more layouts - with simpler extract structures you can support more report layouts per extract - think about merging similar report extracts
3. Ultimate flexibility - your layout templates can now dictate the 'break' structure in the report and your users can structure data pretty much how they wish.

Now there is a caveat to this - there's a balance to be struck between 'simpler extracts' and 'template flexibility' - if you do a massive amount of regrouping in the template its going to be slower than doing it in the database - just be aware.

OK, so if we use the following:

<?for-each-group:ROW;DNAME?>

This will give us a structure with a break group created on the DNAME element. We get a hierarchy thus:

DNAME
 EMPNO
 ENAME
 JOB
 SAL

We could of course create as many levels as we want using the regrouping. Lets have a template with the DNAME above a table of employees. 

To add the sub total for each department is similar to the previous example, there's just a little twist. We can use the plugin Insert -> Field dialog.

The contents of that total field are

<?sum(current-group()/SAL)?>

similar to the last time we just have that 'current-group()' command. Current-group() references the dynamic group we created using the for-each-group: command so we need to tell the processor that we want the total salary for each break level i.e. the department. Files for this article are here.

I found a new blog today from BIPGirl - she has only just started fighting for the BIP cause but it looks promising and already has some good articles that will help you to get started with all things BIP. Check her blog out and point your reader here, http://www.bipgirl.com RSS feed here, http://www.bipgirl.com/feeds/posts/default

The BIP community is growing ... Im not alone blogging on BIP anymore. I have recently mentioned BIP Girl and she is writing some great articles on getting going with BIP but there are now others writing about their experiences with BIP ... some highlights.

BIPublisher and mySQL  - a mySQL post from Borkur on Be ICE

Creating my first report for BI Publisher in offline mode! - a great 'noobie' article on getting started with the template builder for Word

Understanding Architecture Changes from Oracle Reports to BI Publisher - a followup from Rajender on the BIP architecture.

My 1099-MISC Solution - BIP Girl covers how she used BIP to solve generating a 1099 tax report

XML Publisher Using Simple Java + XML + XSL - Neeraj covers an intro to the BIP APIs

BI Publisher: Common Use for Bursting API - Ike provides more insight to bursting

BI Publisher and nested tables and loops - Greg shares some of his experiences in building templates

Creating Applications using JDeveloper 11g and Web Service SOAP APIs - more API info from Venkatakrishnan

Email Distribution of Reports using BI Publisher and Discoverer - Meeester Rittman talks Disco - BIP integration

Thanks to all above and if you have a blog article you want to share let me know.

That lot should keep you BIP happy for a while, happy reading.

An oft used term from my football (Americans read 'soccer') coach back when I was knee high to a grasshopper and we were doing 'header' practice. You dont need those brain cells just launch yourself at a football and get it in the net (goal) - there was a knack to not getting a blinding headache - my method, just avoid heading the ball altogether, just chest it down and volley it into the goal - I was that good, honest! OK, may be not that good and what the hell am I talking about I hear you ask?

Headers ... and footers for that matter in your templates! I was on a call yesterday with a JDE customer that has been implementing BIP and has been struggling with the headers in their templates. We have a command available in the templates, start|end body its a way to get headers and footers into your template without the need for the Word versions. The customer was using them and experiencing problems, now the command has its place but I have never needed it in all the templates I have built and I have built a few. The comment came up that its tough to work with Word headers and footers - this stemmed from the fact that Word does not let you put their Formfields in the header or footer areas. There are ways and means to get around this that the customer was not aware of ... I thought I'd share a couple of those with you readers out there.

So, no formfields allowed in the header or footer but you can put the clear text command in those regions. By that I mean the contents of the formfield e.g. <?CUSTOMER_NAME?>. I can therefore put as much as I want in the header/footer thus:

All well and good but not good looking and maybe a little intimidating to the business user who asked of IT,
   'Hey, I want to have the currency code column to appear after the invoice amount in that table'
to which the IT dept replied,
   'You got MSWord on your desktop?'
   'Yep'
   'Heres the template, get on with it!'

OK, maybe a little blunt but you get the idea. On a side note we are seeing the 'business' getting more and more involved in building layouts. Instead of it being a pure IT nightmare trying to keep everyone happy the business has the tools (MSWord) to make the changes they want. We now have a customer that has gone the whole way and the business is completely responsible for building, mainitaing and enhancing templates for their users - making for one very happy IT department!
Getting back on track, there is an alternative to the raw commands in the header you can create, a little confusingly a 'template' in your template - think of it as a function that will render output for you when called.

Notice the layout is created in the body of the main template and wrapped in <?template:XXXX?> commands - we then call that from the MSWord header of the template - simple stuff. Samples files here.

Now, you can get more sophisticated and completely externalize the header template into a separate RTF template and use it as a 'sub-template' - now you can share the header across multiple templates by just calling it. You can even pass parameters into the 'header' template, say the 'report title' - I have talked about that a little in this blogs second ever post back in 2006