Friday, June 19, 2009

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

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

5 comments:

Anonymous said...

I would love to see your solution for handling line wrapping. I was recently faced with this issue and had to modify the look of our report becuase I was stumped by the wrapping. Awesome blog!

Gaby said...

Same as the previous post... I would like to know how to limit the number of lines when the wrapping features is on.
Thanks!

Jon said...

Easy! This is 2009 not 1979.

With regards to limiting lines when wrapping is on, this is not possible unless you use a fixed width font as the technology doesn't know how it will be rendered using a particular font (some are wider than others).

One step forward, two steps back.

Ike Wiggins said...

Jon,

Do I detect a decenter?

Your correct, line wrapping is more of art than a science. It's a best guest for the maximum amount of characters on a line. In some cases a line would be able to fit on one line vs two. When I write up the post I'll put your comments in there as a known issue.

onamoя oэmoя said...

Hi Ike,
After i run your convertion utilites, it created some .pls .rtf files? what is the next thing i will do? what file will upload to BI Publisher?

Thank,
Romeo