Wheres My Total Too?
By Tim Dexter-Oracle on Oct 30, 2007
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:
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.