Oracle BI Publisher Tags For-each grouping for Matrix report

For-Each Extensions

For-each tags can be confusing.  We will be going over the following tags: 
  
For-each@section:   
For-each@cell 
For-each-group:  field1; field2;   
For-each-group:current-group();field3; 
For-each-group@column 
  
Sample XML: 

<CORPORATION>  
   <NAME>Vision</NAME>    
<DEPT> 
   <DEPT_NAME>IT</DEPT_NAME> 
   <EMPLOYEE> 
      <EMPNO>6</EMPNO> 
      <ENAME>Rich Walker</ENAME> 
      <JOB>CLERK</JOB> 
      <SAL>100</SAL> 
   </<EMPLOYEE>> 
   <EMPLOYEE> 
      <EMPNO>5</EMPNO> 
      <ENAME>Russ Kepel</ENAME> 
      <JOB>CLERK</JOB> 
      <SAL>100</SAL> 
  </EMPLOYEE> 
   <EMPLOYEE> 
      <EMPNO>8</EMPNO> 
      <ENAME>Helbe Korth</ENAME> 
      <JOB>CLERK</JOB> 
      <SAL>100</SAL> 
  </EMPLOYEE> 
</DEPT> 
<DEPT> 
   <DEPT_NAME>Accounting</DEPT_NAME> 
   <EMPLOYEE> 
      <EMPNO>1</EMPNO> 
      <ENAME>Ike Wiggins</ENAME> 
      <JOB>CLERK</JOB> 
      <SAL>100</SAL> 
   </<EMPLOYEE> 
   <EMPLOYEE> 
      <EMPNO>2</EMPNO> 
      <ENAME>Heike Elder</ENAME> 
      <JOB>CLERK</JOB> 
      <SAL>100</SAL> 
  </EMPLOYEE> 
</DEPT> 
</CORPORATION> 
  

For-each@section:    

This tag is the simplest of all of the tags.  It does some things automatically inserts extra tags.  The 2section tag will do the following:   
  
  1. Reset files in your header and footers rtf template  
  2. Automatically force a page break when end-for each is reached. 
  
For the other benefits of using the @section, refer to the oracle documentation. 
  

Example 6:  Page Breaks – By Groups 

<?for-each:DEPT?> 
    <?../DEPT_NAME?>  
   <?for-each: EMPLOYEE?> 
         <?ENAME?> 
    <?end for-each?>  
<?end for-each?>

