BI Publisher: Conditionally Limiting Rows on a Page

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

BI Publisher: Reports6i to BIP Conversion Utilities

Reports6i to BIP Conversion Utilities

Currently, many organizations and developers would really like to make the leap to BI Publisher. One potential option to ease into the BIP tool set is to convert some relatively simple reports6i reports.

There are two ways to do the conversion. Manually do it (see links below) or use the BIP Conversion Utilities.

Oracle OBIEE Documentation
RDFtoBIP.zipped

Doing it manually without a GUI is a good option but it's more time consuming because there is less automation and it doesn't really tell you where your at in the conversion process. Using the BIP conversion utilities is really the way to go though because of it's simplicity. It reminds me of quote by Walter Chrysler

"Whenever there is a hard job to be done, I assign it to a lazy man; he is sure to find an easy way of doing it."

It's refreshingly easy to do the conversion using the BIP Conversion Utilities and extremely fast. Here are the main advantages of using them:

1. BIP technical expertise is not required for the conversion
   
2. Complete automation from start to finish
   

Download the tutorial video (Quicktime)

Download the code

*By downloading the software you agree that you will not sell or license the BIP conversion Utilities. It's free software for everyone to use capiche.

Instructions
Step 1 : Extract the zip
Step 2: Navigate to directory to the bin directory (ReportConversion->bin)
Step 3: Copy the batch the dos batch file to the directory where you will be converting your reports6i binary to xml. Double click to run.
Step 4: Run the ReportConversion.exe (ReportsConversion->bin, do not move the executable)
Step 5. Click the folder icon
Step 6. Navigate to your conversion directory. Select the files named .rdf.xml
Step 7. Click process button.

Report Conversion FAQ

Q. I need to have reports9i installed on the apps server in order to do the conversion.
A. This is the number one misconception about the conversion process. I have heard a lot of developers say they don't have 10g setup in there apps server environment, so they will have to wait until release 12. In reality all a developer needs to do is install forms and reports 10g locally on there PC. Also, it's possible to have multiple versions oracle forms and reports installed on a PC, just be sure to create a new home for each installation.

Q. The conversion has to take place in unix; it must be done from the command line.
A. Because BIP is written in java it allows the conversion APIs to run anywhere there is a JVM. To resolve this issue you need to grab the BIP jar files. The BIP jar files are included with the conversion utility so there is no need to worry.

Q. The reports6i Binary to XML conversion is a manual process and I have to manually key everything into rwconverter.exe.
A. A dos batch or shell script can be written to accomplish this task. A dos batch script is included with the code.

Q. The conversion is time consuming.
A. It's remarkable fast. The conversion utility is running so fast (30 seconds) that it was very difficult to add an voice over for each clip step in the process. As a matter of fact, the video was extended by nearly a minute to allow for a voice over.

Q. The conversion is flawless.
A. Definitely not. The conversion has some known issues: format triggers, bind variables, etc that don't port (there is a log that can be reviewed after the conversion)! You can find more about those issues on this blog, Tim Dexter blog at oracle or if your feeling lucky you can convert your reports and see for yourself. The utility does a really good job but there is no magic bullet.