Oracle BI Publisher Blog

Welcome to the official XML Publisher BLOG, we hope to fill these pages not only with XMLP news and views but also provide how to's and walkthroughs. We're going to hook up the content here with some of the questions we see on the XML Publisher forum on OTN so we can provide more detail on answers to questions or comments.
That said, their was a question recently on how to represent chemical formulae in the XMLP output. Those of you with a little chemistry knowledge will know that the '2' in H2O should be 'subscripted' to be shown correctly in text i.e. H₂O.

So the question piqued my interest, just how would you do it, we have to make some assumptions about the formulae to keep things simple, I assumed that all numbers within a formula such as 'C2H5O' need to be subscripted.

Now this is not a generic enough requirement, at least not at the moment for XMLP to support natively so we need to turn to the power of XSL and our ability to embed it in the layout template to achieve the number subscripting. As you know you are able to use any XSL/XSLFO code in your templates and using these languages you can create 'templates' in your layouts or for those of us that use other programming languages, think of them as functions. You can pass parameters and get resultsets back. Its a very powerful feature. If you have templates (functions) that might be needed across multiple layouts you can externalize them in a sub template.

So the first task is to build a template that can accept a formula and pass back the formatted value ... being somewhat lazy and thinking that someone else must have done this already I cast around the web looking for a possible fit ... and came up trumps on one of the big XSL mailing lists. Kevin Lanham had put together a template that accepted the formula and then recursively looked through the string for numbers and set the subscript properties for them:

I have seen a few questions around sub templates on the forum recently. Using subtemplates is a very powerful way of making your life as a template developer much easier, if you have a piece of formatting functionality that you intend to use across multiple templates then break it out as a sub template you can then benefit from write once - maintain in one place. This also applies to headers and footers, most if not all of you will have an 'organization' header and footer that you need to have on all of your documents - again just create a sub template and reference it from all your top level templates, you can even pass parameters to the header/footer formatting to 'personalize' the header for each template but still manage only a single sub template.

RTF or XSL

You can use either RTF or pure XSL as a subtemplate. RTF is great if you have some visual formatting you need to do - much easier to develop using MSWord than writing XSL. XSL has its place thou, if you have some heavy lifting to do on the data to format it i.e. calculations, etc then XSL is the way to go.

Templates and functions ...
Its a little confusing when starting out with XSL. The complete formatting file is called a 'template' and the formatting functions within a subtemplate are also called templates ... just think about them as functions. You pass the function some object and it returns the modified object.

A Quick Example ...
Lets assume we want a common header and footer for our report outputs, we have a logo, data stamp, etc and we want to put the report name into it too. The report name is going to have to be dynamic and passed to the sub-template at runtime, we can achieve this because XSL lets us pass parameters around in the template call.  

So we have a sub template looking like the following, I have put in the raw commands rather than formfields to make it easier to see whats going on:



A you can see we have the template declaration:
<?template:Header?>
<?param:ReportName;string('My Report')?>

This declares a template called header and it is expecting a parameter called 'ReportName', if it is not populated at runtime then the default, 'My Report' will appear (this is not mandatory). The footer template is declared in a similar fashion.

In our calling template we will have something like:
  <?call@inlines:Header?>
   <?with-param:ReportName;string('Employee Report')?>
  <?end call?>

In the MSWord header of our template, this is calling the Header template and passing the parameter 'ReportName' with the value 'Employee Report', this could equally be an element name if you extracted the report name in the XML data. In the bdy of the template we need to declare that we want to use the sub template in this main template, so we will have a declaration:

<?import:file:///C:/temp/HeaderFooter.rtf?>

This brings up another interesting question for EBS template developers, how do I test my sub template without deploying it to the template manager. Well the import statement supports a URI reference so we can either use the file URI or a UR to reference our sub template. In this case the subtemplate is in the c:/temp directory. Very useful when you want to test. For EBS developers, remember to change this to the 'xdo:' URI format prior to deploying.  
So when we run the main template it will call the Header template and pass in the report name i.e. Employee Report to the subtemplate and the full header will be passed back to the calling template including the logo, timestamp, etc.

So sub templates need a little effort to set up but the benefits going forward far outweigh the cost. Write it once and deploy it everywhere and maintain it in one place going forward ... when management decide to change the company branding overnight you'll thank your lucky stars you used sub templates!

A check printing template is probably one of the tougher templates to build, there is so much going on with regard to formatting and security. I think we covered a lot in our Check Printing white paper ... it needs an update ... we'll get to it. In the mean time today's snippet of knowledge I want to share is around securing those check signatures ... the last thing you or management want is a check run with no security around the signatures. We recently put together a template that provides the logic to secure the signatures and if they are not present then of course we do not want to print a blank check ... thats just as bad as not securing the signatures in the first place so we can use a background image saying 'VOID' that will show if the signatures are not present.

So heres the check section of the template:



All pretty standard stuff, the section of interest is the area with the yellow rectangles, these are actually dummy images but colored to make life a little easier. Now the logic we needed to implement was:
if no signature images are accessible then show VOID
 if check amount < 500 show signature 1
  if check amount > 500 show signature 2 as well
   if check amount > 1000 show signature 3 as well

To achieve this we can use a cool piece of XSLFO functionality that allows us to create a background image i.e. the void image; if the singnature images are present then we can lay those images over the top of the background. Using this approach we can satisfy the requirement above.
So under those fields and images we have the following:

As you can see most of the fields are standard faire in the template world; either calling an image or running an 'if' statement. The only point of note is the '@inlines' command this is basically forcing the images to be aligned next to each other, without it they would stack on top of each other. The BG field is the most interesting, it is setting some attributes for the cell of the table; the first is the background image, the other two are setting the position of the image within the cell i.e. centered and at the bottom. The URLs are pointing to a server where the images are hosted, this could be a direstory or you could equally mount a floppy or memory card on your web server.

Now the images have some requirements:
1. They need to be accessible via a URI, either file:// or http:// or if you are an EBS customer you can use the OA_MEDIA reference.
2. The signature images need to have a solid background otherwise they will not cover the void image and it will show through at runtime.
3. The signature images need to be the same size as the void image. Remember the void image is going to be rendered everytime its just the signature images are going to be laid over the top of it so they need to be at least as big to ensure the void image is covered.

What happens at runtime?

Are there alternatives?

Made it back from Nashville with a pile of requests for blog entries, I'll try and cover them over the next few weeks ... top of the list of requests was how to handle dynamic images in a layout template. There are several ways to tackle this depending on where your images are going to be at runtime. The scenario is, that I have three organizations in my company and they each have their own logo; at runtime, based on the organization I am generating a report for the output should show their logo. Lets assume we have the following XML data for all of these examples:

<INVOICELIST>
 <IMG_DIR>/images</IMG_DIR>
 <IMG_SERVER>http://xdo.us.oracle.com</IMG_SERVER>
 <IMG_DIR_PATH>c:temp</IMG_DIR_PATH>
 <G_VENDOR_NAME>
  <ORG>1</ORG>
  <IMGFILE>Org1.gif</IMGFILE>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
   <G_INVOICE_NUM>
    <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
    <GL_DATE>10-NOV-04</GL_DATE>
    ...
 </G_VENDOR_NAME>
 <G_VENDOR_NAME>
  <ORG>2</ORG>
  <IMGFILE>Org2.gif</IMGFILE>
  <VENDOR_NAME>Tick Tock Clocks</VENDOR_NAME>
  ...
</INVOICELIST>

So our XML references an image directory(IMG_DIR) on our server(IMG_SERVER) and the full path to the image directory (IMG_DIR_PATH) on our server, then each invoice has the organization ID and the image name to be used. So we can tackle this one of three ways:
1. Embed the three logos into the template directly
2. Reference the images on the disk and use a dynamic image
3. Reference the images via a URL

Embedding in the template

This is the simplest method, if we have three images, one for each organization we can add them all to the template and wrap them in conditional statements to show/hide them. 



The simplest method is to use three 'if' statements, you could equally use a 'choose' statement to get the same effect. See DynamicImg1.rtf template here. If you have alot of images and/or need dynamic images in multiple templates; you could put a template function either else where in the template or ina completely separate template altogether and then call it from the main templates. See DynamicImg2.rtf for an example in the zip file. 

Using dynamic images

We can also just add a single dummy image to the template and at runtime point to the correct image file either on the file system or via a URL. We can build the file location of the image quite easily using the XSL concat() function.  Note at runtime the incoming image will be resized (if necessary) to fit the same dimensions as the dummy image.
So in the Web tab of the image properties we have:



File System: url:{concat(../IMG_DIR_PATH,'/',IMGFILE)} See DynamicImg3.rtf
URL: url:{concat(../IMG_SERVER,'/',../IMG_DIR,'/',IMGFILE)} See DynamicImg4.rtf

Notice we use '../' to get a level up from the G_VENDOR_NAME level for the root level values we need to construct the path or URL i.e. ../IMG_DIR_PATH.

If you are an E Business Suite user you can also make use of the OA_MEDIA directory. XML Publisher will resolve this value at runtime. So for an image in the OA_MEDIA directory you could use:

url:{?${OA_MEDIA}/IMGFILE?}

You can get all the examples and XML data here.
So thats dynamic images, of course you can use it for any image and you can build much more complex conditional formatting rules around the images. Have a good day ...

No this is not going to be an article on how to add a barcode to your output ... I'll save that for later. We are finalizing our licensing for our new unicode fonts, that we make available to you. As part of the deal we also have the opportunity to get three barcode fonts from the vendor, we'll have a MICR font too.

So heres the question for you all ... which three would you as customers like? There are so many available but we'd like to get three that you'll actually find useful. If you have suggestions please post them in a comment.

Thanks, Tim

Good question on the forum asking how to insert a db stored blob image in the report output? It can be done relatively easily the first task is to get the blob out into your XML datasource.

Extract your BLOB

The XMLP data engine now(5.6.2) supports the extraction of blob images directly into the XML. Its pulled out and stored in a base64 format there is no limit on the size of the BLOB ie you can get more than 64K of data out in the encoded format. There are other methods to get the data out but it must be base64 encoded.

Reference your BLOB

In the template you now need to reference the image data. We can use an XSLFO expression to reference the image:

<fo:instream-foreign-object content-type="image/jpg">
 <xsl:value-of select="IMAGE_ELEMENT"/>
</fo:instream-foreign-object>

