An Oracle blog about BI Publisher

Wheres My Total Too?

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:

   <ROW num="1">
      <ENAME>Jo Bloggs</ENAME>
   <ROW num="2">
      <ENAME>Jane Doe</ENAME>
   <ROW num="3">

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:


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


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


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.

Join the discussion

Comments ( 3 )
  • Dave M Wednesday, October 31, 2007
    Would it be possible to reference the group sum values from outside of the loop? For instance, if you wanted to have a separate table at the bottom of the report that contained the department totals for each department.
  • Tim Dexter Thursday, November 1, 2007

    Hi Dave

    Good question ... I'll follow up with another post on that ... Tim

  • Laci Schrantz Friday, August 13, 2010
    Just read your post. I kind of support your views though there are two to three points which I don’t think I would agree with what you have said. The best part of it is the way you have written the stuffs. The language was easy to follow and your style was appealing. English is my second language and you have done a good job avoiding jargons and difficult phrases. Keep on writing more posts. PS: I have already subscribed to your blog’s RSS feed.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.