xml publiesher

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

BI Publisher: Conditionally Limiting Rows on a Page

Recently, I've been getting quite a few questions about row manipulation. Besides the solution posted here there are two really good examples for developers to look at that Tim Dexter at oracle created.

Tim D. Examples

• Across not Down!
• Multi Column Row Woes

Question

Hello Iceman,

I came across your posts on internet. I have trouble with a BI publisher report presentation and was hoping to get a quick answer from you.

I have a query result (4 simple columns) that could result records anywhere between 10 to 70. There is no grouping in this query and it is flat. I have a requirement to show 15 records in each page. Since, I have used syntax, it is showing about 22 records per page in the pdf report.

Is there a parameter in for-each element to limit the number of rows per page in the report?

Thanks in advance,
Srinath Sura

The question that Sirnath posed is quite common. It's not atypical for a business to specify the amount of lines they want to be displayed on a customer facing document (invoice, packing slip, etc). Fortunately, Srinath is not being asked to account for the number of printed lines. That gets tricky because we would have to account for line wrapping. The solution here does not account for line wrapping. If their is interest in seeing how one would go about doing that, post a comment.

In order to tackle Srinath problem were going to have to use two bip functions: position() and mod.

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.