Notice the template needs to know the mime type of the image, in this case "image/jpg" The IMAGE_ELEMENT contains the base64 encoded image data. Template and sample data here.
So at runtime the image is brought into the output, et voila!

Please note: In this release the BLOB support is limited to Oracle databases.

When you create a simple report that contains for example a single table, many users would like to have a single template that gets the column by which the table is sorted as a parameter - so that the end user can easily change it. Lately, I got this functionality working  and thought I share it.First you will need to define a parameter:

<xsl:param name="order" select="'SALARY'" xdofo:ctx="begin"/>

Next you will create a table that is sorted for example by name This can be accomplished easily with the table wizard. The resulting for-each and sort statements are:

<?for-each:ROW?>
<sort:NAME;'ascending';data-type='text'?>
?

So in a first attempt we would replace NAME with $order to use the parameter to sort:

<?for-each:ROW?>
<?sort:$order;'ascending';data-type='text'?>
?

This does not work? Why? Well, $order contains a string, while the <?sort:$order ?> statement which is translated into a <xsl:sort select=?$order?/> expect a node as a parameter and not a name. So how do we get the SALARY node from a variable that contains the text 鉄ALARY??

We can get the node by adding a predicate using the [] syntax. We first use the expression that matches any childnode: ./*. Now we add the condition that the name of the child node equals the content of the variable $order. The resulting expression is:

./*[name(.) = $order].

So we replace the NAME not with $order but with ./*[name(.) = $order] and achieve

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='text'?>
?

This worked fine, with one caveat: The sort always sorts alphanumerical and not numerical ? meaning 1000 comes before 2. You will need to change the data-type to achieve correct sorting. You could achieve that with an if statement to set the data-type depending on the field name and run into some other issues ? or pass the type as a parameter.

If you want to use a parameter (e.g. with the name type) in for the data-type, you need to know that this field expects a string and not a node. If you write:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='$type'?>
?

your sorting will fail, because it will use the name of the variable $type as the data type and not its content. So you will need to user {} around the variable to substitute an XPATH expression or a variable inside the text. The final expression is:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='{$type}'?>
?

and your dynamic sorting should work like a charm? Unless you want to use an if statement to set the data-type depending on the parameter name ? but that again is a different story to be told at a different time.

KF

With XML Publisher 5.6.2 you now have the ability to execute pre processing on your data prior to applying a barcode font to the data in the output document. For example you may need to calculate checksum values or start and end bits for the data before formatting them.

In lieu of Oracle not having the barcode fonts available I have worked with the popular IDAutomation fonts, they provide the necessary java code support for their fonts.
Information on their fornts can be found here: http://www.idautomation.com/fonts/

Information on their java source code here: http://www.idautomation.com/fonts/tools/sourcecode/FontIDAutomation.txt

The solution requires that you register a barcode encoding class with XML Publisher that can then be instantiated at runtime to carry out the formatting in the template. This is covered in <<link to class description and sample and setup>>
In the template there are two new commands that need to be used to enable the formatting feature.

<?register-barcode-vendor:java_class_name;barcode_vendor_id?>

This command registers the barcode encoding class. It requires a java class name, this will carry out the encoding and a barcode vendor ID as defined by the class. This command needs to appear before the commands to encode the data in the template. For example:
<?register-barcode-vendor:弛racle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil?;'XMLPBarVendor'?>

So the class is defined as 双racle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil? and the vendor id as 'XMLPBarVendor'

For the data to be encoded the following command is required:

<?format-barcode:data;barcode_type;barcode_vendor_id?>

This accepts:
?    data ? the element in the XML to be encoded
?    barcode_type ? this is the method in the encoding class used to format the data e.g. Code128a
?    barcode_vendor_id ? this is the ID as defined in the register-barcode-vendor field.

At runtime the barcode_type method will be called to format the data value and the barcode font will then be applied to the data in the final output. So in the template create the field and highlight it with you chosen bardcode (make sure the font is available at runtime) and you're done .. for the template anyway. Now you'll need to write the class to carry out the encoding ... dont panic we have an example you can copy.

Advanced Barcode Font Formatting Implementation

For the advanced formatting to work in the template a java class is required that will have the appropriate methods to format the data at runtime. Many font vendors offer the code with their fonts to carry out the formatting, these need to be incorporated as methods into a class that is available to the XML Publisher formatting libraries at runtime. There are some specific interfaces that need to be provided in the class for the library to call the correct method for encoding.
This class has to have the following 3 methods implemented:

/**
 * Return a unique ID for this bacode encoder
 * @return the id as a string
 */
  public String getVendorID();

/**
 * Return true if this encoder support a specific type of barcode
 * @param type the type of the barcode
 * @return true if supported
 */
  public boolean isSupported(String type);

/**
 * Encode a barcode string by given a specific type
 * @param data the original data for the barcode
 * @param type the type of the barcode
 * @return the formatted data
 */
  public String encode(String data, String type);

The class should located in the classpath for the middle tier JVM in which XML Publisher is running.

Note: For EBS users the class needs to be in the classpath for the middle tier and any concurrent tiers that are present.

If in the <?register-barcode-vendor:...?> command, the barcode_vendor_id is not provided. XML Publisher will call the getVendorID() and use the result of the method as ID for the vendor.

An example class that supports the code128 a, b and c encodings follows. You'll need to compile the class and to do that you'll need the XMLP java libraries in the classpath to compile. Once you have that you can simply copy the following code and add your encoding methods.

