Sunday 28 December 2014

Trimming spaces and adding spaces on a BI Publisher report

Quite often in BI Publisher, there is a need to trim unnecessary spaces and add spaces on occasions. 

Think of printing an address. PS_ADDRESSES table has so many fields but when printing the address, some of the fields may not necessarily have values, leaving the address formatting  messed up with a lot of white spaces. This is what I'm talking about - 
The RTF template shown above generates the output as shown here - 

Removing unwanted space
In order to solve this issue, I created one BI Publisher field "mail address" with the code written as shown below.

As you can see now, all the extra white space for fields that didn't have a value is now gone (I'll ignore the border issue for now). Though this fixed the extra space issue, it introduced a new problem. I had given a deliberate space between CITY and POSTAL field in the formula but was ignored by BI Publisher in the output. 

Adding Space
The way you can add a space is quite simple, enclose the space within quotes and embed it between the BI publisher tag as shown below -
 <?if:C.ADDRESS1 != ' '?><?C.ADDRESS1?><?end if?>  
 <?if:C.ADDRESS2 != ' '?><?C.ADDRESS2?><?end if?>  
 <?if:C.ADDRESS3 != ' '?><?C.ADDRESS3?><?end if?>  
 <?if:C.ADDRESS4 != ' '?><?C.ADDRESS4?><?end if?>   
 <?C.CITY?><?' '>?<?C.POSTAL?>  

This gives the proper output I was looking for - 

1 comment: