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

9 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

Julia Stookey said...

Has there been any solution since this post on how to limit the number of printed lines on a template? With line wrapping? I'm looking for a solution for this.

Thanks,
Julia

Ike Wiggins said...

not to my knowledge.

Unknown said...

Hello Iceman,

I have a different requirement and it is driving me crazy. I am designing a check using BI Publisher and the check form has 2 stub sections at the top and the check section is at the bottom. I manage to place the check section at the footer of the template so I am able to keep it fix there. However my dillema is with the stub sections.
challenge is the tables are resizing based on the number of records that are loading from my XML data. Each stub has 2 parts, one that has the invoice number, invoice date, amount and total in one row and the second part has the check number, the check date, the supplier and the total amount paid. And, these parts are separated by a bunch of empty rows. When I have 8 invoices, the check number section goes down and when the number of invoices is smaller the part that has the check number goes up, a real dillema.

Do you a suggestion on how to fix that?
Thanks in advance for your help.
Abner

Ike Wiggins said...

You need to do a nested table, the outer table should be fixed, the inner table can be dynamic. Do work with your ba to deal with overflow. That options for overflow are "void with overflow" or setting the number of remittance lines allowed for check. This will prevent random page-breaks. Best of luck. Ike