--------------------------------------------------------------------------------

package oracle.apps.xdo.template.rtf.util.barcoder;

import java.util.Hashtable;
import java.lang.reflect.Method;
import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;
import oracle.apps.xdo.common.log.Logger;
// This class name will be used in the register vendor field in the template.

public class BarcodeUtil  implements XDOBarcodeEncoder
// The class implements the XDOBarcodeEncoder interface
{
// This is the barcode vendor id that is used in the register vendor field and format-barcode fields
  public static final String BARCODE_VENDOR_ID = "XMLPBarVendor";
// The hastable is used to store references to the encoding methods
  public static final Hashtable ENCODERS = new Hashtable(10);
// The BarcodeUtil class needs to be instantiated
  public static final BarcodeUtil mUtility = new BarcodeUtil();
// This is the main code that is executed in the class, it is loading the methods for the encoding into the hashtable. In this case we are loading the three code128 encoding methods we have created.
  static {
    try {
      Class[] clazz = new Class[] { "".getClass() };
     
      ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));
      ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));
      ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));
     
     } catch (Exception e) {
 // This is using the XML Publisher logging class to push errors to the XMLP log file.
      Logger.log(e,5);
    }
  }

// The getVendorID method is called from the template layer at runtime to ensure the correct encoding method are used
    public final String getVendorID()
    {
        return BARCODE_VENDOR_ID;
    }
//The isSupported method is called to ensure that the encoding method called from the template is actually present in this class. If not then XMLP will report this in the log.
    public final boolean isSupported(String s)
    {
        if(s != null)
            return ENCODERS.containsKey(s.trim().toLowerCase());
        else
            return false;
    }

// The encode method is called to then call the appropriate encoding method, in this example the code128a/b/c methods.

   public final String encode(String s, String s1)
    {
        if(s != null && s1 != null)
        {
            try
            {
                Method method = (Method)ENCODERS.get(s1.trim().toLowerCase());
                if(method != null)
                    return (String)method.invoke(this, new Object[] {
                        s
                    });
                else
                    return s;
            }
            catch(Exception exception)
            {
                  Logger.log(exception,5);
            }
            return s;
        } else
        {
            return s;
        }
    }

  /** This is the complete method for Code128a */

  public static final String code128a( String DataToEncode )
  {
    char C128_Start = (char)203;
    char C128_Stop = (char)206;
    String Printable_string = "";
    char CurrentChar;
    int CurrentValue=0;
    int weightedTotal=0;
    int CheckDigitValue=0;
    char C128_CheckDigit='w';
 
    DataToEncode = DataToEncode.trim();
    weightedTotal = ((int)C128_Start) - 100;
    for( int i = 1; i <= DataToEncode.length(); i++ )
      {
    //get the value of each character
    CurrentChar = DataToEncode.charAt(i-1);
    if( ((int)CurrentChar) < 135 )
      CurrentValue = ((int)CurrentChar) - 32;
    if( ((int)CurrentChar) > 134 )
      CurrentValue = ((int)CurrentChar) - 100;
 
    CurrentValue = CurrentValue * i;
    weightedTotal = weightedTotal + CurrentValue;
      }
    //divide the WeightedTotal by 103 and get the remainder,
    //this is the CheckDigitValue
    CheckDigitValue = weightedTotal % 103;
    if( (CheckDigitValue < 95) && (CheckDigitValue > 0) )
      C128_CheckDigit = (char)(CheckDigitValue + 32);
    if( CheckDigitValue > 94 )
      C128_CheckDigit = (char)(CheckDigitValue + 100);
    if( CheckDigitValue == 0 ){
      C128_CheckDigit = (char)194;
    }
 
    Printable_string = C128_Start + DataToEncode + C128_CheckDigit + C128_Stop + " ";
    return Printable_string;
  }

  /** This is the complete method for Code128b ***/

  public static final String code128b( String DataToEncode )
  {
    char C128_Start = (char)204;
    char C128_Stop = (char)206;
    String Printable_string = "";
    char CurrentChar;
    int CurrentValue=0;
    int weightedTotal=0;
    int CheckDigitValue=0;
    char C128_CheckDigit='w';

    DataToEncode = DataToEncode.trim();
    weightedTotal = ((int)C128_Start) - 100;
    for( int i = 1; i <= DataToEncode.length(); i++ )
      {
    //get the value of each character
    CurrentChar = DataToEncode.charAt(i-1);
    if( ((int)CurrentChar) < 135 )
      CurrentValue = ((int)CurrentChar) - 32;
    if( ((int)CurrentChar) > 134 )
      CurrentValue = ((int)CurrentChar) - 100;
 
    CurrentValue = CurrentValue * i;
    weightedTotal = weightedTotal + CurrentValue;
      }
    //divide the WeightedTotal by 103 and get the remainder,
    //this is the CheckDigitValue
    CheckDigitValue = weightedTotal % 103;
    if( (CheckDigitValue < 95) && (CheckDigitValue > 0) )
      C128_CheckDigit = (char)(CheckDigitValue + 32);
    if( CheckDigitValue > 94 )
      C128_CheckDigit = (char)(CheckDigitValue + 100);
    if( CheckDigitValue == 0 ){
      C128_CheckDigit = (char)194;
    }
 
    Printable_string = C128_Start + DataToEncode + C128_CheckDigit + C128_Stop + " ";
    return Printable_string;
  }

  /** This is the complete method for Code128c **/

  public static final String code128c( String s )
  {
    char C128_Start = (char)205;
    char C128_Stop = (char)206;
    String Printable_string = "";
    String DataToPrint = "";
    String OnlyCorrectData = "";
    int i=1;
    int CurrentChar=0;
    int CurrentValue=0;
    int weightedTotal=0;
    int CheckDigitValue=0;
    char C128_CheckDigit='w';
    DataToPrint = "";
    s = s.trim();
    for(i = 1; i <= s.length(); i++ )
      {
    //Add only numbers to OnlyCorrectData string
    CurrentChar = (int)s.charAt(i-1);
    if((CurrentChar < 58) && (CurrentChar > 47))
      {
        OnlyCorrectData = OnlyCorrectData + (char)s.charAt(i-1);
      }
      }
    s = OnlyCorrectData;
    //Check for an even number of digits, add 0 if not even
    if( (s.length() % 2) == 1 )
      {
    s = "0" + s;
      }
    //<<<< Calculate Modulo 103 Check Digit and generate DataToPrint >>>>
    //Set WeightedTotal to the Code 128 value of the start character
    weightedTotal = ((int)C128_Start) - 100;
    int WeightValue = 1;
    for( i = 1; i <= s.length(); i += 2 )
      {
    //Get the value of each number pair (ex: 5 and 6 = 5*10+6 =56)
    //And assign the ASCII values to DataToPrint
    CurrentChar = ((((int)s.charAt(i-1))-48)*10) + (((int)s.charAt(i))-48);
    if((CurrentChar < 95) && (CurrentChar  > 0))
      DataToPrint = DataToPrint + (char)(CurrentChar + 32);
    if( CurrentChar > 94 )
      DataToPrint = DataToPrint + (char)(CurrentChar + 100);
    if( CurrentChar == 0)
      DataToPrint = DataToPrint + (char)194;
    //multiply by the weighting character
    //add the values together to get the weighted total
    weightedTotal = weightedTotal + (CurrentChar * WeightValue);
    WeightValue = WeightValue + 1;
      }
    //divide the WeightedTotal by 103 and get the remainder,
    //this is the CheckDigitValue
    CheckDigitValue = weightedTotal % 103;
    if((CheckDigitValue < 95) && (CheckDigitValue > 0))
      C128_CheckDigit = (char)(CheckDigitValue + 32);
    if( CheckDigitValue > 94 )
      C128_CheckDigit = (char)(CheckDigitValue + 100);
    if( CheckDigitValue == 0 ){
      C128_CheckDigit = (char)194;
    }
    Printable_string = C128_Start + DataToPrint + C128_CheckDigit + C128_Stop + " ";
    Logger.log(Printable_string,5);
    return Printable_string;
  }
}