Notice that we have to put this for-each outside the table (nesting @ section will not work….. 
Same would apply if we were using an if-statement to suppress the whole table….

For-Each
DEPT 
Employee Name 
DEPT_NAME For-Each 
ENAME End-For-Each  
End-For-Each 
  
  
Produces the following output: This page 1  
Example 6:  Page Breaks – By Groups 
DEPT 
Employee Name 
IT  
Rich Walker   
IT  
Russ Kepel   
IT  
Helbe Korth   

Produces the following output: This page 2  
Example 6:  Page Breaks – By Groups 
DEPT 
Employee Name 
Accounting  
Ike Wiggins   
Accounting  
Heike Elder   

For-each@cell: 

This tags main use is for pivot tables.   It generates columns automatically and dynamically.  Without this tag you would have to create a for-each statement for a fixed number of static columns.   When you create a report that requires dynamic columns or a pivot table you will be very thankful that oracle created @cell.     
  

Example 7: Pivot Table  

<?for-each: EMPLOYEE?> 
    <?ENAME?> 
<?end for-each?> 
<?for-each: EMPLOYEE?> 
    <?SAL?> 
<?end for-each?>  

    

    


Note:  @section from previous example forces a break… 



For-Each ENAME End-For-Each 
Salary 
For-Each SAL End-For-Each 

Produces the following output:  Page 1  
 Rich Walker  
 Russ Kepel  
 Helbe Korth  
Salary 
 100  
 100  
 100  

Produces the following output:  Page 2  
 Ike Wiggins  
 Heike Elder  
Salary 
 100  
 100  


One of the issues you may run into is that columns may not line up.  In this example it’s not possible.  However it has been my experience with multiple header – detail relationships you run into issue if the parents children aren’t exactly the same.   Meaning that some parents have two kids, some have one…. 

Example 8: Yuck, Columns and data don’t match up  

      Plant   1 
           Item a,  qty 1 
           Item b,  qty 2 
      Plant   2 
           Item b, qty 3 
  
           Item c,  qty 4 
           Item d,  qty 5 
  
Produces the following output:  
  
Item A 
Item B 
Item C 
Item D 
Plant 1 
1 
2 
  
  
Plant 2 
3  
4 
5 
  
  
What happens is that @cell is not going to know that it should skip the “item a column” or put a blank value there.  Nope, it just continues to add in each value in each sequential cell.  In this situation you are left with a couple of options: 
  1. Make sure each data set has the same number of elements.  If an element is missing, populate it with a value or null.  As an example:  item a for plant 2, we would put null in the qty and create the item a.  This can be achieved using pl/sql or doing some very complicated matching using xpath  
  2. Make it so the report excludes results that don’t span against every result set.  This is the easiest.  Again the advanced pl/sq technique’s article might be helpful.  
  3. Come up with something else that I haven’t thought about…. 
  
  
  



For-each-group & For-each-group: current-group() 
These tags are mainly used to group elements together.   I don’t believe the oracle documentation states it but it has a limitation of two elements that can be grouped.   In the line below we can see how simply it is. 

<?for-each-group:EMPLOYEE;JOB?>  <?JOB?> <?SAL?>…. End Loop

Were grouping all of the jobs together above by salary.  If we added more fields the grouping, it wouldn’t be as effective (more fields the less that get’s grouped).   The example above is not very good because the data were working with can’t get aggregated very much.  However, will proceed to Current-Group().   Current-group() is an addition to for-each-group tag.  It allows you to basically take the current grouping and group it again (this would include all of the fields that are not being used.  For those of you familiar with some of the new group by functions in oracle 9i this is not a new concept.  Anyways, here’s an example using below:

<?for-each-group:current-group;EMPNO?>  <?EMPNO?><?COUNT(EMPNO)?>…. End Loop


For-each-group@column 

I have never seen this used (corrections: not true anymore, I'm using it). @Column will create a column based on element existing, otherwise it will suppress it. Basically, it eliminates having to write an if stmt excluding a row to see if a value is null.  Yep, that’s right @column is used in if statements as well. 

@Column is used for supressing and removing a cell in "if-statements".  If you are going to use this with a for-each I would recommend that you look at oracle’s documentation...
<CORPORATION>
   <NAME>Vision</NAME>  
<DEPT>
   <DEPT_NAME>IT</DEPT_NAME>
   <EMPLOYEE>
      <EMPNO>6</EMPNO>
      <ENAME>Rich Walker</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </<EMPLOYEE>>
   <EMPLOYEE>
      <EMPNO>5</EMPNO>
      <ENAME>Russ Kepel</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
  </EMPLOYEE>
   <EMPLOYEE>
      <EMPNO>8</EMPNO>
      <ENAME>Helbe Korth</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
  </EMPLOYEE>
</DEPT>
<DEPT>
   <DEPT_NAME>Accounting</DEPT_NAME>
   <EMPLOYEE>
      <EMPNO>1</EMPNO>
      <ENAME>Ike Wiggins</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </<EMPLOYEE>
   <EMPLOYEE>
      <EMPNO>2</EMPNO>
      <ENAME>Heike Elder</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
  </EMPLOYEE>
</DEPT>
</CORPORATION>


Overview of for-each 


Example 1: Get Depts

This statement will loop through all of the DEPT nodes twice (there are only two dept nodes). 
<?for-each:DEPT?><?DEPT_NAME?><?end for-each?>

DEPT
For-Each DEPT_NAME
End-For-Each



It will produce the following output:

DEPT
 IT
 Accounting

Example 2: Get dept/employee

Now this is where were going to reference the vocabulary above.  Employees are available in the context of the current element or node dept.  So you can access those elements but it will be in the context of the current node, which only has two elements to loop through. 

<?for-each:DEPT?><?DEPT_NAME?><?ENAME?><?end for-each?>

DEPT
Employee Name
For-Each DEPT_NAME
ENAME End-For-Each

 It will produce the following output:

DEPT
Employee Name
 IT
Rich Walker
 Accounting
Ike Wiggins

Example 3: Get all the depts and employee

Seems confusing, huh.  You would have expected to see Russ, Heike, Helbe, etc.  The parser got the first employee for each dept. 

If we wanted to drill down in employee’s we would need to make sure were in the right context and to tell the parser there’s more elements there. In order to do this, we need to do another for-each loop.  This tells xml parser the following.   For each dept, get each dept and for each employee get there name too and in that order.

<?for-each:DEPT?>
    <?DEPT_NAME?>
   <?for-each: EMPLOYEE?>
         <?ENAME?>
    <?end for-each?>
<?end for-each?>


DEPT
Employee Name
For-Each DEPT_NAME For-Each
ENAME End-For-EachEnd-For-Each


We get the following output:

DEPT
Employee Name
  
Rich Walker
  
Russ Kepel
  
Helbe Korth
  
Ike Wiggins
  
Heike Elder

Example 4: Fix missing dept

We can see that dept somehow disappeared.  This is can be caused by nesting for-each statements in the same row cell.  To fix this will use the ../ syntax.

<?for-each:DEPT?>
    <?../DEPT_NAME?>
   <?for-each: EMPLOYEE?>
         <?ENAME?>
    <?end for-each?>
<?end for-each?>

DEPT
Employee Name
For-Each DEPT_NAME For-Each
ENAME End-For-EachEnd-For-Each


We get the following output:

DEPT
Employee Name
 IT
Rich Walker
 IT
Russ Kepel
 IT
Helbe Korth
 Accounting
Ike Wiggins
 Accounting
Heike Elder


Example 5: Get all corporation, dept and employees

Now if we are able to go down the xml tree from dept to employee shouldn’t we be able to go up?  Yep, we already did with dept.  Will use he following syntax achieves that:  ../  or //

note:  //  this is not  in-context command, it will find the first element in the document that matches.  This isn’t a problem if there is only one element.  We will be doing that in this example.  Also, if you wanted to find the first element in the current context you could do .//

<?for-each:DEPT?>
    <?//NAME?> 
    <?../DEPT_NAME?>
   <?for-each: EMPLOYEE?>
         <?ENAME?>
    <?end for-each?>
<?end for-each?>

Corporation
DEPT
Employee Name
For-Each NAME
DEPT_NAME For-Each
ENAME End-For-EachEnd-For-Each


 We get the following output:

Corporation
DEPT
Employee Name
 Vision
IT
Rich Walker
 Vision
IT
Russ Kepel
 Vision
IT
Helbe Korth
 Vision
Accounting
Ike Wiggins
 Vision
Accounting
Heike Elder


There are some ways to modify for-each statements.  In most cases they are as follows:

For-each@section: 
For-each@cell
For-each-group:  field1; field2;  
For-each-group:current-group();field3;
For-each-group@column

These are advanced tags for-each.  Notable you will use for-each@section quite often it is very useful.   They’re discussed in another for-each tags article.   See BI Publisher:  for-each tag’s! 

For-Each NAME
DEPT_NAME
For-Each
Name: ENAME Salary: SAL
Current Employee position: <?position()?>
<?if: position() mod 3 = 0?>
PAGE BREAK APPLIED
<?split-by-page-break:?>
<?end if?>
End-For-Each
End-For-Each

*********************************************************************************For those of you new to xpath or xsl, position() is the current position in a for-each loop. For each iteration the position is incremented by one automatically. Typically, position does not need to be used in most Xpath operations.

Modulus is a basic mathematical function that divides a number and returns the reminder. It's usefullness is not strictly limited to mathematical applications. As an example, if you have ever written program that creates a Gregorian calendar you have used modulus. Anyways, it's also a valuable function in bip as we will see shortly.

Pseudo Code 
Solution
If position() mod 15 = 0 then
Use bip section break
End if

BIP Solution
<?for-each@section:DEPT?> <?//NAME?>
<?../DEPT_NAME?>
<?for-each:EMPLOYEE?>
Name: <?ENAME?> Salary: <?SAL?>
<?if: position() mod 3 = 0?>
PAGE BREAK APPLIED
<?split-by-page-break:?>
<?end if?>
<?end for-each?>
<?end for-each?>

Pretty simple solution huh! All we have to do is test if the position is divisible by 15, if it's not then we do nothing, otherwise, we do a page-break.

Reference: http://oracleapplicationstechnicalscripts.blogspot.com/2012/02/xml-publiesher.html

Comments

Post a Comment

Popular posts from this blog

SQL Scripts for Custom Table Creation in Oracle Application 12.2.x

Due to introduction of Online Patching, Post 12.2.x version, there are certain steps to be followed while creating Custom Table. 1. For new table creations - after creating the table in custom schema execute below script to generate edition view and synonym for it in APPS schema. exec AD_ZD_TABLE.UPGRADE('xxcust_schema','xxcust_Table'); 2. For table alterations - after running the DDL run below script to regenerate the edition view for synchronizing any table changes. exec AD_ZD_TABLE.PATCH('xxcust_schema','xxcust_table') 3. Table grants must be done through API to avoid invalids. exec AD_ZD.GRANT_PRIVS('SELECT','xxcust_table,'xx_role') 4. To view objects in all editions, add "_ae" to the data dictionary views select * from user_objects_ae; 5. To update seed data tables in Patch edition, execute prepare command. example:  exec ad_zd_seed.prepare('WF_MESSAGES');  The prepare Procedure will creat...
Read more

Messages in Oracle Application

Image
  What is a message in Oracle Application? Oracle Application uses the Message Dictionary to store translatable  Error  and  Warning  messages that can be used by any programs written in Forms, Reports, Java, or PL/SQL. These messages mainly provide information about business rule errors, such as missing or incorrect data, and how to resolve them, warn about the consequences of intended actions, inform about the status of an application, pages, or business objects, and indicate that processes and actions are performing or are completed. By using the messages in the Message Dictionary, you can define standard messages that you can use in all your applications(Oracle Form, Reports, OAF and ADF), provide consistency for messages within and across all your applications, define flexible messages, and change or translate the text of your messages without regenerating or recompiling your application code. How will you create a Message? In Application Developer Responsi...
Read more