Check a field exists in the XML with BI Publisher

this turned in to a bit of a GOTYA!  we had an interesting issue where mike was using a language field to control how a field looked on the page.  the trouble was we occasionally got crap data where the language field had not been populated  and the default section in a choose statement wasn't firing

the solution turned out to be

<?if:not(element_name)?> desired behavior when the element is not present <?end if?>

 which fixed the problem nicely  thank you google we love you !!!!!!!

formatting number fields

a big thank you to Mike for finding this  here is a very useful blog posting on formatting number fields  which dosent seem to work quite as well as it should in word when designing rtf templates Number Formatting Linky

BI Publisher Date functions

Big Shout out to Jamie on this one :)   I hadn't really had to look for these before but as well as the Date field in the properties bit of the XML which i think shows the date the XML is created   there's also this cracking list of  Date functions out there that i think are going to come in really reallyBIP Date Functions Type Linky !!! useful

Page Structure in RTF templates Headers Footers and Continued on next page

One issue that has come up recently  was adding some structure to our BI Publisher  templates .  adding headers and footers to them  and  the ability to display different headers and footer depending of  we are on the first middle or last pages of a report .  that initially caused quite a bit of head scratching until I discovered a couple of trick that solved all my problems

firstly stay away from the header and footer sections in word  I had a go with these initially but there not reliable if they work at all.  the solution turned out to be just a couple of XML tags

<?call-Template: PageHeadder?>

     <?start:body?>

               Do report Body Stuff in  here    

    <?end body?>

<?call-Template: PageFooter?>

where PageHeadder and PageFooter were a couple of sub templates I had created earlier .  works lovely every time 

Now what do you do if you want a different header for the first and last pages  or if you want the middle pages to print Continued at the bottom ?

well it turns out that this is quite a common question asked on the interwebz and seems to have a nice elegant solution  the excellent chaps over at the never name it blog have put this together Never Name it Blog Page Structure

and because it seems to be a question asked a lot  they put this excellent guide together too for those anoying times where you want reports to print continued on next page on every page apart from the last one Last Page Continues

a very handy BI Publisher Gotya's Page