--------------------------------------------------------------------------------

Once the class is created and placed in the correct class path your template creators can start using it to format the data for barcodes. You will need to communicate to them the following:
1.    The class name and path. In this example: oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil
2.    The barcode vendor id you created. In this example 噌MLPBarVendor?
3.    The available encoding methods, in this example, code128a, code128b and code128c
They can then use this information to sucessfully encode their data for barcode output.

It seems our friends in Redmond have done it again, another security patch has screwed up the Template Builder for Word. We apologise, its tough to know what any given patch is likely to do, this one -KB936021 at least mentions the MS XML Services which we do use so we maybe should have picked it up.

If you are experiencing the following error when trying to load an XML sample file when building a template or trying to connect to the BIP server to create a template:

       Compile error in hidden module: Module_starter

We have the following work around - which seems to work reliably
and does not require you to uninstall the security patch:

1. Go to the Start Menu:
               All Programs -> Oracle BI Publisher Desktop -> Template Builder for Word Language
2. Select your language
3. Click OK.
Now use the builder as normal.

We apologise for this interruption of service, normal Templating experience will resume shortly ... barring any more surprises from Washington.   

Another RTF template enhancement for 10.1.3.3 ... imagine you have a report that maybe has multiple master detail relationships, say a customer listing with each of their invoices listed. If would be nice to be able to expand and collapse customer invoices in the html output providing a level of interactivity.

In previous releases we had an ability to very simply show and hide sections in an output. The sample Customer Orders report that ships with the standalone release showed this off. But it was an all or nothing thang - show all/hide all - we always wanted to allow you to expand/collapse specific regions - we found a horrible way to do it it in a template but it was not practical.

In 10.1.3.3 you can now build such a report, I think it takes the HTML output for publisher standalone to a new level. There are of course some new RTF template commands to add to the template.

<?dhtml:showhide;hide?> <?end dhtml?>
 
<?dhtml-header:?> <?end dhtml-header?>
 
<?dhtml-contents:?> <?end dhtml-contents?>

Now rather than bore you with the written details - Im getting hooked on Jing, thanks AppsLabbers- its a 'project' from Techsmith to record your screen and your voice if you are so inclined all for free as long as you can limit yourself to a few minutes. Those of you that know me will know I found it really hard :o)

Well I was so inclined, so here it is, you can see the show hide in action here. Of course, the sample files are loaded here.
Whats really neat about the solution is that the javascript used to do the show/hide is not part of the BIP server UI but actually embedded in the HTML file itself so you can see that working too, just here.

