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:


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


Total3:


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.


Total4:


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.

Comments:

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.

Posted by Dave M on October 31, 2007 at 12:35 PM MDT #

Hi Dave

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

Posted by Tim Dexter on November 01, 2007 at 05:18 AM MDT #

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.

Posted by Laci Schrantz on August 13, 2010 at 03:44 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today