Monday, November 19, 2012

Dynamic Formatting- BI Publisher

+Kan Nishida
A client wanted to give their users the ability to bold and italicize text for various verbiage in a contract.  The interface is Siebel Hospitality.

I chose an uncommon character, caret (^) to delimit the text.  The caret at the beginning of the text segment turns on the bold/italics and the caret at the end of the text segment turns off the formatting.  You can use this many times in the text.

The pseudo code is as follows:
Count the number of characters in the text.
Starting at the beginning of the text, look at each character.  
If the character is other than the tilde then just output it.
If the character is a tilde then just set a flag.
There are two form fields. One is for plain text and the other is for formatted text.  It the flag is set then print the character in the formatted form field otherwise, print the character in the unformulated form field.
For some reason, spaces are ignored so code has been added to put the spaces in.

Here is the code (note: code can be put in form fields):

The test text:
<?variable:s;string(‘Now is the time for all ^good men^ to come to the aid of their ^commarades^.’)?>
Initialize and set the flag variable "hit":
<?xdoxslt:set_variable($_XDOCTX,'hit',1)?>

 Display the test text: <?$s?>

This is the loop and all the code should go on one line:

 <?for-each@inlines:xdoxslt:foreach_number($_XDOCTX,1,string-length($s),1)?>

 This code sets the flag on and off when it hits the delimiter character.  In my case it is the caret. 

<?if@inlines:substring($s,position(),1) = '^'?><?xdoxslt:set_variable($_XDOCTX,'hit',xdoxslt:get_variable($_XDOCTX,'hit') * -1)?> <?end if?>

The code below should go in a form field with plain text.
<?xdoxslt:ifelse(xdoxslt:get_variable($_XDOCTX,'hit') = -1 ,'',xdoxslt:ifelse(substring($s,position(),1) = '^','',substring($s,position(),1)))?>

The code below should go in a form field with formatted text.  In my case it will be bold and italicized:
<?xdoxslt:ifelse(xdoxslt:get_variable($_XDOCTX,'hit') = -1 ,'',xdoxslt:ifelse(substring($s,position(),1) = '^','',substring($s,position(),1)))?> 

Insert this code in a form field before a space:
<?if@inlines:substring($s,position(),1) = ' '?> 

Insert this code in a form field after a space:
<?end if?> 
  Close the loop.
<?end for-each?>

Monday, April 2, 2012

Generating Consecutive Dates

Below is technique that I used to generate rows of consecutive dates. I had a report that required labor costs for each day of between two dates. This same technique can be used to generate a particular number of rows. This is one place where you can intentionally use a cartesian product.

Here is an example that can be executed in SQL*Plus

variable p_start_mm varchar2(4)
variable p_start_yyyy varchar2(4)
variable p_end_mm varchar2(4)
variable p_end_yyyy varchar2(4)
exec :p_start_mm := '01'
exec :p_start_yyyy := '2012'
exec :p_end_mm := '04'
exec :p_end_yyyy := '2012'
select to_char(ROWNUM + to_date(:p_start_mm :p_start_yyyy, 'MM-YYYY') - 1, 'DD-MON-YYYY') gen_date from dual connect by level <= last_day(to_date(:p_end_mm :p_end_yyyy, 'MMYYYY')) - to_date(:p_start_mm :p_start_yyyy, 'MMYYYY') + 1;