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.

Download the limiting 3 rows per page example

Up and coming posts: Webservices Mayhem