Not quite here for Apps users - you're getting too much bad news lately - will write about something you can use, I promise.

More on dates and we're getting a little weird here. Now, Im not sure how widespread the use of UnixTime is in reports but a colleague, Vinod, asked how Publisher could handle unixtime and format it into a readable date.

For those of you that dont know about it, its the number of seconds (not including leap seconds) since midnight UTC January 1st, 1970. I have to admit I have not seen unixtime outside the context of an OS and definitely not used in a report for a date - but hey, it's a requirement.

However, Vinod managed to work it all out based on a blog entry from a while ago on data calculations - 'A Date Addition' heres how he did it.

1183100412 in unix time is actually 29 Jun 2007 in real money ... to get this into your output you would use.

<?xdoxslt:ora_format_date_offset('1970-01-01', MOD_T div 86400, ?+?)?> where MOD_T is the element holding the UNIX time value.

The 86400 gets your seconds to days et voila and that ladies and gentlefolk is unixtime ... done!

I have had a few enquiries recently asking why there is a dependency on MSOffice to create templates for publisher - actually there is no dependency. Yes, we have invested in Office by producing a plugin to MSWord to help you build templates, but there is nothing to stop you using an alternative editor to build templates. That said we have not certified on the alternatives but some early testing has shown some promise, there are some issues but we have pinned some of these down to the RTF version that we certify on not being supported in the alternative application.

The biggest difference is that the alternatives either do not have formfields or they have their own versions of them that are not compatible with MSWord's versions. Open Office is a prime example - they do have formfields but they do not have a 'help' tab in which to embed the code for the template thus you end up needing to write the command directly into the document. This leads to a very busy template but it will work. 

Of course, there are alternatives,

• PDF templates - these lack the conditional logic available in RTF templates but are great for static documents.
  
• Flex templates - a new and exciting template type - not great for paper but for building interactive reports its great.
  
• XSL-FO templates - under the publisher covers lies an XSLFO engine that conforms to the W3C standard.

There is another alternative on the horizon - the Online Template builder - not a greatly imaginative name at the moment but this is going to be an all singing, all dancing DHTML/JScript application that allows you to build templates. It's not yet production and its not quite to the level of the RTF template sophistication but the gap is closing fast. Its a drag and drop environment with Office 07-like toolbars - very friendly and we hope pretty intuitive - currently going through user testing as I write.  I think Im allowed to at least provide a couple of screen shots:

Based in the browser, first thing you'll get are some default layouts to work with.

Now, you can create objects - charts, tables, sub totals, etc - with the drag of a mouse. Notcie the ribbon tool bars and the property palettes for minute control.

You're gonna have to wait a little while longer for this ... it'll be worth it I promise.

those of you pulling hair out over getting your barcodes into your bursting outputs under EBS - stop pulling, you're ot going crazy. You're going to have to go old school to get them showing up - the bursting engine will not check the Font Manager for fonts - its an bug or enhancement depending on how you look at it - we'll get it sorted either. In the mean time you can still get those barcodes or other fonts into hte output.

Two methods are available:

1. RTF Properties - I have covered these previously, you create a custom property in the RTF.

Name: xdo-font.<<font family name>>.normal.normal


Type: Text 


Value: truetype.font <<directory+name>>

Its quick, the oly problem being the template might not be very portable unless the font directory is mounted across instances.

2. Config File - thats covered somewhere too. A file, xdo.cfg is put into the JRE_TOP/lib directory. Needs the following to map the font location to a name in the template. 

<config version="1.0.0"  xmlns="http://xmlns.oracle.com/oxp/config/">
   <!-- Font setting -->
    <fonts>
      <font family="3 of 9 Barcode" style="normal" weight="normal">
       <truetype path="C:\WINNT\fonts\3of9.ttf" /> 
      </font>
    </fonts>
</config>

Need to bounce those servers to take effect. Good Luck!

Thats 'spanning' not 'spamming' - although you could use Publisher for that too. I have been working with Pat, Tom and team at Emulex recently and they have had some interesting requirements coming up while they have been developing templates. Terms and Conditions on every even page was one - not a unique requirement but one we worked through and got working - before you ask - look out for that post next week.
Today we're talking spanning, they needed to allow their barcodes to span across columns. Like this:

Notice the barcode is able to span across multiple columns as needed.

We can achieve this because the underlying XSL-FO cell definition has an attribute 'number-columns-spanned' - we can use this attribute to our advantage. If we need to allow the data to span at runtime we can override the spanning attribute in the template.

The 'Span' field is placed in the same cell that you want to span, it contains the following:

<xsl:attribute xdofo:ctx="block" name="number-columns-spanned">3</xsl:attribute>

Just set the number of columns to be spanned. The xdofo:ctx specifies the override level - finding the level is sometimes requires a little trial and error - we're looking into that.

We can now get the output we wanted using the override method. Now, be careful, if you set a span level too high you'll push the layout all over the place.
It's worth  using the 'Tools > Export > XSLFO Stylesheet' function just to see what you can do. Sample files here.

Happy Spanning! 

Ever been building a dynamic URL in your RTF template and when it renders you get some interesting prefixes to the URL you were expecting?

