Sunday, August 9, 2009

BI Publisher: Advanced Line Wrapping

Over the years, programmatically controlling line wrapping has been a very popular topic! There have been many requests in the BI Publisher forum, this blog, or via e-mail for a solution. Unfortunately, I wasn't able to provide a relatively simple solution until now.

If your new to this issue let me run down some technical scenarios on why line wrapping causes so many headaches!

Examples
My company's bill of ladding only has seven lines for comments. The comment field is stored as one text field in the database. The text field in the database allows for 70 character but the cell on the BOL can only have 50 characters. I need to only print what will fit on 7 lines maximum.

or

I expanded my cell to support 35 characters of text without wrapping but in some rare instances it wraps with 35 character because of the font I'm using.

or

My Swiss Declarations report can only print two lines on page 1 and there are several lines thereafter on the next page I can use. The text when added needs to create a new row (Cell) in the table and simply not add a carriage return in the one cell.

It's difficult to solve any of these examples in a format template. This is because BI Publisher does not have a string tokenizer api or something like a character array. However we do have ways of creating similar frameworks.

In order to solve the line wrapping problem were going to need to be able to do the following:

1. Have a controlled for-loop
2. Know how many times to loop to break-up a string (iterations) into a specified length
3. Storing the string that's going to get broken up

Controlled for-loop

The for loop down below cannot be directly ported to xsl syntax or BI Publisher.

for-loop java
for(int i=1; i<11; i++){
System.out.println("Count is: " + i);
}
We do have some workarounds though for controlled looping. There are some quietly documented features in the development guide and some other examples that can be found on Tim Dexter blog.

foreach_number can be used to control how many times a for-each statement can loop. It's may seem counter intuitive but for-loops in BI Publisher do not require an xml nodes to loop. The following statement is valid in BI Publisher:

for-each:xdoxslt:foreach_number($_XDOCTX,1,3,1)
Note: This will loop 3 times

Iterations


The math for figuring-out how many iterations are needed to break-up a string is pretty easy. Take the string below, if I have a cell length of 20, I can deduce the number of likely characters to fit in that cell by taking the length of the string, divided by the length of the cell. In the text below the length is 107. When we take 107 (text) divided by 20 (cell) equals 5 rounded iterations.

"Ike likes to walk the walk and talk the talk all of the time. End result is very long sentence of text."

Storage

Although it's not commonly used in the BIP world, xsl variables can be created in BI Publisher. XSL variables can be used globally in a template and or they can be used locally in an xsl template. Variables can also be re-assigned. If you were assuming that assignment can happen multiple times, all of the time, your mistaken. Here's the syntax break-down for creating an xsl variable:

Example 1: Create a variable with one value that's reset in a loop
creating a variable: variable@incontext:first_line_text; substring(//LONG_STRING,1,20)
getting the value:
$first_line_text (should be "Ike likes to walk")

Misc Example 2: Create a variable with one value that is the length of a field.
creating a variable: variable:field_length; round(string-length(string(//LONG_STRING)))
getting the value: $field_length (should be 170)

Misc Example 3: Create two variables and summing both values
creating a variable: variable: bill_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Bill Only' ]/QTY)
creating a variable: variable: ship_only; //ORDER_LINES[QTY > 10 and LINE_TYPE ='Ship Only' ]/QTY)
getting the value: sum($bill_only | $bill_only)


Solution

We now have the basic elements to create a parser utility. The xml text that were going to breaking up is below.
XML Doc

<WRAPPING>
<LONG_STRING>Ike likes to walk the walk and talk the talk all of the time.
End result is very long sentence of text.</LONG_STRING>
<LONG_STRING>This is some other text that will have to get parsed.<LONG_STRING>
<WRAPPING>


Here's the template that will break it up. Below is the code from the format template. This template will be breaking up the text in lengths of 20.

BIP Code:

<?param@begin:cell_length;20?>

<?for-each:WRAPPING/LONG_STRING?>
<?call-template: string_variables?>
<?variable@incontext:iterations; ceiling(string-length(string(.)) div $cell_length)?>
<?$iterations?>
<?for-each:xdoxslt:foreach_number($_XDOCTX,1,$iterations,1)?>
<?call-inline-template:string_parser?>
<?end for-each?>
<?end for-each?>


<?template:string_variables?>
<?xdoxslt:set_variable($_XDOCTX, 'text_string',.)?>
<?end template?>

<?template:string_parser?>
<?variable@incontext:start; ((position()-1)*20)+1?>
<?variable@incontext:first_line_text; substring(xdoxslt:get_variable($_XDOCTX, 'text_string'),1, $cell_length)?>
<?variable@incontext:line_text; substring(xdoxslt:get_variable($_XDOCTX, 'text_string'),$start, $cell_length)?>
<?variable@incontext:last_line_text; substring(xdoxslt:get_variable($_XDOCTX, 'text_string'), $start, $cell_length)?>
<?choose:?>
<? When: position() = 1?>
<?$first_line_text?>(FIRST LINE)
<?end when?>
<? When: position() = last()?>
<?$last_line_text?>(LAST LINE)
<?end when?>
<?otherwise:?>
<?$line_text?>(REGULAR LINE)
<?end otherwise?>
<?end choose:?>
<?end template?>
This is the best solution that I could come up. It doesn't account for the fact that specific characters may be bigger or smaller for a particular font (basically characters are not uniform). When implementing this solution be very conservative about how many characters can fit in a cell. I would suggest using the largest character in caps to figure-out the maximum length.

I'll throw this out there too, if anyone has a better or different way of doing it, please send me an e-mail with your solution on I'll post it on my blog with your name.

Also, it's important to note that a data template with a pipelined function can be used to solve line wrapping issues.....

Is there topic you would like me to cover? Post a comment and I'll see what I can do!