while furtiling round the intertubes to try and find a solution to a particularly annoying sub template issue (I'll post more details when i get a solution ) I've come across this very helpful blog post  with a lovely list of  fiddly bits and got-ya's  that i think is going co come in very usefull

http://everythingoracle.com/obip11dmbug.htm

BI Publisher Handy snippets for checking if feilds exist and are populated

I found this when lurking about the interwebs looking for  a way to check if a field existed in the XML


• To define behavior when the element is present and the value is not null, use the following:
<?if:element_name!=?> desired behavior when the element exists and is not null <?end if?>
• To define behavior when the element is present, but is null, use the following:
<?if:element_name and element_name="?> desired behavior when the element exists but value is null <?end if?>
• To define behavior when the element is not present, use the following:
<?if:not(element_name)?> desired behavior when the element is not present <?end if?>

BI Publisher Sub templates and calling them conditionally

I had a few issues today that sent me off on an oddesy to learn about sub templates and how to use them . and now I've had a fiddle with them they have totally revised my approach to writing templates.  there really dead useful :).

 I'm working on one of our company invoices and  was having quite an unpleasant time trying to work out how the hell to make this template work . like every one else out there I should imagine our invoices can have  lots of different ways to display a line item and you can have any number of line items on a page how on earth was I going to cope with invoices that went over to more than one page ?????   this is the type of thing that on more than one ocasion has reduced me and Mike to sobbing wrecks and reaching for the poultry with one hand and the Whisky with the other when we tried to do this with transform

dead easy it turns out.

This where sub templates come in .  Sub templates are small templates in there own right that you can either store in another RTF document  or stick at the bottom of your template out of the way (which is what i did  in this case) 

My first issue was to make sure the header and footer on each page were there and displayed properly . I  had one header section which is displayed on every page  and 2 footers. one which is displayed on the last page  and one  on all the others . so I created these  as sub templates.  to do that i just wrapped them in <?Template:?> tags  like this 

then i just popped <?call-Template: PageHeadder?> in the  header of my word RTF Template. 

Next I tackled the footer issue by doing exactly the same I created 2 Templates  a page footer one and an last page footer one  then used a choose statement to choose which one to display in the footer  using a bit of code like this 

<?choose:?>

          <?when:PrintTax_ID5=“Y“?><?call:PageFooterWithTax?><?end when?>

           <?otherwise:?><?call:PageFooterNoTax?><?end otherwise?>

<?end choose?>

finally I created a sub template for each of the different line types i had  and used exactly the same technique to call the sub template for the line type i wanted to display .  yet again  no swearing sobbing or poultry bothering required 

BI Publisher Storeing a substring of an XML tag into a variable

this gave me a bit of  a headache  I had a task where i needed to chop of the last  character of one of the XML tags so I could use it in some of the logic statements  further down in the code  .  this did the trick  OptioPrinterName_ID180  is the name of the XML tag i wanted to chop the end off and store  and 1 at the end  is the number of characters to chop


<?xdoxslt:set_variable($_XDOCTX, ‘optioChar’,xdoxslt:right(OptioPrinterName_ID180,1))?> 

 Then further down in the code when i needed  to reference the variable for my logic  i Referenced it as follows


<?choose:?>
         <?when:xdoxslt:get_variable($_XDOCTX, ‘optioChar’)=‘A‘?>Test<?end when?>
           <?otherwise:?>Test2<?end otherwise?>
<?end choose?>

BI Publisher Conditional Character Spacing on a Field

I have just come across an interesting issue  that took a little bit of brain scrambling to sort out.  I had a field that I had to display in two different ways 

 if the field was on the last page of my document I needed to display the date  with 2 spaces between each of the characters   a bit like 1  8  0  3  2  0  1  4

for any other page i had to display     "***VOID***" with normal spacing.

now id managed to get my if statement working fine  to display eather the date or void but the spacing issue became a major headache.

i tried all sorts of XSL_FO combinations to no avail  them a eureka moment .  I could add two fields . id use word to increase the character spacing on one field   and leave the second field with normal spacing.

in the absence of discovering a better way to do it all in one field (Hope fully some one more experienced can sugest that)  hers the code in my feilds  that i used to resolve the issue


word field formatted to double space the chars 
<?xdoxslt:ifelse(Current_stub_number___Local_ID46 !=  Stub_Total___Local_ID66, '' ,G_L_Date2___Local_ID64)?>


second word field formatted as normal 
<?xdoxslt:ifelse(Current_stub_number___Local_ID46 !=  Stub_Total___Local_ID66, ' * * * VOID * * * ' ,'')?>


to increase the spacing on the first field  clicking on the little arrow at the bottom of the font  ribbon brings up the following where you can  increase and decrease character spacing over a field paragraph etc etc in word


so eather the single spaced or the double spaced field is shown 

Extended Function Support in RTF templates BI Publisher

ok while hunting round the interwebz for a way of taking a date and turning it into a sub string a character at a time  (don't ask its a finance report thing  SHUDDER !)  I stumbled across this incredibly cool Oracle document  that shows loads of SQL , XSL, and FO elements and functions and how to do them in BI Publisher  well cool and extremely Useful  thank you Oracle   thank you Oracle Techno Pixies :)


extremely cool  and useful Linky

Using a Case statement in BI Publisher

This is really useful  and surprisingly easy   if you need to display multiple different pieces of information   on part of the document that depend on a value or values in  the XML  you can use a load of  "if"  or "if:else"  statements   but its much easier neater and more brain friendly to use a case statement like the following

where Company_ID199   is the tag in The tag in the XML that you want to make the decision based on


<?choose:?>
             <?when:Company_ID199=‘00040‘?>Test <?end when?>
             <?when:Company_ID199=‘00041‘?>Test 2<?end when?>
             <?otherwise:?> Test3<?end otherwise?> 
<?end choose?>