MSWord is good at that, its a 'nanny' application and knows what is best for you, now take your medicine like a good little 'templater'.  Most popular is the 'C:Documents and Settingstdexter' prefix - nice, just what you need. We discovered another one today, the FSG folks are looking into providing a means to drill down on FSG numbers back into the appropriate GL ledger - its going to be a very neat solution that will add a whole new level of interactivity to FSG reports with Publisher. We had to learn how to ignore Nanny first thou ...

To create the URLs to the correct account they need to build it up using some of the data elements around the number element:

<fsg:RptLine RptCnt="p1001" RowCnt="r100002" LinCnt="l100122">
<fsg:RptCell ColCnt="c1000" RealNum="">Totaled 01-01              </fsg:RptCell>
<fsg:RptCell ColCnt="c1001" RealNum="0.000000">0.00</fsg:RptCell>
<fsg:RptCell ColCnt="c1002" RealNum="2685057669.910000">2,685,057,669.91</fsg:RptCell>
<fsg:RptCell ColCnt="c1003" RealNum="0.000000">0.00</fsg:RptCell>

To build the URL we need RptCnt, RowCnt, LinCnt, etc ... straightforward right. Well it would be if Word did not want to help you so much. The values we are interested in are attributes and therefore we reference those with an '@' sign e.g. @RptCnt. See, even my blog software thinks it knows best, putting a mailto link on the '@' - there it goes again!

When we built reportthe URL in MSWord we got a great 'mailto:' prefix on our URLs - no matter how many auto complete switches we turned off- Nanny Word was there to correct us like a 'dogged' school teacher determined to make you spell 'colour' as 'color' cos we're in America, or maybe 'favourite' as 'favorite' and the best transatlantic transgression - 'sulfur' instead of the correct British 'sulphur'. 'Sulfur' seems so '1st grade' - flame me if you will, its a pet peeve. Another reason for hating Word's nannying tendencies - it's America-English dictionary - yes, I could switch it to British-English but seeing as the majority of my internal docs are headed US based eyes I have to live with it. Right, back from rant land - we create URLs in our template like:

http://{servername}/fsg/?callfunct=XXXX&code={../@RptCnt}{../@RowCnt}{@ColCnt}

and because of those @'s we get the following in the output:

mailto:http://tdexter-us.us.orac.com/fsg/?callFunct=XXXX&code=1223|434343|34322|232

That 'mailto:' really screws things up arrrrgggghhh ... its done it again! Here's the solution, just put the URL in and let Nanny help you.Now when you have the document infront of you press Alt-F9 - this will toggle the field codes in the document and you can remove the offending mailto from the HYPERLINK field.

{HYPERLINK"mailto:%7b/MasterReport/fsg:HostName%7d?DRILLDOWN_ACTION=0&DRILL_DOCUMENT_ID=%7b/MasterReport/fsg:ReqId%7d&DRILL_DOCUMENT_NODE=%7b../@RptCnt%7d%7b../@RowCnt%7d%7b../@LinCnt%7d%7b./@ColCnt%7d"}

press Alt-F9 again and your URLs will be evaluated correctly at runtime. Be sure not to get caught by 'Nanny' or you'll get detention!

I promised a 'how-to' get Terms and Conditions onto your outputs - to be more specific to get them on the back of every page of a document e.g. an Invoice that looks like the following.

Notice that the physical pages 1 and 3 are the invoice and 2 and 4 are the terms and conditions. For the logical pages thou, the terms and conditions are not part of total number of pages calculation i.e. there are only 2 pages in the document. Once the document is printed in duplex there are in fact only 2 physical pages.

Also notice that even thou there are 2 invoice pages we need T&Cs on the back of every page - so even thou the document would normally finish on the second invoice page we are forcing the rendering engine to eject that last page with the TandCs on it.

We can achieve this with a little effort and some new commands in the RTF to suppress numbering and to end on an even page. 

We start with a regular template, lets work with something new, a Sales Order template. Running that against the SO data set gets us sales orders with page numbers and headers resetting as expected with the /@section command.

Now, we need to introduce the Terms and conditions page - to get this we insert a section break into the template after the end of the invoice template. Then we go to the File > Page Setup dialog

and set the document to have different Odd and Even headers and footers.
To get the T&Cs into the new page we need to use the sub-templating approach. Sounds bad, but its not - its actually a bonus. How many of your docs need T&Cs? How many of those share the T&C content? Im asking cos I dont know - never look at them on the invoices I receive - I just buy stuff in blind faith me. Anyhoo, even if each doc has a different set of T&C content you can still put it all in the same sub template file and only need to update it in one place to affect all dependent docs - neat!

We have an import statement to pull in the T&C template:

<?import:tcs.rtf?>

there are some things to point out here. If you are testing using the Template Builder for Word you can not call the 'rtf directly. Create your T&Cs template in Word like this one - then use the Tools > Export > XSL-FO Stylesheet and save this as your template. Then modify your import statement to pull in the xsl version of the template.

Of course when you deploy to the server you can use the xdo:// URI for EBS or other URIs for the specfic version you are on.

Inside the TC template file we have a template that simply holds the T&Cs text -

<?template:TC?>
Lorem ipsum ....
<?end template?>

In the main template we then have:

A section break and then in the header of the next page a call to the TC template.

Now all we need are the new commands to get this to work:

<?section:force-page-count;'end-on-even-layout'?>

<?section:xdofo:blank-on;'even-skip-page-count'?>

- this handles the supression of the T&Cs pages from the total number of pages. 

You'll notice that my examples are ejecting an extra page for each sales order - thats a bug and its been fixed - check patch#:6416280

All files available here and the pertinent bits in the template highlighted in red.

An old question and Im not sure why it bubbled to the top of the forum today. Question is from Ruth:

Can anyone help?
I have XML output from the PO Output for Communication report which looks like this

<LINE_ATTACHMENTS>
<TEXT>SLC CUSTOMER REFERENCE=100</TEXT>
<ID>2679</ID>
<TEXT>SLC CUSTOMER REFERENCE=88</TEXT>
<ID>2680</ID>
<TEXT>SLC CUSTOMER REFERENCE=5512</TEXT>
<ID>2681</ID>
</LINE_ATTACHMENTS>

I need to display all three of the text fields but cant seem to find a way to do it. I have tried for-each and if statments but they only show the 1st text field, I cant seem to find away to display the 2nd and 3rd ones.

Can anyone help?

Ruth really really needed help - Im happy to say it was not me answering but one of our active forum members 'vetsrini' - sorry Sir or Marm I can identify you no further from the forum.

You folks need to share your names in your posts or handles - its so much easier to reply to 'John' or even 'Dr Plexi' than User353533 - get a cool handle such as 'The Tick' or maybe 'Sleepless in Seattle' - cheesy maybe, but we know two things about you already - you live 'up t'North' and you're an insomiac who watches mushy flicks with Tom Hanks in it - must be those long rainy nights. Sorry, 'Seattlites' - I have been to Washington about 6 times now and every time it been raining - its always very 'green' in your neck of the woods.

Back to Ruth's issue and 'Vet's' solution  - hope you dont mind I gave you a nickname. Ruth wanted a table showing 'ID' and 'Text' in a two columned table and was a little stuck on how to construct the loop required. Looking at the XML the elements needed are wrapped in the LINE_ATTACHMENTS element but just looping over that:

<?for-each:LINE_ATTACHMENTS?>
<?ID?>       <?TEXT?>
<?end for-each?>

will only give us the first members of the set. You can actually take the looping down a level further ie for-each:LINE_ATTACHMENTS/ID and for-each:LINE_ATTACHMENTS/TEXT and get the looping correct. We now have two loops in their own cells.

Here's the contents of the fields.

Notice that we use '.' in the ID and Text fields - this tells the rendering engine 'insert the current element here' ie the TEXT or ID column thats we are looping on. Now we get out like:

Thanks for the solution 'Vet' and 'Ruth' for the question - keep em coming!

Theres a long thread out on the forum concerning 'totals' or more accurately 'sub totals' and the fact that they are not showing or they are incorrect. I'll get to that but the crux of the problem is to first understand the structure of the data you are using. Back in the heady years of release 4.5 - not so long ago really, it was November 2004. We were like a teenager on a first date, a little awkward, full of energy and ready to impress - but we had a problem. You could build your layout templates in Word but there was no help i.e. no template builder. It meant that you had to build the templates by hand - by today's standards, tedious to say the least.

There was one advantage thou - it forced you to look at the structure of the data to understand how to construct the layout to 'walk' that data hierarchy. Today, we do our best to hide all that and for 'flat' data we provide the means to restructure the rendered data in the output. Becasue you dont spend some time on the knowing the structure then when you are starting out with the template builder or even creating totals from scratch its a little tough to understand. Let's deal with the easier structure to understand first:

Say we have the following:

<INVOICELIST>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
  <G_INVOICE_NUM>
   <INVOICE_NUM>981110</INVOICE_NUM>
   <INVOICE_DATE>10-NOV-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
   <ENT_AMT>122</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>00s</INVOICE_NUM>
   <INVOICE_DATE>07-JUN-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>100</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>FI1009</INVOICE_NUM>
   <INVOICE_DATE>10-MAY-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>1220</ENT_AMT>
  </G_INVOICE_NUM>
 </G_VENDOR_NAME>
 ...
</INVOICELIST>

This layout will generate a vendor name/address followed by a table of its invoices. We want a total at the bottom of the invoice table for each vendor. Notice in the table we have a 'for-each:G_INVOICE_NUM' in the first cell and and 'end for-each' in the last cell. This will force the template to loop over the invoice section of the data and render one on each row of the table.
To add a total for each vendor we need to add a field before the closing field for the G_VENDOR_NAME - we can either add a row to the table and insert the total (1) or put a value directly under the table (2).

The contents of the 'sumENT_AMT' in both cases will be:

<?sum(ENT_AMT)?>

because the field position is inside the end G_VENDOR_NAME field then the total is only calculated for the current level ie at the vendor level. If we out the field outside of the vendor field then we would get a total for all the vendors.

As I mentioned earlier the template builder plugin will do this for you but hopefully you can now see what its actually doing. Check out the 'by Example' document and the samples folder under the template builder install directory. You can get the files for the above example here.

 Next we'll take a look at 'dynamic totals' when we re-group the data in the layout template ...

Last time we looked at adding totals, today we'll take a look from another angle and also look at one of the most powerful features of the RTF templates - that is 're-grouping'.

We need to look at and understand the re-grouping before we get to the totals. Lets assume we have the follow