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>
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:
- Reset files in your header and footers rtf template
- 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 |
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.
<?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:
- 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
- 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.
- 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>
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?>
It will produce the following output:
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 |
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 |
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;
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!
DEPT_NAME
For-Each
Current Employee position: <?position()?>
<?if: position() mod 3 = 0?>
PAGE BREAK APPLIED
<?split-by-page-break:?>
<?end if?>
End-For-Each
End-For-Each
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. ExamplesQuestionHello 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 SolutionIf 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.
Step 2:
