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: It 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.

Klaus Fabian

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 following XML data:

<ROWSET>
   <ROW num="1">
      <EMPNO>7810</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>7800</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
   <ROW num="3">
      <EMPNO>7876</EMPNO>
      <ENAME>ADAMS</ENAME>
      <JOB>CLERK</JOB>
      <SAL>1100</SAL>
      <DNAME>RESEARCH</DNAME>
   </ROW>
   ... 
</ROWSET>

As you can see the the data is completely denormalized and flattened out. BIP standalone will give you this easy - even under Apps you can get this, this is all PeopleSoft query will give you to work with. A flat list output is great but it would be nice to get some structure on the report, say to group employees by their department and then generate departmental salary totals.

To achieve this we need to use the 'for-each-group:' command - this effectively imposes a 'break group' structure over the flat data but in the presentation layer - this is really powerful. The benefits are three fold:

1. You can simplify your extracts- if you only have to extract flat data then developing and maintaining those complex data structures becomes much easier 
2. Support more layouts - with simpler extract structures you can support more report layouts per extract - think about merging similar report extracts
3. Ultimate flexibility - your layout templates can now dictate the 'break' structure in the report and your users can structure data pretty much how they wish.

Now there is a caveat to this - there's a balance to be struck between 'simpler extracts' and 'template flexibility' - if you do a massive amount of regrouping in the template its going to be slower than doing it in the database - just be aware.

OK, so if we use the following:

<?for-each-group:ROW;DNAME?>

This will give us a structure with a break group created on the DNAME element. We get a hierarchy thus:

DNAME
 EMPNO
 ENAME
 JOB
 SAL

We could of course create as many levels as we want using the regrouping. Lets have a template with the DNAME above a table of employees. 

To add the sub total for each department is similar to the previous example, there's just a little twist. We can use the plugin Insert -> Field dialog.

The contents of that total field are

<?sum(current-group()/SAL)?>

similar to the last time we just have that 'current-group()' command. Current-group() references the dynamic group we created using the for-each-group: command so we need to tell the processor that we want the total salary for each break level i.e. the department. Files for this article are here.

Dave M followed up with a question on the regrouping totals post from a day or two ago.

Would it be possible to reference the group sum values from outside of the loop? For instance, if you wanted to have a separate table at the bottom of the report that contained the department totals for each department.

Of course the answer is 'Yes', but of course there are several ways you could tackle it. You could use our updatable variables to track totals against each department as you move through the XML tree. Not ideal and probably expensive.

You could loop through everything again in the summary section.

<?for-each-group:ROW;DNAME?> 
 <?DNAME?> <?sum(current-group()/SAL)?> 
<?end for-each-group?>

Its another pass thru the data which could be expensive on a very large report. The fastest way to get the summary is going to be to have the summarization at the extract layer. Another alternative might be to build a dynamic XML tree and then use that to render the values. I think this would be just as expensive as the solution above thou - I might be wrong thou.

I received an interesting question from Mark Nelson, one of our SCs last week from a customer he is working with. It concerned fonts - as you know we use MSWord on a Windows platform to create templates. Now before you unix/linux fans ask - no we have not certified on OpenOffice or similar. We have built a few templates by hand using OO - some things work, some dont. Right ow the demand is not high enough to warrant certified support of OO or similar and the porting of the template builder to OO would be a mamouth task.

Moving swiftly on, fonts on Linux - so you build your template on Windows and lets assume you use a few external fonts, say Wingdings for some checkboxes. It all works and tests fine on your desktop - now you need to move to production on the server and you use Linux.

Where do you stand on using the MS Windows wingdings font on a linux server? Will it work? What licensing should I be aware of?

Well, we do not ship those fonts to you nor license them - I must give all credit to Mark for digging the following nugget of information up.   

Microsoft released their TTF fonts for use on other systems with no license charge, provided they were distributed in their original format.  They are no longer available on Microsoft痴 web site, to the best of our knowledge, but they are available here,

An easy way to install Microsoft's TrueType core fonts on linux   

The source forge site provides instructions on downloading and installing the fonts. I have to admit to not having tried the instructions but if any one out there can confirm or deny them I'll share experiences. 

Back from OOW and fighting a cold from all that 'sea level' air - give me the moutains any day. I dont really mean that - I lived by the ocean until we left the UK in '99 - I miss it, but standing atop Pikes Peak is a sight to behold. I promised normal service when I got back from crazy town, this morning there was a question on the forum asking how to achieve conditional rows and columns. I have admit I thought I had covered that here or in the documentation ... I can not find it so here goes.

Conditional Rows

This is pretty straightforward and later releases of the template builder will help you achieve even more easily. Basically you need an 'if' statement that will cover the complete row.

Notice the 'C' field, this contains the conditional code:

<?if@row:number(SAL)>2000?><?attribute@incontext:color;'red'?><?end if?>

see the '@row' thats going to apply the condition to the whole row for us. You can either hand code it or as I mentioned the template builder will do it for you. It provides a friendly dialog for simple conditions - if you have more complex requirements then you are going to have to get your feet wet or hands dirty in the code.

The '@incontext' is a useful addition to the 5.6.3 release shortening code requirements further to update attributes, in this case the color.

The above condition will highlight the text red for the whole row where an employee has a salary greater than 2000.

Conditional Columns

This runs along similar lines to rows ie we have an '@column' command. Sadly the template builder cannot help you here ... yet. But the code is not tough to write. I have written something a little more interesting for the conditional column formatting. I have grouped the employees by department and then added some conditional logic to hide the Job column if the department name is 'Accounting'

Notice the 'CC' fields, I need one for the column header and the data cell, the contents are simple:

<?if@column:DNAME !='ACCOUNTING'?><?end if?>

 The @column defines the extent of the conditional statement ie just to the column, when executed we get the following:

notice the missing Job column.

Now I have used some simple conditions you can of course get pretty complex in the logic, you're limited only by your imagination and your XSL knowledge, there by hangs a tale - you got questions get to the forum, Im there in Colorado mornings and late evenings. Get the template and data here.

Scouring the forum this morning I almost had an 'instant message' session with Darice, we were pinging the forum back and forth so quickly - a great way to communicate and really shows the value of the forum. There were problems getting counting working when re-grouping. We ended up exchanging emails and I think things are going to be working now.
Another thread on counting came up today too and is worthy of mention here. It came from Vadim (nagornyi):

Here is sample XML:

<ROWSET>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>2112</DATA>
 </ROW>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>7985</DATA>
 </ROW>
 <ROW>
 <DEPT>B1</DEPT>
 <DATA>8452</DATA>
 </ROW>
 <ROW>
 <DEPT>C2</DEPT>
 <DATA>2159</DATA>
 </ROW>
</ROWSET> 

Using Table Wizard, I am getting this report:

A1 2112
 7985 
B1 8452 
C2 2159 

Everything OK, but at the report header I need to count the number of DEPT, i.e. 3. How to get it? Thanks.

There are ways to do this programmatically in XSL but its nasty, very nasty. Thats why we have a function that makes life tres simple - 'distinct_values'. Its a Publisher extension that does all the heavy lifting for you in the background, it takes the format:

xdoxslt:distinct_values(Node name)

and returns a numeric value.

In Vadim's case

<?count(xdoxslt:distinct_values(/ROWSET/ROW/DEPT))?>

A word of caution, its expensive, just like DISTINCT in SQL its going to require a lot of processing to read the node tree in and process it eliminating duplicates. There are other slightly less expensive ways to achieve the same using XPATH but for now, just use it wisely.

An update - Nilanshu came up with the XPATH solution I was aluding to above ... remember the data must be ordered by the data you want to count and remove the duplicates from.

If your data is ordered, then you may simply count all ROWS that have a different DEPT than the previous one, keeping into account the first row of course. This might give you a better performance for large XML files.

<?count(ROW[position()=1 or ./preceding-sibling::ROW[1]/DEPT!=DEPT])?>

XPATH 2.0 does provide something similar, but AFAIK XPATH 2.0 does not work in Oracle XMLP yet.

<?count(distinct-values('DEPT')?>

Happy Thanksgiving to those readers in the US - for the rest of you Happy Thursday. Before I head off to help eat some monstrous great bird with a group of friends and family I have a great customer tip to share.

You may remember the article I posted last week on a check security solution from Evergreen - they provide the removable hardware that holds the check singatures and micr font, it sits between server and printer. They also now provide the software solution that integrates with Publisher and enables the signatures to be read and pulled into the final document.   

I received a mail from Dustin Frysinger and Keith Preston from the National Lime & Stone Company - they have been working on their own check security solution wanting to come up with a lower cost alternative. They had not invested in a SIMM printer module to hold the check signature images but they still wanted a hardware solution. I have mentioned mounting a USB or memory card as an accessible drive in the past; Dustin and Keith have actually implemented it. They have the check signatures on a memory card that can be kept locked up until a check run needs to be executed. When they are ready, the memory card is inserted into a card reader on the server and its mounted as a drive - I have played with mounting the card on a web server and gotten that working in the past. They took a simpler route and just mounted it as a drive - simpler is always better in my book - I have to kick myself sometime for being too fancy in my solutions.

In their template they have logic to pull in the signature images as required i.e.

url:{'file:///media/USB_DISK/sig1-5.gif'}

Rather than a regular URL they are using the 'file' URI to pull the image in. Of course that drive needs to be accessible from where ever Publisher is running. If the check print is run without the card in place then signatures are of course not pulled in and there is a blank area left. Still a little vulnerable may be - my bank never seems to check my signature until after tha mony has been drawn - maybe things are stricter for a company check. However its not hard to add the logic to put in a 'VOID' image if the signature image is not present - I blogged this a while back ... actually it was back in April 06 that's more than 200 articles ago! If you implement that logic alongside the card method then I think the checks can be as secure as they can be.

Its a nice solution to a tough problem - Im working with a few customers that do already have a printer SIMM module that they need to communicate with via escape sequences. Basically, we need to inject PCL codes into the document prior to sending to the printer to ensure images are pulled in as necessary from the printer module. Its a tough nut to crack because we do not generate PCL ourselves so we need some injection method - Im hoping to be able to publish that soon.

Dustin and Keith have been up to a host of other stuff but I'll save that for another day. If you want to know more about their approach, let me know via comments or mail and I'll hook you up - Im sure they'll be cool with that.

Now, Im off to stuff myself with stuffing, veg out on veggies and maybe eat some turkey - its a tough time of year for the poor old turkey.  

If you are trying to use sub templates in 10.1.3.3 or you used to have them in previous releases and they are not working for you now ... read on. You are not going mad ... we slipped some new security into the release and by default the security is 'On' and stopping 'external content' from being pulled into your templates.

This property controls whether to use secure file I/O mode for the XSLProcessor and for the XMLParser. When it is set to true, file I/O such as import sub-template or import an XML document is not allowed. Its set via the Admin > Runtime Configuration pages. It can also be set at the report level too.

So, now you know, apologies for any confusion, misery and heartache caused! 

Check printing seems to be the hot topic right now ... I have seen far too many requests for a check print template out in the publisher-sphere. Its a little complex with the cheque or check (depending on your version of English) at the top. It then repeats the invoices to be paid twice allowing 10 rows (this is adjustable) for each. If it spills to a second page then the check portion is not printed.

It also has some conditional formatting around the check signature images - this is a soft image solution but could easily be used with the 'hard' solution I blogged last week from Dustin and Keith from the National Lime & Stone Company.

In the zip file that contains the template, data, PDF output and signature images there is also an annotated version of the template that attempts to explain how the thing actually works under the covers.

Its delivered as is - its actually based on an Oracle Report XML output from EBS so apologies to non EBS folks but it should be enough to get you up and running at least.

And before you try, not that you would, but you might, thats a random routing and account number :0) 

If you have been struggling to build templates for Purchase Orders in EBS with their XSLFO restriction I have relief. Maybe you have been building RTF templates and using the export facility to get the XSLFO template from it. May be, if you're mad you have learnt the XSLFO standard and have been building XSLFO templates from the ground up?

You no longer need pull your hair out, the Purchasing team have released a patch to allow you to use RTF templates. Its actually been out for quite some time and I apologise for not telling you sooner. They sneaked it out and I have had what can only be described as 'fun' trying to find the patch in our bug database and metalink. Anyhoo, the patch number is 4670662 - you need to be on 11i10 as I understand it and if you are on later versions of 11i10 you probably already have it. If you are not sure just check that you have the following version or higher of

java/communicate/PoGenerateDocument.java 115.23.11510.17

Through streaming eyes and a nose that wont stop running, its a doozy of a cold/flu. I found a nice question yesterday on the forum from Chris (chrish6):

Hi All

I have just spent the last couple of hours trying to do something which I was hoping would be fairly simple - the only thing I have figured out is that it is not simple.

Heres my problem, I have data structured like so

<HORSE>
 <HORSE_NAME>Chris One</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Two</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Three</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Four</HORSE_NAME>
</HORSE>

I want the data to come out in RTF data like this

Chris One Chris Two
Chris Three Chris Four

I really want to avoid changing the data file.
So I don't come across as a lazy boy I have looked into a couple of things....
Tims blog on Rows and Columns - this is the direction I need to go but I don't get how to force a new line - I tried it and got all records on one line.
I've read various forum posts a blog by another guy doing invoice stuff - which seems over complicated for what I need.
Tim - any chance you could do a follow up to your rows and columns blog??
I'm running latest BIP,
my data can have x amount of records and may be an odd or even amount of records.

Not sure on the horse reference, maybe Chris has a race horse stable and wants to generate address labels for them. I came up with a solution that requires you to specify the number of columns you want in the table. In Chris' case its 2, it relies on a small piece of XPATH code to get specific records or horses to show up.

All we have in the template is a 2 celled table, inside each we have a complete for-each loop with the HORSE_NAME and an end loop.

The contents of the F fields are :

<?for-each:HORSE[position() mod 2=1]?>   -   <?for-each:HORSE[position() mod 2=0]?>

the [position() mod 2 =1] expression tests the result of dividing the current record number by two and check the remainder. This puts the odd records into the first column and even records into the second column.

Now your not tied to two columns, just add a column and adjust the 'mod' calculation to have

<?for-each:HORSE[position() mod 3=1]?> 
     - <?for-each:HORSE[position() mod 3=2]?> 
            - <?for-each:HORSE[position() mod 3=0]?>

to get this output

Sample template and data here.
You can keep going as long as you wish, just need to work out the 'mod' calcs and of course if you dont want horses but maybe address labels, just add the fields you need and you're 'off the races' as it were.

A great colleague and friend of mine, David Haimes has recently started blogging about Financials and more specifically on the Intercompany product - his pride and joy.

'Yeah' so what? I hear you cry - its not about reporting or BIP'

Well, today it is, its a bit of a teaser for an upcoming article I hope Dave is going to get into some detail on soon and I can then followup on with a more generic 'how-to'.

Dave and I go waaay back, well in Oracle terms, to 1998 when I joined Oracle in the UK. We both worked in the Financials for EMEA development team. Building and maintaining localizations for the financials product across mainly Europe. It was a really tight team run by Paul Snelling, a curry eating, beer swilling monster of a man who I thought was fantastic, very scary but fantastic. The things we got up to would make your Grandma blush, they made me blush - but enough of Paul's 'party tricks'.
We had a sister team based in sunny California and in '99 I got the chance to come out and attend the Forms6i roll out training - remember, this was just before EBS11i was released so Forms6i was hot, well for us Apps bods it was - Forms9i was probably already out by then but you know how up to date Apps stayed back then.

Anyhoo, the week I was in California it poured with rain every day, I got lost on the 101, a feat in itself. The number of times I drove by my hotel! I could see it from the freeway but could not for the life of me work out how to get there. In my defense, the jet lag meant that I woke at some ungodly hour in the morning and thought, I might as well get to the office - in the dark. I left the office in the evening, in the dark, jazzed up on gallons of coffee to make it through the afternoon - in my addled state my brain could not get itself around those cloverleaf interchanges, so it was no wonder I couldnt get to the hotel, well thats my excuse.

After coming back to good ol'Blighty and disseminating what I could remember of Forms6i to the team, it was back to the wonders of Interest Invoice - a mean plsql, forms and reports solution that I had been tasked with, 'getting to work.' In the spring of '99 I then got the chance to come to America with my family to live. So with 'rose tinted spectacles', expecting white picket fences, acres of cheap land and a mansion to live in we arrived in California - oh, what a shock! California is sunny, warm and full of great folks but boy is it expensive!

Suffice to say, we settled in a tiny apartment and tried to work our way up, I skateboarded to work in shorts and t-shirt - life was good. About 10 months later Dave arrived from the UK - slept in our garage for a month because of the housing shortage in San Fran and the rest, they say is history. May be not history, I could go on and on ... and on, but I wont.

Back to Dave's blog - as I mentioned above, the latest entry is about reporting and how the intercompany team used the Publisher common region to host interactive reports within their OA Framework pages providing drill down from intercompany transactions to the sub ledgers. As I mentioned, I will follow up with a how to on using the region in your pages - just as soon as I can get the danged OAF plugin to work with JDeveloper.

Lots of questions around watermarking documents recently - I had a bit of a breakthrough last week on how to create dynamic watermarks using one of those Easter Eggs I mentioned a while back. But before we get too far along lets start at the beginning, what do we mean by 'watermark' - for those who have not seen one - not many of you I hope. Its a piece of text or an image that is striped through a document. Its generally 'greyed' or partially transparent and appears behind the main document.

that was a piece of static text, you can also use an image.

So how do you add one - kinda depends on your version of MSWord you're using. I have to admit to being a 2003 user, my 2000 version has given up the ghost on my laptop. So, Im chickening out of trying to get it working and grab screen shots - Microsoft document it really well here - http://support.microsoft.com/kb/211324.

For those of you using 2003 its a little simpler.

1. Format > Background > Printed Watermark

2. This pops a dialog -

Select Image or Text Watermark. For image you just find the image you want - remember to get a transparent gif format if you dont want an ugly fill around the image (see above). For the text you can either pick a seeded string or enter your own.

3. Now 'OK' out of the dialog and your done.

If you want to modify the size or angle of the watermark just double click on the header or View > Header/Footer. The image or text (actually a WordArt object) can be edited, moved, sized, etc.

Pretty straight forward stuff - there is a caveat thou, it will only currently work for PDF outputs.

Next ... dynamic and conditional watermarking

So static text is all very well for a document but lets say we need something more dynamic, maybe the text is in an XML element in the incoming XML data for the report or maybe we need some conditional logic to show a specific value based on some flag value in the incoming XML.

Let's deal with the simple situation first - pulling a value from the incoming XML data. This is pretty straightforward, use the same method to add the watermark in Word but rather than enter a fixed string, just reference the XML element that is holding the watermark text.

Just be careful here, you might need to provide the full path to the element or use a .// prefix to get the text to render correctly.

You can also assign an element value to an XSL variable, so if you want to concatenate a few strings in your XML or static test you can build up watermark text quite easily.

For the more complex case above where we need some logic, I needed to scratch my balding pate a little. Roc has posed a question on the forum wanting some conditional logic around the watermark.

Hi XMLP Gurus,

Is it possible to code watermark in XMLP by inserting a field and coding the condition in that field. Depending on the condition I can print the watermark text on the output. Is this achievable?

Thanks in advance...

I took a little while to get an answer - I was getting a little desperate looking to build some XSLFO template to handle te logic - I stumbled upon a very useful inline 'if' statement that was hidden away - yes, another hidden feature that even I did not know about. It takes the format:

xdoxslt:ifelse(boolean expression ,true result, false result)

in use we get something like

xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled','Approved')

So if the WM_FLAG element contains a 'C' then the 'if' statement returns 'Canceled' otherwise 'Approved' - now you can actually nest the 'ifelse' statements so we can get some serious logic into the inline 'if'. In this case, for the watermark we used the following:

<xsl:variable name="wMark" select="xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled','Approved')"/>

this assigns the result of the 'if' to a variable wMark - we then use

<?$wMark?>

as the value in the watermark text. This then gets resolved at runtime correctly based on the 'if' statement, neat huh?

I have to mention that we have an issue at the moment if you are using the @section command - we're looking into that.

Finally, there is another method to add a watermark - via a java API that we provide. Its a post document generation step and of course you need to have some means of calling the java API. Either a java concurrent program or maybe someting funky in the after report trigger if you are using OReports to generate the XML ... hmmm thats needs some thought.

Using the API is pretty straightforward, its actually documented here for all versions but I'll give you the basics.

Use the SetTextDefaultWatermark( ) method to set a text watermark with the following attributes:

? Text angle (in degrees): 55
? Color: light gray (0.9, 0.9, 0.9)
? Font: Helvetica
? Font Size: 100
? The start position is calculated based on the length of the text

Alternatively, use the SetTextWatermark( ) method to set each attribute separately. Use the SetTextWatermark() method as follows:

? SetTextWatermark ("Watermark Text", x, y) - declare the watermark text, and set the x and y coordinates of the start position. In the following example, the watermark text is "Draft" and the coordinates are 200f, 200f.

? setTextWatermarkAngle (n) - sets the angle of the watermark text. If this method is not called, 0 will be used.

? setTextWatermarkColor (R, G, B) - sets the RGB color. If this method is not called, light gray (0.9, 0.9, 0.9) will be used.

? setTextWatermarkFont ("font name", font size) - sets the font and size. If you do not call this method, Helvetica, 100 will be used.

The following example shows how to set these properties and then call the PDFDocMerger.

Input:

? PDF Documents (InputStream)

Output:

? PDF Document (OutputStream)

For example

import java.io.*;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;


...


public boolean mergeDocs(InputStream inputStreams, OutputStream outputStream)


{
  try
  {
    // Initialize PDFDocMerger
        PDFDocMerger docMerger = new PDFDocMerger(inputStreams, outputStream);
    // You can use setTextDefaultWatermark() without these detailed setting
        docMerger.setTextWatermark("DRAFT", 200f, 200f); //set text and place
        docMerger.setTextWatermarkAngle(80); //set angle 
        docMerger.setTextWatermarkColor(1.0f, 0.3f, 0.5f); // set RGB Color
    // Merge PDF Documents and generates new PDF Document
        docMerger.mergePDFDocs();
        docMerger = null;
        return true;
      }
        catch(Exception exc)
      {
        exc.printStackTrace();
        return false;
       }
     }

Not too tough, right!

Got a chartin' question today, 'can we build Bubble charts with BIP' - the short answer is 'Yes.' Those of you that have spoken to me will know Im no good at short answers - so here comes the long answer and a 'how-to' 

For those of you that have not seen or heard of them.

A Bubble chart is a variation of a Scatter chart (if you dont know what a scatter chart is ... go look it up :) in which the data points are replaced with bubbles. A Bubble chart can be used instead of a Scatter chart if your data has three data series, each of which contains a set of values.

For example, the following XML contains values for three types of data not including the product line name: number of products in product line, dollar value of sales for the product line, and percentage size of market share for the line - not grate data but you get the idea, I hope.

<BUBBLES>
 <BUBBLE>
  <PROD_LINE>Electronics</PROD_LINE>
  <PROD_NUM>10</PROD_NUM>
  <SALES>100000</SALES>
  <MKT_SHARE>15</MKT_SHARE>
 </BUBBLE>
 <BUBBLE>
  <PROD_LINE>Household</PROD_LINE>
  <PROD_NUM>13</PROD_NUM>
  <SALES>135000</SALES>
  <MKT_SHARE>25</MKT_SHARE>
 </BUBBLE>
 <BUBBLE>
  <PROD_LINE>Garden</PROD_LINE>
  <PROD_NUM>20</PROD_NUM>
  <SALES>200000</SALES>
  <MKT_SHARE>30</MKT_SHARE>
 </BUBBLE>
</BUBBLES>

Now, we can plot these as a bubble chart - there are 3 components to the bubble plot the x-axis and y-axis position and the diameter of the bubble. Sadly, the charting wizard in the Template Builder does not handle Bubble charts so we have to get our hands dirty and get into the chart XML definition. We need to pass the data above to the charting engine, its needs to be in a specific format.

<DataValues>
 <RowData>
  <Cell></Cell>
 <RowData>
</DataValues>

The RowData group needs to repeat for as many data points or bubbles we need to plot. For our data, we have 3 bubbles so we would need to pass.

<DataValues>
 <RowData>
  <Cell>10</Cell>
  <Cell>100000</Cell>
  <Cell>15</Cell>
 <RowData>
 <RowData>
  <Cell>13</Cell>
  <Cell>135000</Cell>
  <Cell>25</Cell>
 <RowData>
 <RowData>
  <Cell>20</Cell>
  <Cell>20000</Cell>
  <Cell>30</Cell>
 <RowData>
</DataValues>

The first Cell value maps to the x-axis - the Number of Products in the product line
The second Cell value maps to the y-axis - the total sales
The third Cell value maps to the diameter of the bubble - the market share
These are all bound to the product lines in the chart legend.

Now its not so tough to get the data we have mapped to the required format in the chart: definition. Heres the bubble chart definition.

chart:
<Graph graphType="BUBBLE">
 <X1Title text="No of Products" visible="true"/>
 <Y1Title text="Sales" visible="true"/>
 <Title text="Market Share Analysis" visible="true" horizontalAlignment="CENTER"/>

 <LocalGridData colCount="{count(.//BUBBLE)}" rowCount="{count(.//BUBBLE)}">
 <RowLabels>
  <xsl:for-each select=".//BUBBLE">
   <Label>
    <xsl:value-of select="PROD_LINE"/>
   </Label>
  </xsl:for-each>
 </RowLabels>
 <ColLabels>
  <xsl:for-each select=".//BUBBLE">
   <Label>
    <xsl:value-of select="PROD_LINE"/>
   </Label>
  </xsl:for-each>
 </ColLabels>
 <DataValues>
  <xsl:for-each select=".//BUBBLE">
   <RowData>
    <Cell><xsl:value-of select="PROD_NUM"/></Cell>
    <Cell><xsl:value-of select="SALES"/></Cell>
    <Cell><xsl:value-of select="MKT_SHARE"/></Cell>
   </RowData>
  </xsl:for-each>
 </DataValues>
 </LocalGridData>
</Graph> 

Ignore the top part right now and focus on the <DataValues> section:

 <DataValues>
  <xsl:for-each select=".//BUBBLE">
   <RowData>
    <Cell><xsl:value-of select="PROD_NUM"/></Cell>
    <Cell><xsl:value-of select="SALES"/></Cell>
    <Cell><xsl:value-of select="MKT_SHARE"/></Cell>
   </RowData>
  </xsl:for-each>
 </DataValues> 

This XSL will map the current data into the required format for the charting engine. The rest of the XML for the chart is regular stuff just need to set the graphType to "BUBBLE" and we're done.

Yes, bubble charts are possible - just requires some effort, that I hope we'll eliminate in a future release of the Template Builder but at least you now know how to build em. Template, XML and output here. Before you ask, I have tested bubbles back to release 5.5 - not so good quality as SVG output was not supported back then but it works.

Update

A couple of follow ups ensued which are worth sharing:

How do I remove the grid lines and big quadrant lines?

To remove the regular gridlines:

<O1MajorTick visible="false"/>
<X1MajorTick visible="false"/>
<Y1MajorTick visible="false"/>
<Y2MajorTick visible="false"/>

These will turn off all grid lines - but not the 'quadrant' lines. For those you need:

<QuadrantLine lineColor="#64ff" visible="false"/> - I just left the lineColor attribute in there for those of you wanting to chage the color rather than remove them.

How can I change the Bubble colors?

Inside the graph tag you can create a set of 'series' to hold the colors.

<SeriesItems>
<Series id="0" color="#cc0000"/>
<Series id="1" color="#ffcc00"/>
</SeriesItems>

You'll need as many id's as data points or as you want different colored bubbles. If there are more bubbles than colors then the colors will loop around again.

Last week, at least I think it was last week, it feels like I have been writing in a 'cold' 'miserable' fog lately. I finally visited the doctor today, 'walking pneumonia' was the prognosis - it explains a lot. I have been feeling completely worn out these last weeks with a cough and what can only be described as a 'snotty' nose ... apologies for the conjured image.
Anyhoo, Im now drugged to the eyeballs in the great American tradition. Im not allowed to be my 'reserved English self' here, just 'getting on with it' - when I used to walk into a doctor's surgery in the UK and was asked 'so how are you?' I always found myself saying 'yeah, Im good' - 'then why are you here to see me' was the doctors quippy retort. Very drole! I have been converted, when I go in now, which is still pretty rarely - I ensure a good throaty cough is my answer to any question ... nice.

Moving on, or back to last weeks entry on Watermarking II. Buried away in that post was mention of a little known feature that I thought was worth its own entry. Those of you that know Excel will know that it has an 'inline' if statement, it's very useful - well its come to BIP. It's a simple command that takes the format:

xdoxslt:ifelse(boolean expression ,true result, false result)

in use we get something like

xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled','Approved')

So we can return strings, numbers or even element values from the if statement. You can even nest 'if' statements within the command e.g.

xdoxslt:ifelse(.//WM_FLAG='C?,'Canceled',xdoxslt:ifelse(.//WM_FLAG='A?,'Approved','Pending'))

I have not seen how deep I could nest but it ought to be as deep as you wish. Hopefully it should prove useful to you, its available from release 5.6.3. onwards.

I have been exchanging mails with a colleague in the field regarding the 'smoothness' of our charts. By default in our PDF outputs we generate SVG chart output to get the best quality output. His customer was still not happy - he sent me a copy of their chart:

 and a snap shot of their problematic output.

Now I got to see the issue - they are already using the SVG output but at 1600% resolution you can see that the chart lines are actually made up of individual smaller lines. Im not SVG expert but I assume this is the easiest way to implement a chart - using an arc to get the curves smoother would take alot of time, working out which way the curve ought to go.

Im also assuming that the customers printers are of such a resolution that the quality of the print outs makes the chart all but unreadable.

Its a limitation of the BIBeans charting engine that the chart is not smoother - in the engine's defense there are 100s of data points in this chart. Looks like share price or similar chart with so many points. This got me thinking about the finance charts you get to see on Yahoo and Google - the newer charts are very cool and very Flash like (more on that another day) - both sites a feature a 'time slider' allowing you to move a time slice across the total stock chart, heres the Oracle chart on Yahoo - check out the Time range widget bottom right corner.

If you play with the slider you'll hopefully notice that the charting engine appears to have some algorithm to reduce the numer of data points as the range is extended, effectively 'smoothing' the chart. If not, check out the video, focus on the chart as the slider moves, you'll see the chart suddenly 'smooth' as the data points are effectively reduced.

Chart Smoothing Video

Now this would be a fantastic enhancement to the BIBeans charting engine - not there yet thou. An area I'll try to investigate or even better you can investigate if you have nothing better to do over the holidays is to come up with some algorithm in the template or an extension to check how many data points there are and reduce them if necessary to give the chart a smoother look . Not going to be easy but might prove a very useful feature in lieu of the charting engine enhancement.

This was going to be a holiday posting but today I found Bill on the forum who I think might actually be able to use this post ... funny world. That said I may be completely 'off' and the following prose is completely useless.

Bill's question - I want to make the report colorful, the color should be same for same group, the color should be change for every other group. Kind of did some research but cannot figure out yet.

You'll see in my follow up question - do you want colored fonts or backgrounds - either are pretty straightforward. The background needs a trick but nothing like making an elephant disappear more like hiding a coin behind a 2 year olds ear.

Background

Font

If we take the font colors first, they are easy, just change the font color for each group you have in the template. So in my case the vendor group is red and the inner invoice group is green.

For the background, all I have done is put the whole template inside a table except for the Vendor group fields. Then just color the table first and then the inner grouping table so its kinda on the top of the first table.

and you're done. Of course if you have multiple groups, just color accordingly. Sample files here. Hope thats what you were after Bill ?

I knew I had jumped the gun ... the devil is in the detail as it were - Bill followed up yesterday on the forum with a little more detail on what was needed.

Thank you for your fast response, actually I should have made my question clearer. Let's take your sample data, what I need is like this:

For group 1 Nuts and Bolts Limited, make background yellow,
981110 10-NOV-04 Standard EUR 122
100000 28-MAY-04 Standard FIM 122
100001 28-MAY-04 Standard FIM 200
1 03-JUN-04 Standard FIM 400
100004 28-MAY-04 Standard FIM 100
00s 07-JUN-04 Standard FIM 100
FI1009 10-MAY-04 Standard FIM 1220
Stop make background yellow.

For group 2 Tick Tock Clocks, make background blue,
100002 28-MAY-04 Standard DEM 235
100003 28-MAY-04 Standard DEM 100
2001 01-JUN-04 Standard EUR 1000
2002 01-JUN-04 Standard EUR 1500
2003 01-JUN-04 Standard DEM 1000
2004 01-JUN-04 Standard DEM 2000
3001 04-JUN-04 Standard EUR 1000
FI1011 25-MAY-04 Standard DEM 2000
Stop make background blue.

For group 3, make background back to yellow
Big Bike Motorsports
3001 01-JUN-04 Standard SEK 1000
FI1013 02-JUN-04 Standard USD 25000
Stop make background back to yellow

For group 4, make background back to blue
Second Cars
FI1010 20-MAY-04 Standard FIM 2440
Stop make background back to blue

We're mixing 'Groups' and 'records' here, in my original post I colored fonts and backgrounds based on the group or 'for-each'. What Bill is decribing above is to alternate the coloring of records within a group. Still doable, still not 'disappearing an elephant' but maybe 'pulling a bunch of flowers out of your sleeve' - OK OK I'll stop with the magician analogies.

Heres the template, looks similar to the ones from yesterday but without the shading.

The only addition is the IFColor field - inside there is some code to manipulate the table fill color based of some boolean expression.

<?choose:?>
 <?when:position() mod 2=0?>
 <?attribute@incontext:background-color;'Yellow'?>
 <?end when?>
 <?otherwise:?>
 <?attribute@incontext:background-color;'Red'?>
 <?end otherwise?>
<?end choose?>

This relies on the position() and 'mod' functions to allow us to, in this case alternate between two colors we can increase the mod value to alternate between more if we wish. If you are going to stick with just 2 then we can simplify further.

Just set a color background on the table and in the formfield use:

<?if:position() mod 2=0?><?attribute@incontext:background-color;'Red'?><?end if?>

So by default the color will be yellow but when the 'mod' condition is satified ie every even row you'll get a red background. Of course standard HTML color names are supported along with RGB HEX values.

Pieter from the support organization contacted me yesterday - if you ever get him on one of your TARs, you have hit pay dirt, one awesome dude! He was pinging Leslie (documentation) and me about a feature that he could not find in the user docs - it was another that has slipped through the cracks. 

If you have links in your report outputs until recently you had no control over whether the link, when clicked, would open in the same page, or a new window - in fact by default it would be in the same window. We do have support to allow you to control the target a la HTML but its not documented until now.

There are a couple of methods depending on your version of Word. If you dont have 2003 or above you can use the following method.

In your links just add the following ??target=XXXX ie www.oracle.com??target=XXXX where XXXX is the target you want e.g. _blank. Your links will look a little funky but you'll get the desired effect.

If you have Word2003 or later you can use native Word functionality to set the target.

You can also pass a dynamic value to the template to define how the link should work. Use '{$myTarget}' in place of XXXX for 2000 and for 2003+ just put{$myTarget} in the target dialog. Of course 'myTarget' is a template parameter that you define in the template and pass at runtime.

I spent some of today trying to solve a none too common (that I have seen) problem, that is to add a gantt chart to your BIP or BIEE outputs. BIBeans, the charting package we use does not support them - at least not in a way we can use them, they have a solution for EBS OAF pages but not for a report.

For the uninitiated a gantt chart ( I dont know why but my brain wants to call it a 'gnat chart'- no disrespect Mr Gantt) shows a timeline for say a project and all of its components, when will one sub project end so another can start - the best known and the bane of my life a few years back is/was MSProject - we had a monster of a project plan that stretched into the distance and over the horizon at times, were the developers even going to be here in 12 months time? This was back in the dot com boom  - 'interesting times'as they say in Ankh Morpork!

The example above it pretty simple but you get the idea. I sat and thought for a while and came up with a couple of options.

1. Get 'noodling' (non-technical term for hacking) with other bibeans charts and try and combine a few together to get the desired result.

2. Have a bash with the MSWord shapes to create a gantt output.

Option 1 was going to be lots of work as the BIBeans XML chart defintion is not well documented - I dont want to wade through that DTD again. I have multiple chart entries on this blog to try and clear the mist but my glasses are still fogged up. There is some good news on the horizon on that front - the new version of the template builder has taken a huge stride forward when it comes to inserting charts. It even supports inserting the bubble charts I wrote about a while back.

Option 2 was much more palatable for me and ought to be some fun. I have not touched the shape support for a while and I have been looking for a good business requirement for them ever since we released them other than the gauge I built last year. Should just take a bit of calculation to get the shapes to behave and we would be good to go.

For those of you that dont know, you can create drawings in Word, using predefined or custom shapes, you can combine them together to form more complex objects, such as the gauge. Now Publisher, gives you the ability to then manipulate the shapes or sub shapes - you can duplicate, add text, skew, move, even write text along a line. If you take another look see at that gantt chart up there and break it down, its nothing more than a rectangle thats moved, stretched and duplicated down and across the page.

So we dont get bogged down in what the data is I thought I come up with a calendar report of someones weekly meeting schedule - its a nice straightforward example.

Thats the output from Publisher - not bad, needs a little tidying  but for a first effort and as a mean to convey how to do it, its great.

How did I do it? - well all of our reports need some data so I knocked up some XML.

<MEETINGS>
 <DAY>
  <NAME>Monday</NAME>
  <MEETING>
   <START>9</START>
   <DURATION>1</DURATION>
   <DESCRIPTION>Team Meeting</DESCRIPTION>
  </MEETING>
  <MEETING>
   <START>11</START>
   <DURATION>2</DURATION>
   <DESCRIPTION>Discuss BIP</DESCRIPTION>
 </MEETING>
 <MEETING>
   <START>14</START>
   <DURATION>1</DURATION>
   <DESCRIPTION>Customer Call</DESCRIPTION>
 </MEETING>
</DAY>
...

  Of course I built it simple but there are 3 pieces of information I need about the meeting, the day, the time and the duration - I added the description cos I was getting 'fancy' - a non technical term for 'showing off'

The template could not be simpler in its layout.

So my time slots were fixed, we could have made them dynamic but I want to focus on the shape manipulation. We have a simple for-each loop in the first cell to get the days of the week in rows down the page. Then there is that yellow box - if you check our docs you'll see that to manipulate the shape we embed some commands into the Web tab of the object's properties. In this case I had:

<?for-each@shape:.//MEETING?>
<?shape-offset-x:((number(START)-8) * 65)?>
<?shape-size-x:DURATION?>
<?end for-each?>

The for-each@shape tells our parser that we want to generate shape instances for this loop.

The shape-offset-x - defines how much to offset each shape in the loop. The actual offset value '((number(START)-8) * 65)' is not that tough to work out. Our starting hour of the morning is 8am and the shape is sitting on the border of the 8am cell so we need the shape to be offset from that point - remember we know when the meeting is so our offset is going to be 'start time - 8'. Need to be careful here to use the 24 hour clock a meeting at 1pm using the 12 hour clock yields some interesting results. Finally, the 65 is the point width of the columns in the table row. To get this value, just flip Word to measure in Points rather than Inches or CMs - Tools > Options > General. Then get into the table properties and into the Column tab.

The shape-size-x:DURATION defines how big the rectangle should be - we have the duration of the meeting. So a 2 hour meeting will result in a block twice the size of the original to fill the 2 hour slot.

And thats it to get the blocks repeating across and down the page.

To get 'fancy' and add the description we need to build up a composite shape. You could just use the Add Text command on the shape but you get some funky font stretching when the shape has to be enlarged. So go with the composite - you need the basic rectangle and a text box.

In the rectangle you need -

<?shape-size-x:DURATION?>

<?shape-text:DESCRIPTION?>

<?for-each@shape:.//MEETING?>
<?shape-offset-x:((number(START)-8) * 65)?>
<?end for-each?>

This gets the whole thing to repeat across the page as needed, et voila!

I should point out that when you see the template you'll see that the shape is not exactly at the 8am cell edge - its slightly offset. Its a feature, we want you to play with your creations until they are perfect. Seriously, Im not sure why it needs it, something funky in Word maybe - you just need to nudge it around to get it perfect. You can get the data, template and output here. 

From here, Im moving on to a more complex output with dynamic rows and columns and then onto the fabled org chart which, with a little jiggery pokery I think I can tease out of our shape engine.

Happy Gantting!

I did a couple of posts a while back on totaling here, here and here - I recently got a question asking how to do some conditional totaling, more specifically in a list of invoice amounts how I can total according to an invoice type - something along those lines anyway. To make myself clearer assume you have the following data:

<G_INVOICE_NUM>
 <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
 <GL_DATE>10-NOV-04</GL_DATE>
 <NVL_I_DISCOUNT_AMOUNT_TAKEN_0>0</NVL_I_DISCOUNT_AMOUNT_TAKEN_0>
 <INV_TYPE>Standard</INV_TYPE>
 <INVOICE_NUM>981110</INVOICE_NUM>
 <INVOICE_DATE>10-NOV-04</INVOICE_DATE>
 <INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
 <ENT_AMT>122</ENT_AMT>
 <ACCTD_AMT>122</ACCTD_AMT>
 <VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>
<G_INVOICE_NUM>
 <SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
 <GL_DATE>28-MAY-04</GL_DATE>
 <NVL_I_DISCOUNT_AMOUNT_TAKEN_0>.41</NVL_I_DISCOUNT_AMOUNT_TAKEN_0>
 <INV_TYPE>Internal</INV_TYPE>
 <INVOICE_NUM>100000</INVOICE_NUM>
 <INVOICE_DATE>28-MAY-04</INVOICE_DATE>
 <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
 <ENT_AMT>122</ENT_AMT>
 <ACCTD_AMT>20.33</ACCTD_AMT>
 <VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>

Take a look at the INV_TYPE, we have 'Standard' and 'Internal' invoice types. How can we get totals by invoice type?
One method would be to group by the INV_TYPE, that way we could very easily get the totals, but lets assume we can not do that, our users do not want that type of grouping. The just want an invoice listing with a total fro all invoices and totals for each type. We can achieve this using an  XPATH expression.

If we loop over the invoices ie for-each:G_INVOICE_NUM to list the invoices we can then generate a total for the invoice entered (ENT_AMT) and accounted amounts (ACCTD_AMT) quite simply with:

<?sum(ENT_AMT)?> and <?sum(ACCTD_AMT)?>

To get at the totals for the invoice types and assuming that we know the different types we can use:

<?sum(ENT_AMT[../INV_TYPE='Internal'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Internal'])?> and

<?sum(ENT_AMT[../INV_TYPE='Standard'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Standard'])?>

the XPATH statement is essentially an if statement, including only those amounts that satisfy the boolean expression. Notice the ../, we need that because INV_TYPE is at the same level as the amount elements, so we effectively go up a level to the parent and then down to the INV_TYPE.

Now lets assume we do not know the number of invoice types in the data but we want to show them and their invoice totals. For this we are going to have to use some grouping. We can group the invoices by INV_TYPE and then do a sum on the invoice amount values.

Its pretty striaght forward (once you're familiar with the regrouping) we have a for-each-group on the G_INVOICE_NUM parent and group by INV_TYPE thus:

<?for-each-group:G_INVOICE_NUM;./INV_TYPE?>

Then we just create the table to hold the results, for the totals we do not need the XPATH any more :

<?sum(current-group()/ENT_AMT)?> and <?sum(current-group()/ACCTD_AMT)?>

We need the current-group() command because our amounts are inside the newly created INV_TYPE group. This approach does mean that we have to effectively loop over the data again but I think its cheaper than to create updateable variables to store types and totals as you do the main loop.

Hopefully we have now covered the totaling question with these articles, you can get the sample template and data here.

Another charting 'tidbit' today  - for those of you in the UK substitute the 't' - its too rude for American ears/eyes so we use a 'd' here ... 'phnarr, phnarr' - sorry America another UK joke, that is, unless you read one of the more boorish comics from the UK in which case, Go Finbarr!
 
I wish I could find a way to help you more to relieve the pain and misery that is the fine tuning of charts in BIP. There is a new chart dialog coming with a property palette for all the finite control you might need but thats a little ways out.
When I get questions, I resort to trawling throu the graph DTD to try and find what I need to get some niggly piece of text to behave as I need - its a slow and laborious process. 
I received a mail from Mayur from Keste, an Oracle partner yesterday asking:

I want a simple Bar chart (Single Bar) with numbers on top. See below image.


I have following code for the image on the template.

chart:

<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT">
<Title text="MBS Past 24hrs Dispatches" visible="true" horizontalAlignment="CENTER"/>
<MarkerText visible="true" markerTextPlace="MTP_TOP"/>
chart data definition ...
</Graph>

I am able to see the chart, with numbers. But numbers are with decimal points. Like 31.00, 20.00, 2.000 etc. I want to see number like 31, 20, 2, 13, 16 etc. I have tried to format using ViewFormat, but it痴 not working.

I have been playing with it on and off and getting annoyed, you may remember I documented the same thing for Pie charts a while back. Applying the same principles I started looking for the right elements to use in place of PieLabel - how about 'ColLabel' ... nope, not there, there is a ColLabels but thats no use. After a lot of frustration I came up with an idea this morning to load the DTD into XMLSpy and then convert it to a schema. XMLSpy has a nice expand and collapse feature for schemas that lets you see dependencies, etc. Within 2 mins I had the code I needed! 

So if you need to control the number format of your bar values then just add:

<MarkerText visible="true">
<Y1ViewFormat>
<ViewFormat decimalDigit="0" decimalSeparatorUsed="true" decimalDigitUsed="true"/>
</Y1ViewFormat>
</MarkerText>

under the <Graph> tag ... boo ya!

As a follow up to yesterday's mis behavin bar charts Mayur had a follow up question. The resulting chart would have looked like this:

There was a followup request to have each bar take on a different color like this:

The simplest method is to add the following attribute to the Graph element e.g.

<Graph colorMode="COLOR_BY_GROUP">

This will change the colors for each bar - there is no further control over these colors without a little effort. But there is a way to define a set of colors to be used - this could be useful if you have a set of corporate colors that you want to use on all your charts. The BIBeans engine supports what they call 'styles' this is an XML definition of the look and feel for the charts from colors to fonts. The engine ships with a series of styles that you can apply to your charts - the latest Template Builder chart dialog allows you to pick one of these.

There are 13 predefined styles but you can create your own. Its a pretty simple XML file - I found it easier to take one of the delivered files and modified it - to get at the files you need to dig into the jar files that make up the chart engine. Its  going to depend on what flavor you're running to know where the jar is - check the classpath in most cases - if you get stuck drop me a comment.

Inside the bipres.jar file you'll find a set of XML files - just open the jar file with WinZip or similar and you'll find them. You can then extract one of them and get busy ...

There is a mass of things you can change in there - I was interested in the colors, just find the following section:

<SeriesItems defaultBorderColor="#0" defaultBorderTransparent="false" borderUsingDefaults="false" 
 defaultColor="" defaultMarkerShape="MS_AUTOMATIC" defaultLineWidth="3" defaultFitlineType="FT_NONE">
<Series id="0" color="#a20346"/>
<Series id="1" color="#88575"/>
<Series id="2" color="#1008a"/>
<Series id="3" color="#cc9900"/>
<Series id="4" color="#d0146b"/>
<Series id="5" color="#694eaf"/>
<Series id="6" color="#dc5c15"/>
<Series id="7" color="#758b27"/>
<Series id="8" color="#a20346"/>
<Series id="9" color="#88575"/>
<Series id="10" color="#1008a"/>
<Series id="11" color="#cc9900"/>
...
</SeriesItems>

Those hex values map to the color palette thats available to the charting engine. This is where things get a little tedious you are going to have to get a HEX color map, maybe here and then the really hard part getting everyone to agree on the color scheme to use. This is the toughest thing in the world - been there done that, had the arguments but did not get the T shirt - we couldnt agree on the color.

Once you have the file updated you need to merge it back into the jar file - WinZip can help here again - just remember when you extract the XML to be modified you get it out using the full path info and save it with a different name - then when you merge it back in use the 'full path info' flag to get it back into the jar under oracledssgraphstyles.

Now to test it, in your XML chart definition you just need to add a new attribute to the Graph element:

stylePath="/oracle/dss/graph/styles/greeeen.xml"

So now you'll have something like:

chart:
<Graph stylePath="/oracle/dss/graph/styles/greeeen.xml" colorMode="COLOR_BY_GROUP">

You can get the template here and you'll get something like this

yes, the color differences are very subtle - but Im just that kind of guy and I like green so dont argue!

Its been a week almost full of chart tips and hints, except for the Tweeting post on Monday, where are you all? To round off the week, more charting, this is a new chart to me - they are called Spark charts, when you see one you kind of see why they are called that.

Mine are rather long sparks but you get the idea. Its really just a series of line charts with all the trimmings stripped out such as borders, grid lines, axes lines and values etc.
I got an inquiry or maybe it was a challenge from Bryan (one of the BIP bloggers - who we have not heard from recently :0) in the sales team. He cc'ed me and told a colleague that sparks might be possible with BIBeans and BIP but the man to know would be Tim Dexter - a challenge if ever I heard one.

Its not that tough to do, its a matter of turning off all the lines and text and using a line chart. Again, the Chart dialog in the Template Builder is going to let you down here - just wait for that property palette version - even I will stop pulling my hair out trying to get these things working. Here's the XML anyways:

<Graph version="3.2.0.22" autoLayout="AL_NEVER" markerTooltipType="MTT_NONE" markerDisplayed="false"
 graphicAntialiasing="true" textAntialiasing="true" graphType="LINE_VERT_ABS"
 frameSizeAutomatic="false">
<ImageSize height="80"/>
<LegendArea visible="false"/>
<O1Axis visible="false"/>
<O1TickLabel visible="false"/>
<PlotArea borderTransparent="true" fillTransparent="true">
<Rect height="24000" width="31842" x="-15889" y="11807"/>
</PlotArea>
<PieFrame>
<Rect height="22400" width="20800" x="-11381" y="12339"/>
</PieFrame>
<SeriesItems>
<Series id="0" color="#66"/>
</SeriesItems>
<Y1Axis visible="false"/>
<Y1MajorTick visible="false"/>
<Y1TickLabel visible="false"/>
<Y2MajorTick visible="false"/>
</Graph>

notice the 'visible' attribute being set to false for the axes related stuff and the plot area border set to transparent - thats it really. The only other element of note is the 'Rect' in the PlotArea - this allows the chart to fill the whole area in the dummy image in the template. Its fiddly but resonably quick to set.

The other cool bit is to get the charts repeating in the table for each range - thats as simple as putting the chart inside a repeating group in a table just like a field.

You can get the template, data and output here. Next week ... more charts, nah Im kidding even I have had enough for a while!

I exchanged several mails with Scott from General Dynamics regarding FSG outputs with BI Publisher. He was looking at using the Financial Report Template Editor (in Excel)solution from the Report Manager team for FSG reports and had some questions. He had an issue with loading an updated Excel template into the template manager via their UI - theres a bug open for this that he is now hopefully attached to. The discussion opened up a little into the capabilities within the Excel template solution - I provided some insight which I think is worth sharing with a wider audience.

As some of you know there is a true Excel template solution in the works - its not quite generally available - we are working through some kinks and requirements with a customer at the moment and once they are sorted it will hit metalink without a password requirement. More on those tomorrow. To clear things up on the Report Manager Excel template solution - this is not a binary Excel solution.

You use an Excel interface to build the layout for the FSG data - under the covers an XSLFO template rather than an Excel file is created to format that output. At runtime the XSLFO is applied to the FSG data and the user can pick the output format they want e.g. Excel, PDF, etc. The fact to know here, is that the Excel we generate is not actually Excel - its HTML or more properly XHTML. We then set the mime type such that Excel is used to open the file. There are obvious limitations - no native Excel functions are going to work so you can use a formula function on your data or embed a macro in the template - we do not interpret VBA macro language into XSL code ... sorry. The advantage, is that you get a familiar Excel interface to build the templates in and you get multiple output choices from the template as well as the Excel output. Under the Report Manager module you also get drilldown on those FSG numbers into the GL balance inquiry pages.

So why not just use an RTF template? Why not indeed - you can build everything you can in the Excel interface the only missing piece right now would be the drill links but they are coming soon. Scott did ask about the page width limitations? Those of you that know FSGs will know that the FSG engine can generate reports with almost as many columns as you wish. How do you print that out without getting data truncated on the right hand edge of the paper? The FSG team ship you a template out of the box that wraps the data to a new page when it hits the edge of the 'paper' no matter how many columns of data you have. This was designed with the idea in mind that you wanted to print out the FSG where the page size is determined by the paper you feed into the printer. But, if you just want to get the content out to Excel then that paper size can be adjusted.

Think about it, when you first open a new Word doc it makes an assumption that you want 'Letter' or 'A4' sized paper depending on where you are in the world. There is nothing to stop you going in and setting the page width and height to their maximum 22" x 22" - now as long as your output is destinined for Excel or HTML where there is no concept of page size (until you want to print) then you can use this custom width and height and avoid the wrapping that we do for you. With the generic FSG template just extend the table width to the width of the page and you're done.

Your users who want to work in Excel will be happier - they will have a 30 column report laid out in Excel (without wrapping) and they can beaver away using familiar native Excel features to dot the i's and cross the t's. If they want to print their modified report - Excel will handle it with its 'Z' or 'N' print format. Once they are happy with the numbers in the original report they can just apply a new template that expects to be printed ie its Letter or A4 sized and you get all the high fidelity reporting and wrapping from a regular sized RTF template.

Tomorrow, I'll tempt you with some features that are coming with the new binary Excel templates ...

Good question from Remc0 on the forum recently.

When the following in a group:
<?sum(current-group()/_saw_8_[.!=鋳])?>

It returns a 0 when all values are null. But 0 means smaller then 0,5 and null means it is not there. Now my question is: how can I sum the values and make a different between 0 and null?

Its a good question and there is a reply to use the updateable variables - I have to admit an aversion to updateable variables - they are expensive and are not a feature of native XSL - there are other ways around using the 'variables'

Remco's basic problem is that the code above that checks and eliminates null values in a sum function returns a zero value when all values are null. For example with the following XML.

<AMOUNTS>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="CM"></AMOUNT>
<AMOUNT type="CM">100</AMOUNT>
</AMOUNTS>

The sum of the amounts of the type 'CM' using the XPATH null elimination:

<?sum(AMOUNT[@type='CM' and .!=""])?>

as an aside, notice we're combining XPATH expressions with an 'and' - we want to sum all AMOUNT values where the type attribute is CM and the values are not 'null'. In this case we get '100' returned - thats good. If we sum the INV types:

<?sum(AMOUNT[@type='INV' and .!=""])?>

we get a zero returned - which as Remco notes is not actually correct - well not for his requirement, he wants to see an empty string.

My approach was to use our inline 'if' statement.

<?xdoxslt:ifelse(sum(AMOUNT[@type='CM' and .!=""]) = 0,'',sum(AMOUNT[@type='CM' and .!=""]))?>

The if statement tests the returned value and if its a zero ie the XML values are null then an empty string (two single quotes) is inserted otherwise it returns the calculated amount. This is fine if all the values are null but if the returned sum value happens to be '0' then an empty string will be returned.

In the interest of audience participation, here's a challenge. Can anyone come up with the XPATH necessary to return the real zero values and handle the null case too?

Spotted a nice question on the forum today, its nice cos its a good question and even better it has a short but sweet answer. Michaël  has the following data:

<root>
<name>Mike</name>
<name>Max</name>
<name>Nick</name>
</root>

Hi Mike, Max, Nick how's your day?

Not quite so obvious but it can be done. We can use the @inlines command to force the template to lay the text out horizontally across the page rather than down the page.

Hi <?for-each@inlines:name?><?.?> <?end for-each?> how's your day?

this gets us

Hi Mike Max Nick how's your day? - the italics are just for emphasis the code wont magically change your output

Now we could get all cool and funky and introduce logic to add commas as appropriate to get our English past my old Language Arts teacher. But, 1. Im not at school any more and 2. the answer wont be short and sweet anymore. 

As an extension to the article I wrote earlier on getting grouping to work across the page rather than down the page. I received a question from Vidhya at Smart Dog Services - an Oracle partner. Here goes:

Its another get the grouping across the page not down it. But there is a little wrinkle in here I think. Vidhya sent only a couple of columns of data, I assume that can be dynamic. So we need to handle the possibility of more columns coming in at runtime. We can use the @column and @cell functions to do this - its pretty well documented but you need to play around with it to understand what's going on.

We need a two celled table that looks like this:

The column is going to get repeated for each dynamic column coming into from the data, so set the width as needed. To explain the contents of the fields:

FE Col Desc - <?for-each@column:G_DESCRIPTION?> - this is going to create a new column in the table for every member of the G_DESCRIPTION group that there is
Description - you should be able to work that one out
EFE - closing out the G_DESCRIPTION group

FE Cell Comm - <?for-each@cell:G_COMMISSION?> - creates a cell for every member of the G_COMMISSION group present
Commission - <?COMMISSION?>
EFE - closing the G_COMMISSION group

This gets us the output Vidhya is looking for with the column titles coming from the DESCRIPTION and the values from the COMMISSION element. As more columns come in, the table will fill across the page, once it reaches the edge it will break to a new page. If you have a similar requirement but want to repeat a fixed set of row headers you can use the horizontal-break-table: command.

If you want the columns widths to adjust as columns come into the document then the dynamic columns section in the user guide is going to help you out there.

There is a good and apparently simple question on the forum today from mysterious user613190 - dont be a number, be a free person to mangle a phrase. It was actually posted yesterday but a man has to rest a little.

Hi,

In my output I am getting page numbers like

1 of 3 (when output has 3 pages)
2 of 3
3 of 3

But if output has only one page then I need to show like below

1

Could you help me in this?

Its a simple question, how do I conditionalize (is that a new word I just made up?) the page numbers? Seems straightforward to the uninitiated XSLFO template creator.
'Why cant I count the number of pages in the template and then render one string or another?'
'Well, you can't !'
'Why Not?'
'Because I said so !'
Hang on, hang on, this is sounding like a conversation between my son and I; except it would be about why he can not go to the movies, or hang out at the skate park or maybe ride his dirtbike - it's a power thang and I get irrational sometimes OK!

Getting back to a rational answer ... you still can't and its not because I said so, its because the XSL standards say so. Well that and the fact that the rendering engine at runtime decides how much data/information can fit on a page. To understand the process a little more, when we apply a template against some data, the flow (for the case of PDF) and assume your RTF template has been converted to XSLFO is as follows.

XML + XSLFO  -------> FO ------> PDF
__________             ____
          |                        |
 XSLT Engine          FO Renderer

That FO stage is the Formatting Object - its a big piece of XML that looks similar to extremely detailed HTML with pixel perfect placement of objects on the resulting page. To understand why you can not place 'page' logic in the template you need to know the processes going on. To get to the formatting object its an XSL transformation - at this stage all thats happening is the format and placement of data is applied to the XML data but there is no notion of a 'page' or how much data will fit on that page. The second stage, taking the FO to the PDF output is carried out by the FO renderer - at this point the engine will start to lay the data out on pages, calculating what will fit and then rendering it.

So, you see that any logic around pages in the template is not going to work, its lost and can not be executed in the FO layer, by then all calculations and conditional formatting will have been executed and because there is no notion of what will fit on a page or how many pages there are going to be - by then its a matter of fitting the data onto the page.

What are the options then I hear you ask? Well, we already have some extensions to the standard we have put in to our engine around page rendering. Those of you using the dynamic column solution to get a Z printing format on your PDF output are unwittingly using one of those extensions. We are therefore looking into building a page calculation extension to allow such functionality as is being asked for above - it's coming, not in the next release but some time after that.
What about now, I need a solution! - well one option to is to control the number of lines you want to show per page. If you have that in place then you can comfortably calculate in your template whether you are going to get 1 page or 100 pages of output and then act accordingly. I have covered fixed number of lines per page before and that article should get you on your way. If folks out there have found others, please feel free to share.

We had a customer issue a few weeks back ... 'really Tim, I never would have guessed' :0) Yeah, we get some issues sometimes. This was a JD Edwards customer that had developed an encoding class for a barcode. Lost? Check out the Advanced Barcode Support article. Basically, some barcodes need the data encoded, calculating stop bits and such like, prior to applying the barcode font. To support this, we allow you to create a java class that implements a Publisher interface to encode the data values. This can then be called from the RTF template layer using commands - check the user guide people!

The encoding class needs to be in the classpath on the server be it JDE, EBS or standalone - this is straightforward enough but how about being able to test on the Template Builder for Word interface?

It can be done, no support directly in the tool but with a little jiggery pokery we can get the class tested. The list of jar files that the builder will load at runtime is limited but the main one we can highjack is the xdocore.jar. You'll find it under the jlib directory under the Template Builder install directory.

You can open the jar file using something like WinZip, with it you can see the structure of the contents,

The class you developed will no doubt have a package declaration in it e.g. oracle.apps.xx.xmlp.barcode - this needs to map to the path in the jar file. To get it into the jar in the right place just get the class into the directory path on your local drive.

Then, in the case of Winzip, open the jar, then drag and drop the class and ensure the 'Use Full Path Info' option is set to get the class into the correct path that will map to your package specification.

Now, you can test to your hearts content in the comfort and relative speed of MSWord.

Thats how many templates I have found in our R12 environment this week. Its been Fusion crazy in the office all week and I have somehow tasked myself with charaterizing all the concurrent programs, parameters, layout templates and data templates, who is using what API, the list goes on and on.

Getting some early results:

The XSL-TEXT, XSL-HTML and XSL-XML templates surprised me and piqued my interest, In the XSL-XML there are some straightforward conversions from XML to XML but the contracts team get the prize for most interesting. They have functionality allowing users to update the contract terms inMSWord 2003. They then get the Word ML (XML) from the result and use us to convert to a more friendly XML format to report from ... cool huh?

The three XSL-TEXT templates are generating flat file outputs for transmission to partners. The two XSL-HTML templates are interesting, they both accept HTML as in puts and return the appropriate XSLFO code to embed in your outputs - Ive written about doing that in other articles.

As I dig up more numbers I'll share them ... next up, those concurrent programs that we maintain, all 9631 of them - that can not be right, please dont let it be right. 

I have been scouring blogs over the last few daze and came across a post from Gareth that I missed in my last recce. Gareth is a regular contributor to the XMLP/BIP blog collective, focusing on EBS integration among other subjects. He comes up with some nuggets of gold that I hope he does not mind me sharing with folks - more pearls of BIP wisdom from the real Middle Earth a.k.a New Zealand here.
Those of you that had been struggling with getting XMLP based formatting for check printing are now in better shape with an AP patch for 11i 4303528 - I wrote about that here. This time, Gareth has come up with a neat approach to another thorny problem in EBS - AR Statements.

Getting XMLP templates applied to 'Print Statements' in AR is painful at best - its a combination of spawned 'C' and Oracle Reports. For those of you still struggling, nothing official yet, but Gareth has a solution that is not too invasive or custom.

Check it out and get it implemented here http://garethroberts.blogspot.com/2008/01/beautiful-statements-in-1-easy-step.html

Thanks Gareth!

This one is a bit 'out' there, unless you are one of our Vietnamese users. In which case, welcome and this is for you. If I have not lost the rest of you already, read on it's a good story and a good lesson to be learnt today ...

Dorothy Teoh, from the Singapore Support team, dropped a question to our internal mailing list recently asking:

Hi Gurus,

Does anyone experienced any MLS issue after upgrade to jdk 6 ?  Panasonic logged a severity 1 SR for this issue : 

  THOUSAN SEPARATOR NOT WORKING FOR VIETNAMESE LAN GENERATED WRONG CURRENCY FORMAT

Customer is using e-Business Suite 11.5.10.2 and recently experinced f60webmx spinning 100% CPU after upgraded to Linux Redhat 4 updates 6.  As a result, support suggested customer to upgrade to latest JDK which is jdk 6.  Customer experienced xml report for Vietnamese default language printed thousand character as '.' instead of comma.  Customer is in Developer 6i patchset 18.  Patches 5488542, 6195758 and 5884875 applied but could not solve the issue.

May I check with wide audience if anyone hit the same issue ?  Could this be new bug in BI publisher ?

We were flattered at being called 'Gurus' but dismayed that, shock, horror, there might be a bug in our product and a new one at that!

Now, it had me beat without a lot of leg work - so I held out for a day or two to see if someone else jumped in. Our internal mailing list probably generates close to 50 mails a day that do not require a one line answer. So we tend to play chicken in the dev team - who is going to hold out longest before answering. But we were saved, Ryoji Suzuki from the Internationalization (i18n) team stepped in with a bomb shell shocker!

This is the expected behavior because:

- Vietnamese has been supported since Java5.
- BI Publisher gets the default decimal and thousand separators from Java.
- Before Java5, the decimal and thousand separator for Vietnamese was:
     Decimal: . (dot)
     Thousand: , (comma)
     Example: 123,456.78
- After Java5 where Vietnamese has been supported, the decimal and thousand separator
   for Vietnamese are:
     Decimal: , (comma)
     Thousand: . (dot)
     Example: 123.456,78

So what you are seeing in Vietnamese report should be expected behavior, and what you saw before upgrading java6 could be considered as a bug.

Regarding this default values (decimal is comma, and thousand is dot), if we see MS Windows regional options (Control Panel -> Regional and Language Options, and choose "Vietnamese") , MS also defines the decimal and thousand separators for Vietnamese as "," (comma) and "." (dot) respectively. So I think the default values should generally work for Vietnamese users.

Would you please double check if the default decimal and thousand separators (decimal is comma, and thousand is dot) are acceptable for the Vietnamese users. I hope it is acceptable.

But if they don't like these default separators, we should file an ER. If you generate the report through concurrent program. there are already two ERs filed. You may want to track them.

For Concurrent manager: Bug#4250828
For OA Framework Region: Bug#5407152

Thanks!
-Ryoji 
 

1. The i18n team are monitoring our mailing list
2. Ryoji does the investigation and finds that its not a bug at least not for BIP. Sun might not be so happy about it thou!

Awesome result, a case of finding the right person looking at the right email at the right time - thanks Suzuki-san!

Another bad bad play on words that only my British and perhaps Indian brethren will see the link for. We used to, when I was a kid, make 'trunk' calls on our phones - today it would a be a 'long distance' call - someone has explained better here there is some interesting history behind it. Use it at your next dinner party to impress your friends ... maybe not!

To the more useful part of the post ... another question from our mailing list and another great answer from Kan who works in Oracle Consulting.

Hi, wanted to check if we can use the same TRUNC function avaliable in oracle db as shown below in BI Publisher (v10.1.3.3.2)

 select trunc(1234.123,0) from dual --> 123 
 select trunc(1234.123,2) from dual --> 123.12

please let me know if it's possible or if there is an alternative. 

You immediately maybe think, thats easy just use the ROUND function but its different ...

TRUNC(1234.125,2) --> 1234.12
ROUND(1234.125,2) --> 1234.13

As Kan says in his reply, to get this to work in XSL speak ...

You can use a combination of substr/instr to do this.

1. select trunc(1234.123,0) from dual --> 123       
                <?xdofx:substr(?123456.123?, 1, (instr('123456.123', '.', 1)+0))?> 

2. select trunc(1234.123,2) from dual --> 123.12    
                <?xdofx:substr(?123456.123?, 1, (instr('123456.123', '.', 1)+2))?> 

Not very friendly I know but doable - we need to build a new extension for this - its coming soon!

Not so much a how to today - that comes tomorrow; more along the lines of comment. Im charting again but looking at some interesting data. The data in question is the US Mortgage rate - I was chatting to Leslie our documentation guru last week. She mentioned that she had been shopping around for a refi on her house. One vendor told here not to bother right now and to wait until September. Pourquoi? Asks Leslie - 'because the rate always falls in September in an election year!'

Huh? So the mighty Fed that sets interests rates and acts, I thought, independently of government is swayed by 'election years' and lowers the cost of borrowing just before the election and affects the worlds economy to boot. Who benefits? other than the consumer that is. Maybe the fastest candiate off the blocks can claim that they helped sway the Fed in their decision. I couldn't see it.

After a bit more discussion it was thought that maybe it only falls when an incumbent is running for a second term. Maybe George had a quick word with Greenspan in 2004, 'lower the rate there feller, just for old times sake?' Reminds me of our yo-yoing gas (petrol) prices here where Im damn sure the government steps in when OPEC gets a little too greedy.

Being the consumate skeptic I was out googling, looking for historical rate numbers. I alighted upon the USTreasury web site which posts such numbers and they have rates back to 1990 in an XML format to boot; even better, the XML has been generated by an old friend - Oracle Reports.

Time to get busy with a template and check out this claim. Here's the full 17 years worth:

Its a busy chart with more than 4500 data points - I'll address that tomorrow.

Looking in at Nov 2004 and Nov 1996 for messers Bush and Clinton

Bush Incumbent

Clinton Incumbent

I have included some data leading up to the election date but I can not see a change either way? Its not a scientific study but maybe the incumbent does not have the influence afterall ... phew! The only thinkg I can see is a steady rise continuing after Bush got back in, there may even be a jump ... but Im an impartial observer and quite obviously know nothing! 

Tomorrow the crux of why I was looking at this data using our charting engine - how can we smooth it out a bit for users?

Sometime in the dim and distant past I wrote about mortgage rates and generating charts for masses of data. It was not actually that long ago last Monday in fact but the intervening 7 days have seemed like months. The hacking cough, aching bones, 'flesh creep' and feeling tired all the time - never felt so bad in my life. I now understand all the fuss about getting a flu shot in the fall, although it appears it would not have done me any good this year. I got Type A which flufacts.com states.

Influenza Type A is the most common and also the scariest of the three influenzas, causing the most serious epidemics in history.

Now, I was not scared but bloody hell it was rough!

Getting back to charts, the mortgage rate chart image I posted looked like this:

In the blown up inset you can see that the line is actually made up of multiple rectangles - thats the SVG output we get from the BIBeans chart engine. Its fine for charts with none too many data points but this chart has around 4500 points which leads to a very 'choppy' chart that does not look good nor prints too well either.

I came up with a way to allow you to smooth the chart. Now, when shared with the rest of the development team I got flamed by a couple of them - maybe flamed is too strong a word - let's say lightly toasted!

My approach was to allow the developer/user to specify a smoothing value for the chart, let's call it 'x'. In the chart definition I then used this value to skip every 'x' values in the data set. So for 4500 data points with a smoothing value of 10 I ought to get 450 points - you can see why I got 'toasted'. A better way would be to calculate an average across those 10 skipped values and use that. Thats a fair point but on a chart with 4500 data points and you want it smoothed so you users can get a feel for the data do you want complete accuracy or a visualization so that they can see the 'rough' view of the data?
If the former, then get the extraction portion of the report to do the calculation for you - thats a lot of heavy lifting in the template to calculate that - you could build an extension function to do it I guess - maybe look into that another time. If you want the latter, read on ...

Once the smoothing is implemented you can get something like this:

I admit, there is some data lost in the smoothing but its a better looking chart, IMHO, from a users point of view. Its going to depend on the smoothing value you assign to the data. If you compare the two you can see some of the smaller peaks and troughs are lost. Reducing the smoothing value brings them back but things get choppy again so its a balancing act. Playing with it a value of 20 is about right in this case.  I think even an average value based chart would still lose some of the finer detail. If you're still with me, how did I do it?

First I created a smoothing parameter for the template

<?param@begin:chartLimit;'20'?>

Its got a default value but this can be overriden at runtime.

Now we have to get our hands dirty and get into the chart code. I used the chart dialog to start out and then got stuck in:

chart:
<Graph graphType="LINE_VERT_ABS" >
 <LocalGridData  
       colCount="{count(.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0])}"
       rowCount="1">
 <RowLabels>
 <Label>
 BC_6MONTH
 </Label>
 </RowLabels>
 <ColLabels>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
 <Label>
 <xsl:value-of select="BID_CURVE_DATE" />
 </Label>
 </xsl:for-each>
 </ColLabels>
 <DataValues>
 <RowData>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]">
 <Cell>
 <xsl:value-of select="LIST_G_BC_30YEAR/G_BC_30YEAR/BC_6MONTH" />
 </Cell>
 </xsl:for-each>
 </RowData>
 </DataValues>
 </LocalGridData>
</Graph>

the parts to take note of are, surprisingly the parts in red, duh! They all share the same common calculation:

.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]

This is essentially only allowing values through where the current record position, when divided by the chartLimit value = 0. So for a chartLimit value of 10 we would get the following points: 10, 20, 30 and so on. The first instance calculates the number of points to be plotted and is assigned to the colCount attribute - the charting engine needs this so dont ignore it. The second is for the chart labels - optional. Lastly the actual data point to be plotted. Simple eh?

At runtime, depending on your flavor the chartLimit parameter can be passed to the template to 'smooth' the chart to the users preference. I have zipped up a standalone report definition here. Those of you on an Apps flavor can use the template and sample XML to test it thru your application. 

An oft used term from my football (Americans read 'soccer') coach back when I was knee high to a grasshopper and we were doing 'header' practice. You dont need those brain cells just launch yourself at a football and get it in the net (goal) - there was a knack to not getting a blinding headache - my method, just avoid heading the ball altogether, just chest it down and volley it into the goal - I was that good, honest! OK, may be not that good and what the hell am I talking about I hear you ask?

Headers ... and footers for that matter in your templates! I was on a call yesterday with a JDE customer that has been implementing BIP and has been struggling with the headers in their templates. We have a command available in the templates, start|end body its a way to get headers and footers into your template without the need for the Word versions. The customer was using them and experiencing problems, now the command has its place but I have never needed it in all the templates I have built and I have built a few. The comment came up that its tough to work with Word headers and footers - this stemmed from the fact that Word does not let you put their Formfields in the header or footer areas. There are ways and means to get around this that the customer was not aware of ... I thought I'd share a couple of those with you readers out there.

So, no formfields allowed in the header or footer but you can put the clear text command in those regions. By that I mean the contents of the formfield e.g. <?CUSTOMER_NAME?>. I can therefore put as much as I want in the header/footer thus:

All well and good but not good looking and maybe a little intimidating to the business user who asked of IT,
   'Hey, I want to have the currency code column to appear after the invoice amount in that table'
to which the IT dept replied,
   'You got MSWord on your desktop?'
   'Yep'
   'Heres the template, get on with it!'

OK, maybe a little blunt but you get the idea. On a side note we are seeing the 'business' getting more and more involved in building layouts. Instead of it being a pure IT nightmare trying to keep everyone happy the business has the tools (MSWord) to make the changes they want. We now have a customer that has gone the whole way and the business is completely responsible for building, mainitaing and enhancing templates for their users - making for one very happy IT department!
Getting back on track, there is an alternative to the raw commands in the header you can create, a little confusingly a 'template' in your template - think of it as a function that will render output for you when called.

Notice the layout is created in the body of the main template and wrapped in <?template:XXXX?> commands - we then call that from the MSWord header of the template - simple stuff. Samples files here.

Now, you can get more sophisticated and completely externalize the header template into a separate RTF template and use it as a 'sub-template' - now you can share the header across multiple templates by just calling it. You can even pass parameters into the 'header' template, say the 'report title' - I have talked about that a little in this blogs second ever post back in 2006

Quick one today, Im looking for feedback/information - I have written about check printing here on numerous occasions, the one piece we as a product struggled with was signatures and their security. I wrote about some of the 'soft' alternatives to securing the signatures in a previous post. It has always niggled me thou, that we could not help those folks that had invested in a 'hard' security solution.
By that I mean that their check printer has a removeable memory module that contains the signature images. To get them out on to the page the printer is passed the appropriate escape sequences in the document and low and behold the signatures appear as long as a the memory 'stick' is there.

The key to supporting this 'hard' solution is that these printers use the PCL file format for printing - as many of you know, we dont natively support PCL, just PDF and PS, that is until now! We are working feverishly on our own PCL output (at last) and we are not far away from completing it. Once we have it we then have complete control over the PCL generation that is sent to the printer. Yeah, so what?

Well this means that we can allow you to embed you signature escape sequences into your RTF templates that will be preserved all the way through to the printer and that means you can wrap conditional code around them too and that means we will have a complete 'hard' signature solution for check printing. A dream come true ... OK, maybe not a dream but a nightmare I and a some customers I have been working with, can wake up from soon.

So, its coming but I need some help. Ideally we need a check printer that we can test with - its a long shot but if anyone in the Bay Area can let us borrow their check printer and a couple of hundred pages of fresh check stock, along with their routing and account numbers well be in touch soon ... from Tahiti :0) Seriously thou, if we can get a printer that would be fabulous.

Failing that, some details around what the escape sequences are that you need to pass to your printers would help. Without a check printer to test with, we are working along the lines that if we can put an escape sequence in a template that contains say 'Hello World' and have it come out at the printer. Then we can extrapolate that you'll be able to embed your escape sequences into your RTF and have your signatures come out the other end - a safe assumption I think but we need more information. If you have the info and would may be like to become a Publisher Guinea Pig testing the solution and helping us out, drop me a mail.

Ok, so I said it was a quick one today, I lied, those that know me know, that when a 'yes' or 'no' answer will do I cant just say yes or no. Im the opposite of a bolshy teenager who manages to answer the most open question, inviting a diatribe of verbose verbiage, with a one word answer. See there I go again ... Im shutting up now, mail me!

Arrived in California today to a beautiful warm and sunny day, great to see the team. With the morning's meetings out of the way it was off to the 600 cafe to get a much missed veggie burrito - its not so much the burrito (although it was as good as ever) it was the fantastic lady who serve's you. Sadly, I have forgotten her name, but she looks so gruff and barks questions at you, what kind of tortilla do you want, beans? black or refried? jalapanos? salsa - hot? Its a fantastic tongue lashing setting you up to savor the flavor of the burrito even more ... forget Qdoba get over to the Oracle cafe in the bottom of 600!

So to todays rather strangely titled entry - hard core sorting. I say hard core purely because of the requirement. Here's the XML:

<REP> 
 <G_1> 
  <NAME_1>Peter</NAME_1> 
  <SORT_1>6</SORT_1> 
 </G_1> 
 <G_1> 
  <NAME_1>Anna</NAME_1> 
  <SORT_1>3</SORT_1> 
  <G_2> 
   <NAME_2>Paul</NAME_2> 
   <SORT_2>1</SORT_2> 
  </G_2> 
  <G_2> 
   <NAME_2>Mary</NAME_2> 
   <SORT_2>11</SORT_2> 
  </G_2> 
 </G_1> 
 <G_1> 
  <NAME_1>John</NAME_1> 
  <SORT_1>2</SORT_1> 
 </G_1> 
 <G_3> 
  <NAME_3>Jim</NAME_3> 
  <SORT_3>4</SORT_3> 
 </G_3> 
 <G_3> 
  <NAME_3>Frank</NAME_3> 
  <SORT_3>12</SORT_3> 
 </G_3> 
</REP> 

Looks innocuous enough until you see the requirement on how it should render ...

Paul 1
John 2
Anna 3
Jim 4
Peter 6
Mary 11
Frank 12

Hmmmm ... well Klaus and Hok Min rose to the challenge and came up with the following:

<?for-each://*[starts-with(name(.),'G_')]?> 
 <?sort:./*[starts-with(name(.),'SORT_')];'descending';data-type='number'?> 
 <?./*[starts-with(name(.),'NAME_')]?>, <?./*[starts-with(name(.),'SORT_')]?> 
<?end for-each?>

Why am I sharing this ? Couple of reasons:

1. Its a neat solution to a tough problem - that, I would have pushed back on before answering. Why not get the extraction routine to do all the heavy lifting. I've said many a time, sorting in the template comes at a price. This type of sorting comes at a bigger price, you are sorting on a partial string comparison ... not good! If you can get the query to do the work, get it to do the work!

2. If we ignore the sorting and focus on the 'for-each'. You can see that you are not looping over a given element in the XML but you are looping over a partial string match on the element name. Tres Cool!

  starts-with(ELEMENT, string ) is an XSL function
  name(.) - another XSL function that returns the element name itself, not its contents.

So we are matching on all element names that start with 'G_' - this enables us to treat the nested 'G_2's as if they were at the same level as the G_1 and G_3 elements.
Again, I would question the XML structure and ask why its so badly formed, but I suspect we did not get to see all of it.

I'll try and expand on this theme of not just 'element looping' in more articles, you can create some damn fine reports with an Oracle burrito inside you!

We have been working on a new project, not development but hopefully just as useful. We are putting together a set of new viewlets that will highlight and hopefully explain how to use/create template features with examples. This will be across all of our template types ... eventually. We have a big list and are working as fast as possible to get them out. We'll post them as soon as we have a reasonable amount of them ready.

One of the viewlets Jen in our Ohio office has been putting together on adding checkboxes to your output in an RTF template - adding the checkboxes is not that tough but getting them in your output can be a little tricky. We need to use the xdo.cfg file to tell the engine where the check box font will be and then which glyphs to use - I covered that here and Jen's viewlet will make things crystal clear of course.

In the course of putting the viewlet together Jen got to use the xdo.cfg file with the Template Builder for MSWord - it struck me today that I do not think we have documented or blogged where this file is?

If you check your Template Builder install you'll find the xdo.cfg file under

... Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config

You'll actually find the xdo example.cfg file that we ship. If you change the name we will start using it. Inside you can put your font references and properties, you'll find those listed in the user docs.

There's another file thats really useful for us and especially our support team, thats the xdodebug.cfg. If you are having problems and want some debug information or you have logged an SR support are going to want that debug info - get it up front for them and you'll be golden or at least happier because support will be able to help you much faster.

If you are testing on the template builder then you need to create the debug file as follows:

a.  Create a file called xdodebug.cfg

b.  The contents of the file should be:

LogLevel=STATEMENT
 
LogDir=c:temp 

this should always be set to STATEMENT to maximize the information provided in the log.

LogDir specifies where the debug files should be written to, this needs to be a writable directory for Publisher.

c.   This file should then be saved to the $JRE_TOP/lib directory - in this case JRE_TOP is going to be the 'jre' directory you are referencing in the 'Java Home' field on the Preview tab under the Tools > Options. OF course the same file can be placed on the serverside too, again, its going to sit under the JRE_TOP/lib directory. Once you have it in place run your template/report again.

You will get a bunch of files in the directory, the RTF template, the XML, the converted XSLFO template, the FO file, a log file and the final output - if things got that far. Support will want all of it. Just remember to remove the debug file when you are done - leave it and it will fill your disk!

Some of you may have seen the 'shape' support we have with our RTF templates in the documentation, some of you may have played about a bit with it and thought, 'OK, so what can I do with them from a business perspective?' Some of you may have found uses for them, if you have, please share with it the class!
Since they were introduced as a bit of a side project from Edward (back then 'Mr RTF Template') I have always struggled a little when talking about the shape support, yeah, its cool but what can I use it for. I have written about using them to build a gantt chart and we have seen a few other applications for them - overall they fall into the 'well I cant build what I want in your charting engine so I'll have a stab at shapes!'

This past week Mike, our Product Manager dropped a good one into our respective laps. Do we have the ability to generate this:

I took out the performance measure titles but you get the idea.

There is quite a lot going on here, it was funny to see it, I had just seen something very similar as a requirement from another customer. They had actually abandoned they're visualization because it was too confusing. This one seemed much easier to read although technically a little more complex.

I did not think our current charting engine was up to it, maybe some combination of a couple of charts overlaying each other would get it done but it gets messy very quickly doing that. Shapes was an alternative that might just work, all I would need would be some background table, then a couple of yellow triangles, a black one, a circle and a horizontal line - easy!

Did not have any data to work with so came up with some and made some assumptions:

<PERFORMANCE>
<NAME>c.Makes the coffee really well</NAME>
<VALUE_SELF>3</VALUE_SELF>
<EVAL_START>2</EVAL_START>
<EVAL_END>3</EVAL_END>
<COG>2.7</COG>
</PERFORMANCE>

Looking at the graphic you can hopfully work out how the data maps to an 'icon'. 'COG' is the 'Center of Gravity', an average of the evaluators scores for a given metric. The horizontal line joins the start and end of the evaluators range.

The RTF template for the chart is pretty simple really.

The for-each field is looping over the PERFORMANCE loop, the shapes are sitting atop each other with the following code:

Straightforward shape stuff, the shape-offset-x moves the shape along the x (horizontal) axis and the shape-size-x (for the line) extends it. Whats the 104.7 for then? Thats the fun part of shapes, in this case its the multiplier to move the object 'x' pixels - I needed to get the shape to sit on the appropriate vertical line and after a litte trial and error I got to 104.7 pts - its not quite there but a little more tweaking will get it spot on.

Shapes are a little funky, they float around on top of the rest of the layout, so our RTF engine has to find them and pin point their position - its not always spot on so you need to do a little 'noodling', a technical term, to get them to the right position. Thank heavens for the template builder to let me 'noodle - test', 'noodle-test', etc.

Here's the final output in PDF

Its close, I missed the last two columns out, thats just simple conditional formatting - I wanted to concentrate on the shape manipulation functionality.
If you get a tough visualization from your users and think how the heck can I do that? Get on and shape up!

OK, I lied, Im squeezing one more in before the migration ...

Got a couple of bugs today asking about how to add and change chart titles. Its not tough, but for now its a code update. Once you have the base chart in your template, double click the image to get the Word dialog up. Got to the Web tab to see the XML for the chart

Heres the code for various titles

<Graph version="3.2.0.22">

 X Axis Title

<O1Title horizontalAlignment="LEFT" text="My X Axis" visible="true">
<GraphFont name="Times New Roman" size="12" bold="false" italic="true"/>
</O1Title>

Chart Title

<Title horizontalAlignment="LEFT" text="My chart" visible="true">
<GraphFont name="Times New Roman" size="12" italic="true"/>
</Title>

Y Axis Title

<Y1Title horizontalAlignment="LEFT" text="My Y axis" visible="true">
<GraphFont name="Times New Roman" size="12" bold="false" italic="true" textRotation="TR_HORIZ_ROTATE_270"/>
</Y1Title>
</Graph>

Things are going to get better I promise, the next release of the template builder for Word 10.1.3.4 will expose these properties thru a dialog.

OK, thats it for a couple of weeks!

Welcome back .... new look, same old (hopefully) useful articles - will talk about the new look another day. We're back with a bang, at least for those of you struggling with the Purchase Order Oracle Report. Dave summed up the problem on the BIP forum last week:

I have the following XML:

<PO_DATA>
 <LINES>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 1</LINE_DESCRIPTION>
   <PO_LINE_ID>2467</PO_LINE_ID>
  </LINES_ROW>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 2</LINE_DESCRIPTION>
   <PO_LINE_ID>2468</PO_LINE_ID>
  </LINES_ROW>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 3</LINE_DESCRIPTION>
   <PO_LINE_ID>2469</PO_LINE_ID>
  </LINES_ROW>
 </LINES>
 <LINE_ATTACHMENTS>
  <TEXT>Detailed item description</TEXT>
  <ID>2467</ID>
  <TEXT>Detailed item description2</TEXT>
  <ID>2468</ID>
  <TEXT>Detailed item description3</TEXT>
  <ID>2469</ID>
 </LINE_ATTACHMENTS>
</PO_DATA>

Is this possible?

Notice the line attachment section? We have a link back to the LINE_ROW section but no nice structure to loop over!

We have to get into some more serious XSL/XPATH expressions to get the description to appear with the rest of the line information.

With some help from a relatively new member of the BIP dev team, Yaoguang, I managed to come up with a solution.

Where you are looping over the LINES_ROW, just inside the for-each field add a new field:

<?variable@incontext:PoLnID;'PO_LINE_ID'?>

Every time the loop goes over it assigns the current PO_LINE_ID to a local variable 'PoLnID'

Where ever you want the description to appear on the row create a field that contains

<?/PO_DATA/LINE_ATTACHMENTS/ID[child::text()=$PoLnID]/preceding-sibling::TEXT[1]?>

Its a bit of a mouthful but its basically looking for a child element that matches the PoLnID value - then its looking for the first [1] occurrence of the TEXT value that is a sibling to that ID.

Works real nice!

Of course it would be better to get the PO team to restructure the XML but this will do nicely. Example template here

<?import:[template URI]?>

<?call:Header?>

<?if:x=1?>
 <?import:[template URI]?>
<end if?>

will not work. The import calls can not be executed at run time they are like pre-processor commands, like 'h' files in C. With that said you can then conditionally call a template within that imported sub-template. There has been a question on the forum from Todd and I have had a similar conversation with Pat and the gang at Emulex recently. Emulex have a good use case for conditional template calling. They have developed an RTF Purchase Order template that generates terms and conditions on the back of each page. They are now moving out of just using the PO for their domestic market and need it to work in an international arena. Translations aside, they just need a different set of terms and conditions for the international market version of the PO. They are already importing a T&Cs template - all they need to do is create a domestic and international T&C template within the sub-template. Their XML has a tag in it identifying what type of PO is being generated, so they can have something like:

<?import:file:///d:/temp/tctemplates.rtf?>
<?choose:?>
  <?when: .//TYPE=1?>
   <?call:TC1?>
  <?end when?>
  <?when: .//TYPE=2?>
   <?call:TC2?>
  <?end when?>
  <?otherwise:?>
   <?call:TC3?>
  <?end otherwise?>
<?end choose?>

This would handle 3 possible T&C templates in their sub-template.

Todd had a good comment, why are the 'call' statements not closed with an 'end call'. I did not have a good answer. Suffice to say, we handle it for you - a good if inconsistent, feature. If you want some example code, get it here.

<?if:CF_LPN_FLAG='Y'?>
 <?split-by-page-break: ?>
<?end if?>

<?for-each:DEPT?>
...
...
...
<?split-by-page-break:?>
<?end for-each?>

Another totaling question on the forum a few days ago from user645621- not your average (excuse the pun, you'll understand once you read the question) totaling question I must say. Check out the other 'total' posts here.

I am trying to do something a bit obscure with BIP. My XML is something like:

<DETAIL>
 <PERSON>Bloggs, Joe</PERSON>
 <LOCATION>XYZ</LOCATION>
 <BILLABLE_HOURS>1</BILLABLE_HOURS>
 <AVAILABLE_HOURS>1</AVAILABLE_HOURS>
</DETAIL>
<DETAIL>
 <PERSON>Bloggs, Joe</PERSON>
 <LOCATION>XYZ</LOCATION>
 <BILLABLE_HOURS>5</BILLABLE_HOURS>
 <AVAILABLE_HOURS>5</AVAILABLE_HOURS>
</DETAIL>

Note the data has been flattened because we need to regroup in different ways. I am regouping as such Location (Master) then by Person (Detail) What I need to do is find the Billable % at Person Group which is Billable Hours / Available Hours (<?sum(current-group()/BILLABLE_HOURS) div sum(current-group()/AVAILABLE_HOURS)?> - which is OK, but at Location level I need the average of the billable % at the location level. I have tried assigning the calculation for the billable %age to a variable and then dividing by the number of records but this has not proven to work. My thoughts where to create a variable to hold the total pct for a location and then divide by the number of records (another variable). The problem is assigning the calculation to a variable ie <?xdoxslt:set_variable($_XDOCTX, ‘PCT_TOT', (sum(current-group()/BILLABLE_HOURS) div sum(current-group()/AVAILABLE_HOURS)) + xdoxslt:get_variable($_XDOCTX, ‘PCT_TOT’))?> Results in an error - An internal error condition occurred in the Template Builder

Some others have contributed to the thread, namely Srini but our mysterious user was not happy with the proposed solutions. I jumped in with two others - might not be right either and they raise another question I would be glad to hear from you on ... read on.

Assuming you have :

   <?for-each-group:DETAIL;./LOCATION?>
   
   LOCATION
   
   Table of details - <?for-each:current-group()?> PERSON BILLABLE_HOURS AVAILABLE_HOURS % <?end?>
   
   <?end for-each-group?>
   

<?sum(current-group()/BILLABLE_HOURS)?>

<?sum(current-group()/AVAILABLE_HOURS)?>

<?sum(current-group()/BILLABLE_HOURS) div sum(current-
                                            group()/AVAILABLE_HOURS)?>

<?xdoxslt:set_variable($_XDOCTX, 'AvgHrs', 0)?>

<?BILLABLE_HOURS div AVAILABLE_HOURS?> --- show the line value
<?xdoxslt:set_variable($_XDOCTX, 'AvgHrs', 
   xdoxslt:get_variable($_XDOCTX, 'AvgHrs')+ (BILLABLE_HOURS 
     div AVAILABLE_HOURS) )?> 

<?number(xdoxslt:get_variable($_XDOCTX, 'AvgHrs')) div count(current-group()/PERSON)?>

I have Mike who leads the Product Management team for today's post. If you create a report in the Enterprise release based on a data template you'll know you can get the parameter values entered by the user into the XML data set and can then include them in your output very easily.

But, what if you are not using a data template, maybe a single query or a web service? You can still get at the parameter values - just a little effort is required. The server actually passes all of the parameter values to the template you just need to register your interest in them.

Lets assume we have a parameter in our report called 'param1' - we want to be able to see that value in our report output. All you need to do is declare the parameter in the template:

<?param@begin:param1;'None'?>

<?$param1?>

That dollar sign is important! Note that if you are using an LOV for a parameter and doing something like this for the select statement:

select department_name

,         department_id 

from departments

Where you show the user the name but pass the id to your data extract. We will pass the id to the template. If you are using the 'All' option on your report parameter we actually pass '*' as the All value so you need to handle that.

So, you can render the parameter values in the report output but of course you can now get into the wonderful world of template filtering. Lets assume you have a report that generates sales data for all of your local offices. You want each office manager to only see their own sales numbers. Rather than execute the report for each office you can run the extract for all offices (using the caching we provide) and just have the template limit the data that can be viewed in the output when the office manager sees the report.

Assume you have the OFFICEID as a hidden parameter in a report. You can then limit the data based on the parameter value:
<?param:OFFICEID?>
<?for-each:SALES_DATA[OFFICE_ID = $OFFICEID?>
....
<?end for-each?>

The text in the [ ] is an XPATH expression which is effectively an 'if' statement on the group.
So, there are your parameter values!

http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:74,69,75,71,78,71,87,82,84,59,88,79&chxt=x,y&ch
xl=1:|Stores 1-50|Stores 51-100|Stores 101-200|Stores 201-300|Stores 301-400|Stores 401-500|Stores 501-600|Stores 601-700|Stores
701-800|Stores 801-900|Stores 901-1000|Stores 1000+

<STORES>
 <STORE>
  <RANGE>Stores 1-50</RANGE>
  <SALE>
   <WEEK>1</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>2</WEEK>
   <AMT>12</AMT>
  </SALE>
  <SALE>
   <WEEK>3</WEEK>
   <AMT>15</AMT>
  </SALE>
  <SALE>
   <WEEK>4</WEEK>
   <AMT>11</AMT>
  </SALE>
  <SALE>
   <WEEK>5</WEEK>
   <AMT>10</AMT>
  </SALE>
  <SALE>
   <WEEK>6</WEEK>
   <AMT>16</AMT>
  </SALE>
 </STORE>
...
...
<STORES>

<xsl:variable name="dList"> 
 <xsl:for-each select=".//STORE">
 <xsl:value-of select="sum(SALE/AMT)"/>
  <xsl:if test="position() != last()">
   <xsl:text>,</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

<xsl:variable name="sList"> 
 <xsl:for-each select=".//STORE">
  <xsl:value-of select="RANGE"/>
  <xsl:if test="position() != last()">
   <xsl:text>|</xsl:text>
  </xsl:if>
 </xsl:for-each>
</xsl:variable>

concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:'
,$dList
,'&chxt=x,y&chxl=1:|'
,$sList)

url:{concat('http://chart.apis.google.com/chart?cht=bhs&chtt=Store+Sales&chs=600x400&chd=t:',$dList,'&chxt=x,y&chxl=1:|',$sList)}

-Dhttp.proxyHost=www-proxy.us.oracle.com -Dhttp.proxyPort=80

• InitPT - <?init-page-total: contd_footer?> - initializes a page total object 'contd_footer' - for more info on this check the user guides.
• ET - <?add-page-total:contd_footer;1?> - within the table, adds the total into the page.
• Footer - <?table-footer:?> - an undocumented command (we had other plans for the command for a later release - hence the lack of doc) to specify that this row is the table footer. MSWord does not have a concept of table footer so we need to mark it as such for the rendering engine to 'see'. Notice its in the last row of the table - no borders remember (thats optional)
• PT - <xdofo:inline-total display-condition="exceptlast" name="contd_footer">Continued ...</xdofo:inline-total> - this is the cool piece. It is supposed to render the page total, but instead it will show the string 'Continued ...' on all but the last page of the table.
• EET - <?end-page-total:contd_footer?> - this closes out the page total command.

<xsl:variable name="vState">
 <xsl:for-each select=".//Row[position() > 1]">
  <xsl:value-of select="Data[2]"/>
 </xsl:for-each>
</xsl:variable> 

url:{concat('http://chart.apis.google.com
 /chart?chco=f5f5f5,edf0d4,6c9642,365e24,13390a&chd=s:',$dList,'&chf=bg,s,eaf7fe&
  chtm=usa&chld=',$vState,'&chs=440x220&cht=t')}

<kml xmlns="http://earth.google.com/kml/2.0">
 <Response>
  <name>500 Oracle Parkway</name>
  <Status>
   <code>200</code>
   <request>geocode</request>
  </Status>
  <Placemark id="p1">
   <address>500 Oracle Pkwy, Redwood City, CA 94065, USA</address>
   <AddressDetails Accuracy="8" xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0">
    <Country>
     <CountryNameCode>US</CountryNameCode>
     <AdministrativeArea>
     <AdministrativeAreaName>CA</AdministrativeAreaName>
      <Locality>
       <LocalityName>Redwood City</LocalityName>
       <Thoroughfare>
        <ThoroughfareName>500 Oracle Pkwy</ThoroughfareName>
       </Thoroughfare>
       <PostalCode>
        <PostalCodeNumber>94065</PostalCodeNumber>
       </PostalCode>
      </Locality>
     </AdministrativeArea>
    </Country>
   </AddressDetails>
  <Point>
   <coordinates>-122.262227,37.531028,0</coordinates>
  </Point>
 </Placemark>
</Response>
</kml>

<kml xmlns="http://earth.google.com/kml/2.0">

<?coordinates?>

<?namespace:x="http://earth.google.com/kml/2.0>

<?x:coordinates?>

  <Point>
   <coordinates>-122.262227,37.531028,0</coordinates>
  </Point>

url:{concat('http://maps.google.com/staticmap?center='

 ,substring-before(substring-after(x:Point/x:coordinates,','),',')

 ,','

 ,substring-before(x:Point/x:coordinates,',')

 ,'&zoom=14&size=300x300&markers=' 

 ,substring-before(substring-after(x:Point/x:coordinates,','),',')

 ,','

 ,substring-before(x:Point/x:coordinates,',')

 ,'&key=|dev key|

In the Google Maps entry from a few daze back I mentioned that to use the XML coming from the Google API I needed to get funky in the template because of the XML format. I have had two inquiries this week about how to handle an empty namespace, so I thought I'd break out those paragraphs into an article.

the XML in question starts.

<kml xmlns="http://earth.google.com/kml/2.0">
 <Response>
  <name>500 Oracle Parkway</name>
  <Status>
   <code>200</code>
   <request>geocode</request> 

Looks innocuous enough but that first line will trip up the unwary RTF template developer. Its a namespace declaration, but you support namespaces right? They just need to be declared in the top of the template and then use the namespace prefix when referencing the elements in the XML. For instance, the following XML

<INVOICES inv="http://www.oracle.com/xml/2.0">
<INVOICE>
<NUMBER>1001021</NUMBER>

can be handled by declaring the namespace in the template thus:

<?namespace:inv="http://www.oracle.com/xml/2.0">

and then using 'inv' as a prefix to every data reference e.g.

<?inv:INVOICE?>

But this Google namespace has a problem - there is no prefix

<kml xmlns="http://earth.google.com/kml/2.0">

So how can you handle it? We just need to provide a dummy prefix in our template, for instance, in our template we declare..

<?namespace:x="http://earth.google.com/kml/2.0>

we then need to prefix all of our element names with 'x:'

<?x:coordinates?>

I know, you're asking what the heck do we need namespaces for in the first place? A kind Wikipedia soul has stated:

'In general, a namespace is an abstract container providing context for the items (names, or technical terms, or words) it holds and allowing disambiguation of items having the same name (residing in different namespaces).'

'Disambiguation'? is that a word? Basically, namespaces allow you to uniquely identify a set of names so that there is no ambiguity when objects having different origins but the same names are mixed together.

For example:

<INVOICES >
 <INVOICE>
  <NUMBER>1001021</NUMBER>
  <NUMBER>1001011</NUMBER>

you can not discern between the INVOICE values however with the use of a namespace

<INVOICES inv="http://www.oracle.com/xml/2.0">
 <INVOICE>
  <NUMBER>1001021</NUMBER>
  <inv:NUMBER>1001011</NUMBER>

we can now reference the two NUMBER elements, they are distinct from each other.

So, why do Google have an empty namespace? This might be down to the developer at Google that wrote the code neglected to specify a prefix. Maybe it was done to keep me awake when I blogged it either way, we can and now you can handle it :0)

I wrote about OMR marks back in April - I made a passing comment about using MSWord shapes to put the 'slugs' onto the document. I thought I had written about that method previously - I hadn't!

Klaus put together an RTF a while back to demo this - I nagged him for it to support this post. He recreated it for us. The basics are to add a line shape to the RTF template where you need the marks.

Its a simple case of adding the line shapes to the document - positioning them might get a little tricky but remember you can change the units used in the document to something finer than Inches or Centimeters such as Points or Picas. Tools > Options > General > Measurement Units.

If you need to have the marks on every page, the key is to put the template into Header/Footer mode - View > Header/Footer. You can then add the 'slugs' where ever you need them on the document.

I have not tried it but if you need more dynamic placement of the slugs you could use the shape manipulation functionality we provide in the template. Check the user documentation or for a quick overview the shapes demo created by Jen in the Demo Catalog.

I have loaded Klaus' sample file here.

I have seen quite a few templates recently that are using our updatable variables to keep a track of the current record being processed. Regular readers will know my dislike for the updatable variables. Don't get me wrong, they have their place ... sometimes. It might be the way you set the dang things up that annoys me so much and I should take some blame for not chasing the dev team up to simplify the declaration and retrievel. Here's the code to define and assign a value to a variable x:

<?xdoxslt:set_variable($_XDOCTX, ’x’, 10)?>

now lets update the variable, adding 10 and then show it

<?xdoxslt:set_variable($_XDOCTX, 'x', xdoxslt:get_variable($_XDOCTX, 'x')+10)?>
<?xdoxslt:get_variable($_XDOCTX, 'x')?>

Too much right? Imagine using them to track the number of the current record. Folks are doing this in their templates. There is no need - the underlying standard for RTF templates is XSL and it has some neat native functions that can be used to track the current record and the last record.

position()

last()

For some examples I indebted to Chris from UK Oracle Consulting. Chris will be out at Oracle World again this year so if you would like to hear how the Queen's English should be spoken drop by the demo grounds. I seem to have developed some mid-atlantic drawl - my family in the UK think Im lost to those danged Yankees! I do still pronounce butter with t's rather than d's but my kids are struggling to understand me when I ask for a 'glass of water' rather than 'wadder'. Enough language mangling already! Back to Chris' examples. He came up with the following XML data.

<ROOT>
  <LIST_G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>123</HEADER_ID>
    </G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>456</HEADER_ID>
    </G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>789</HEADER_ID>
    </G_ORDER_HEADERS>
  </LIST_G_ORDER_HEADERS>
</ROOT>

and some examples of position() and last()

.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[HEADER_ID = '123']/position() RETURNS '1'
//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() = last()]/HEADER_ID RETURNS '789'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[last()]/HEADER_ID RETURNS '789'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() != last()]/HEADER_ID RETURNS '123' and '456'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[2]/HEADER_ID RETURNS '456' 

You'll have noticed the [ ] - these contain what are know as XPATH expressions. They are very powerful and well worth taking a look at - I'll try and cover XPATH more than we do in the documentation.
Update
===========
Leslie, our fab doc writer has just pointed out that I should have stated:
I will work with the doc writer to get some more in the documentation

I'll also try and get some more in here in the meantime :)

You can of course use them both in a conditional statement too:

<?if:position() = last()?>
 Last Record!
<? end if ?>

Chris' third example is doing something similar but its using the condition within an XPATH expression, G_ORDER_HEADERS[position() != last()] - inside those square braces you do not need the 'if'.

So, throw out those updatable variable whenever you can and get native!

More on using shapes in your RTF templates and outputs. I think I have mentioned before that the shape support is very cool but finding real world applications for them is a little tougher. Im pleased to see a few more folks using them in their outputs. they are extremely flexible and if your users can imagine a visualization they want then shapes can go so much further than any charting package.
If you want to check out the shape support and dont want to pore through the doc, check out Jen's latest flash demo on the demo catalog - second from the right.

Just recently this has gotten a little out of hand, in a nice way of course. I have been exchanging emails with Amit from Persistent Systems in India. I provided some initial help with some shape visualizations and Amit implemented a very nice interface for a competency report.

Very cool cylinders, if you want to see the shape set up in the template, get the zip here. There was also another visualization I had not seen.

Im not entirely sure how it ought to be read but I guess Amit's users know. Those were achieved using some funky single celled table manipulations.

I think once Amit's users got to see the current outputs their brains started whirring as users brains do. They then came up with the following for Amit to have a go at implementing.

Wow! those combs, triangles and slugs look very cool, check out the rounded rectangles - no way any generic charting package can do those. Can shapes step is? Of course it can - you can get the RTF here.

For the comb, its a case of building it using line shapes and then grouping it. Its not going to be manipulated but the triangles and slugs are - its just a case of adding them into the template and then data enabling them.
The rounded rectangles were a little easier - Im assuming that you only need a single black rectangle per grid. I just created the 9 based white rectangles in the template, added the black one and then added the x,y movement manipulation to move it over the appropriate white rectangle. I have to admit shapes can get fiddly. If you start grouping them and want to add/edit the code to move them either individually or as a group it can get frustrating but your users are going to love you for it. Whats going to look better and be easier to read - the samples above or a flat table full of numbers? We're humans, we like pretty pictures and they can speak a thousand words of course.

<SALES>
 <SALE>
  <YEAR>2006</YEAR>
  <SOFTWARE>1200</SOFTWARE>
  <HARDWARE>850</HARDWARE>
  <SERVICES>2000</SERVICES>
 </SALE>
 <SALE>
  <YEAR>2007</YEAR>
  <SOFTWARE>1000</SOFTWARE>
  <HARDWARE>800</HARDWARE>
  <SERVICES>1100</SERVICES>
 </SALE> 
 <SALE>
  <YEAR>2008</YEAR>
  <SOFTWARE>900</SOFTWARE>
  <HARDWARE>1200</HARDWARE>
  <SERVICES>1500</SERVICES>
 </SALE> 
 </SALES>

chart:
<Graph graphType="LINE_VERT_ABS"><LegendArea visible="true" />
 <LocalGridData colCount="{count(xdoxslt:group(.//SALE, 'YEAR'))}" rowCount="3">
 <RowLabels>
  <Label>SOFTWARE</Label>
  <Label>HARDWARE</Label>
  <Label>SERVICES</Label>
 </RowLabels>
 <ColLabels>
  <xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <Label>
  <xsl:value-of select="current-group()/YEAR" />
</Label>
</xsl:for-each-group>
</ColLabels>
<DataValues>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SOFTWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/HARDWARE)" />
</Cell>
</xsl:for-each-group>
</RowData>
<RowData>
<xsl:for-each-group select=".//SALE" group-by="YEAR" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Cell>
<xsl:value-of select="sum(current-group()/SERVICES)" />
</Cell>
</xsl:for-each-group>
</RowData>
</DataValues>
</LocalGridData>
</Graph>

<Graph  markerDisplayed="true" >
<SeriesItems>
 <Series id="0" markerShape="MS_SQUARE"/>
 <Series id="1" markerShape="MS_CIRCLE"/>
</SeriesItems>
...
</Graph>

• MS_SQUARE
• MS_CIRCLE
• MS_DIAMOND
• MS_PLUS
• MS_TRIANGLE_DOWN
• MS_TRIANGLE_UP

I bet $10 that many of you have reports that have numeric content that need to be formatted according to their currency. As many of you know you can use the formats that are provided by MS Word or our own dialog.

There are a limited number of formats but you can add/remove elements to the formats. For example you can add a '$' or any other currency symbol to any of them so '#,##0.00' can be updated to '$#,##0.00'. All very well if you have a report with a single, known currency, what can you do if there are multiple currencies or unknown currencies at design time?

We do have a format-number function thats documented, again it suffers if you dont know the formats you need. We now have (5.6.3+) a format-currency function - this is format-number on plenty of caffeine. It takes the format:

<?format-currency:value;currency_code;'true|false'?>

Only thing that needs explanation is the true|false - this turns the currency symbol on or off. If its 'false', you'll get the correct number formatting but no symbol.

You can of course substitute in XML values for the currency_code. Assuming we have the following XML.

<G_INVOICES>
<TRX_NUMBER>510262</TRX_NUMBER>
<TRANS_TYPE>Standard</TRANS_TYPE>
<TRANSACTION_DATE>13-DEC-05</TRANSACTION_DATE>
<CURRENCY>EUR</CURRENCY>
<TRANS_AMOUNT>19349.69</TRANS_AMOUNT>
</G_INVOICES>

we can use the following for the currency formatting field.

<?format-currency:TRANS_AMOUNT;CURRENCY;'true'?>

this will give us the correct currency formatting and add the currency symbol if available, otherwise we'll drop in the currency code provided. You can now generate the following types of output.

10¢ to the first person who finds out what an MGA is!

Got a request to come up with a list of all the extension functions we provide in Publisher. I finally got around to generating some javadoc for the XSLTFunctions.java.

You can get the zipped java doc for the 5.6.3 release here.
You can get the zipped java doc for the 10.1.3.4 release here

Should be enough in there for you to work out whats needed in terms of parameters.

To use the functions you need to prefix them with

<?xdoxslt: function_name(parameters) ?>

Enjoy!

Got a good internal question from Nikos one of our Product managers this week, it was a sorting question but a tough one. He had the following data:

<ROWSET>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>75241.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>143054.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>West</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>108053.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>West</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>171242.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>South</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>110312.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Qtr1</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>18132.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Qtr1</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>32847.0</SALES>
  </ROW>
  ...
  </ROWSET>

You get the picture, unsorted sales data with funky values for the YEAR. What he wanted to do was get an output that sorted by Market then Product then by the YEAR value ie Jan, Feb,Mar, Qtr1 ... Total Year. He then wanted to highlight the Qtr and Year rows with other background colors. Something like this:

The first two criteria are easy, just use the for-each-group functionality. The last is a bit tougher, you could hard code the YEAR values into a table, that would get what he wanted but not very satisfying from a 'Templaters' point of view.

A quick Google and I had a plan - hat tips to XSL miesters, Michael Kay and Jeni Tennison.Still a little hard coding but nothing like the other hard coding option and a bit more satisfying. It relies some string calculations to come up with the sort order. Here's the sort command:

 <?sort: string-length(

   substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'

  ,YEAR));

    'ascending';

     data-type='number'?>

then you sort by the string length ... coool! Now you just plug that into the template.

<?for-each:current-group()?>
<?sort: string-length(
         substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'
          ,YEAR))
        ;'ascending';data-type='number'?>

Hok Min from the dev team made a good point here. To avoid that ugly and potentially unmanageable (if you wanted to use it again in the template) sort string. Just create a variable to hold the string and then reference it in the sort command:

<xsl:variable name="mnthtbl">JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year</xsl:variable>

Add to the sort by:

<?sort:string-length(substring-before($mnthtbl, ./YEAR));'ascending';data-type='number'?>

For the conditional highlighting of the 'Qtr' and 'Total Year' rows we can use the string manipulation again.

For the QtrX

<?if@row:substring(YEAR,1,1)='Q'?><?attribute@incontext:background-color;'#80FFFF'?><?end if?>

For the Total Year

<?if@row:substring(YEAR,1,1)='T'?><?attribute@incontext:background-color;'#00CACA'?><?end if?>

All Im doing is testing the first character of the YEAR value and looking for a 'Q' or a 'T'. Using the @row gets the formatting applied to the whole row. If you want the source files you can get them here.
You can of course apply this sort to any set of strings, its going to come at a small price thou. Its far far better to sort in the XML extract if at all possible. So what have we learned? this XSL stuff is cool! Google is a 'Templaters' friend and there is always an easier way!

More on barcoding today but I think this will be it ... read on!

I have been exchanging emails with Nisha from the warm and sunny town of St.Petersburg That should have been enough of a clue for you to realize I did not mean the cooler and only slightly less sunny Russian city of the same name. Im talking about the Floridian 'St Pete'. Geography lesson over ... we have been talking barcodes, more specifically advanced barcodes, in fact the Code128 variety.

Those of you that have used these barcodes will know that the data needs to be encoded before applying the barcode font. you will also know that you need to write and mount some java code for us to call so the data can be encoded. I think Nisha will not mind me saying that she was a java novice between her, Kevin in Oracle support and myself, Nisha now has Code128 barcodes coming out on their outputs in E Business Suite.

So what? I hear you cry, 'you've written loads of articles on barcoding.' Well once we had gotten things sorted out, I cheekily asked Nisha to come up with a small doc on the process that was followed to get the barcodes out on to the paper. It seems the solution is somewhat broken up and spread out. Unbelievably, Nisha stepped up and wrote a document for us. Im very grateful.

Get the real Barcoding 101 for EBS here!
Yes, its specific to Nisha's requirement and yours might vary a little but its a good guide on what you need to do to get everything up and in the right place to get advanced barcoding working. Its written by someone that actually needed to get it into production, rather than us writing what we think you need. Maybe we should send developers to customers before they write a jot of code and then send them again afterward so that they know what its like to use their software. Anyhoo, thanks Nisha, you rock!

Im not talking about filling your reports with fluff. I can remember, mistakenly filling my high school essays with fluff and padding. Why use 5 words when 10 can say it more eloquently and fill the word count more quickly. Sadly, there was no pulling the wool over my teacher's eyes - just had to learn more facts!

I have just been helping out on a demo for a customer proof of concept. They wanted the following report output.

Nothing fancy there on the surface, building the report is straightforward enough. We can get this output very quickly.

But have you noticed in the original, there is some space after each set of categories.

We can achieve this by manipulating the 'padding' in the first cell of the row to effectively increase the height of the whole row. This increases the space between the last row of the group and the beginning of the next.

Just insert the following in a form field in the first cell:

<?if:LAST='Y'?>
<?attribute@incontext:padding-bottom;'10.0pt'?>
<?end if?>

The LAST element was in the XML to define whether the current member of a group is the last. You could equally use a position() based if statement, you just need to have some means of identifying the last member of a give grouping. The next line just manipulates the 'padding-bottom' attribute - here we are making it 10pts. This effectively provides us the row height we need to provide the spacing between the rows we need. There are of course other padding attributes:

• padding-top
• padding-bottom
• padding-start
• padding-end

XSL uses 'end' and 'start' rather than 'right' and 'left' for internationalization purposes. With the extra conditional field we can now generate the required output.

this post kinda begs the question, 'how the heck do you know what you can manipulate?' Well there is the XSL-FO spec available, if you want to wade through it. Personally, I dont very often. My best friend is the Template Builder for Word -> Tools > Export > XSL-FO Stylesheet. this will dump your current RTF template into the browser. There you can check out whats going on in the template. Its not that tough - it's bit like complex HTML, there are tables, rows, cells, etc and each have attributes that you can override to your hearts content. Yes, you have to write some code at the moment, but the extra power in the formatting is worth it I think. I have not touched Oracle Reports (in anger) in a while but I can not think how I would achieve the layout above, not off the top of my head anyway. So get padding!

I have been trawling through our documentation lately. Looking for something else I came across something that I have been asked for lately. The need to keep paragraphs of text together!
This functionality was actually delivered back in 10.1.3.3.3, here is Leslie's prose ...

Support for "Keep Paragraph with the Next Paragraph"

The RTF standard includes a command that enables you to "keep a paragraph with the next paragraph." This command prevents a page break between the paragraph for which it is enabled and the following (or next) paragraph.

To enable "keep paragraph with the next paragraph" in Microsoft Word:

• Select the paragraphs you want to keep together on a page.
• From the Format menu, select Paragraph, and then click the Line and Page Breaks tab.
• Select the Keep with Next box.

Keep Paragraph Intact
The RTF standard includes a command that enables you to keep all the lines of a paragraph together ("keep paragraph intact") and prevent the paragraph from breaking across pages.

To enable "keep paragraph intact" in Microsoft Word:

• From the Format menu, select Paragraph, and then select the Line and Page Breaks tab.
• Deselect the Keep lines together check box.

So now you know ...

More mails this week, its funny how you get a batch of requests for something all at the same time. This past week the proverbial hot potato has been Tree Maps and how to make em?

For the uninitiated, a tree map is a 2 dimensional graphical visualization of your data e.g. school size + absenteeism, maybe your debtors and the size of their debt and their delinquency or your organizations current project load, their relative size and how far behind or ahead they are compared to plan. For a large amount of data, say, listing on the stock market its very hard to compared whos up and whos down just by looking at tabular data. You can color code the data for sure but if you have masses of rows its tough to compare. A tree map gives you a easy visualization of common entries and they are color coded to make life even easier.

In the graphic above we can see schools in a school district, the size of the of the rectangle is affected by the school student population ie a bigger one for a larger school. The color of the rectangle is governed by the difference from an attendance target the school has for a given day.

Now you know what they are, so how do you build em?

I first looked at RTF templates, they can do so much and they can partially help but only for single dimension data. We can conditionally color a table cell based on some data but resizing that cell gets very complicated very quickly - I ended up in hard core XSL too quickly. I did find a brave soul out on the intertubes that had built several XSL style sheets to get the tree map - IMHO its just too expensive a development. If you only need a sing dimension then RTF templates are quick and easy.

You can get the template and data here.

Much more useful and altogether more cool from an output format standpoint is the good ol Flex template. I have not looked at Flex for a while, digging back into it I was amazed at the amount of open source development going on around building re-usable flex components. Don't want to build it yourself, go google for it. The Google Code site has masses of components and examples, just search for it. That's just what I did and came up with Josh's tree map component page. Its covered by an Open Source license from MIT so both kudos and thanks to Josh for it.

Seeing the MIT license got me digging through links to see what else might be out there from the MIT brainiacs. All you could ever want to know about tree maps.
Then I found this site, the MIT Simile Widgets - some awesome stuff going on in there, not Flex necessarily but some very rich interactive visualizations.

Getting back to Tree Maps and Flex - using the component in a project is easy enough, getting Publisher to generate data that will work with the component is a whole other kettle of fish ... I'll save that for tomorrow ...

Following on from yesterday's intro to tree maps we now get into the nitty gritty of the Flex component and BIP.
The flex component is not that nitty really, its very easy to use, the main entry for it :

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}" labelField="@NAME" weightField="@TOTAL_STUDENTS" x="0" y="0"
colorFunction="{itemToColor}" dataTipFunction="{itemToToolTip}"/>

is pretty simple. It needs to know your data source (dataProvider.) The labelField, what text to put in the rectangle, the weightField, this governs how big the rectangle is. The colorFunction - how to colour the rectangles and finally the dataTipFunction, this is a popup when folks hover over a rectangle to show them info about the underlying data.

The component download comes with a bunch of samples, I took the stock market example and bent it to my will, cos Im strong like that!

Both the color functions and dataTipFunctions are provided, they are easy enough to understand and can be modified easily. josh provides some good getting started doc.
All sounds very easy and the component is, its the data it needs or more importantly the data structure it needs thats a bit more tricky.

Those of you that know and use Publisher (standalone/BIEE) will know that if you give Publisher a piece of SQL it will return you XML. Its flat XML, we call it 'ROWSET/ROW' - the treemap component can not use it - it actually can, but you get some groovy results. The sample data Josh works with is simple enough:

<node label="Mail" data="100">
 <node label="Inbox" data="70"/>
 <node label="Personal Folder" data="10">
  <node label="Business" data="2"/>
  <node label="Demo" data="3"/>
  <node label="Saved Mail" data="5" />
 </node>
<node label="Sent" data="15"/>
<node label="Trash" data="5"/>
</node> 

But you'll notice that there are elements and attributes, you will also remember that Publisher does not generate attributes on its own. It appears the component needs a hierarchy with attribute values.
We can get Publisher to generate the attributes but we need some help, I had to use SQL XML. I have written about this in the past, its very powerful if a little fiddly to get the XMLELEMENT and XMLATTRIBUTES commands in the right spot. I managed to get a structure that worked with the tree map component with the following SQL XML.

select
 XMLELEMENT("SCHOOL",
  XMLATTRIBUTEs( e.SCHOOL_NAME as NAME,
   sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) as "ABSENT",
   count(e.stu_id) as "TOTAL_STUDENTS",
   round((sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "PCT_ABS",
   round(0.14 - (sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "DIFF_FROM_TARGET"
    )
    ).getClobVal() as SCHOOLS
from ENROLLMENT e,
SCHOOL_TYPE t
where e.sch_cd = t.sch_cd
and t.type = 'H'
and e.calendar_dt = to_date('10/24/2007','MM/DD/YYYY')
group by e.SCHOOL_NAME

Lots of heavy lifting going on in there but it basically generates:

<ROWSET>
<ROW>
<SCHOOLS>
<SCHOOL NAME="Dauphin High School" ABSENT="223" TOTAL_STUDENTS="1925" PCT_ABS=".116" DIFF_FROM_TARGET=".024">
</SCHOOLS>
</ROW>
. . .

We can not get rid of the ROWSET/ROW elements but we can live with that. All we need to do is specify the path to the SCHOOL level i.e.

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}"

I have yet to investigate if we can use a data template to avoid the extraneous elements. The component will allow you to have multiple levels of data just like a tree so you can build outputs like this.

You can get the complete Flex project here and the Publisher report here.

I got a good template last week that made me double take. Those of you that have seen enough Oracle themed powerpoints will remember the preponderance of red blocks on the slides. The problem was simple, the template was not working?

When I first opened it from my mail client, I did a double take thinking the emailer was trying to create a powerpoint template. Tough to see in the graphic below ... click to blow it up.

Imagine some one madly trying to answer mail, build a demo and answer a phone, having written or seen umpteen Oracle powerpoints and you'll be close to the state I was in when I opened it, double took and burst out laughing, someone thinking they could build a powerpoint template, bah!

After a third look I realized that it had opened in Word and it was an rtf file. With no margins and all that red it looked just like a powerpoint slide.

The reason it was not working properly came down to the shapes and the way the images were pasted in. We have some issues with the the shapes and getting them to sit in the right place. They 'float' above the regular word palette and its tough to pin them down for non PDF and RTF outputs.

A quick conversion to a table with colored cells and properly inserted images i.e. insert->picture->from file... and it renders great in powerpoint output. Template available here.

So to answer the Powerpoint template question, if you're asking it? No, its not supported but you can get some mighty fine ppt outputs from the RTF templates with a little ingenuity.

I have covered our MSWord shape support in various entries and there are some examples in the documentation. As you know we can change the shapes by skewing them, stretching them, squashing them, etc. We can also repeat them - the last is useful, if say, you want a visual representation of a rating for an employee.

As I mentioned, this can be achieved with shapes and that is documented, Jen (who watches our TARs/bugs among other fabulous activities) sent me a variation on the shape repeating. Gif repeating! The customer wanted to use an embedded image rather than a shape and have that repeat - it was a star gif which begged the question 'why not use a shape?' but anyhoo, we aim to please with BIP so here goes.

Some of you that have repeated shapes will be familiar with something like:

<?for-each@shape:xdoxslt:foreach_number($_XDOCTX,0,RATING,1)?>

This code is embedded in the shape to get it to repeat, we want to repeat an embedded image so we can not embed the code, we have to wrap it, so we get some thing like this:

A little different, but it generates the correct number of stars. You need to remove the @shape and use the @inlines to get them on a single row and then have the loop start at 1. Remember with shapes you can eliminate the star in the template with the offset but with the gif approach you cant so when RATING =1 we only want a single star so the loop needs to run from '1 to 1' to just get a single star, For rating =3 we get from 1 to 3 ie it adds two more stars to the row. If there is a situation where there should not be an image you will have to get imaginative and wrap an if around the whole thing so that nothing is shown.

If you are interested in the template, you can get it here.

Now people, I covered stars, what other images might you want to repeat on a report, I want business requirements here!

Bit of a long hiatus or holiday since my last post. I recently moved to a new position and have been playing catch up ever since. Im now caught up (kinda), you won't get 4/5 entries a week but at least 2 or 3 :0)

To get back into the groove, we're back looking at charts. Essentially a colleague was looking for something like this:

Does not look that special but in the accompanying email the OWN_NUMBER column is meant to be a 'house' image. You'll also notice its laying on top of another column. Let me say right now, I dont think the 'house' can be laid on top of the column. Getting a house into the chart is doable thou.

There is a caveat, the charting engine can embed an image into the chart but it will stretch it based on the data. So be wary about what images you are going to use.

To add the images you are going to have to get your hands a little dirty in the chart code. You will need to add a new element to the XML.

<SeriesItems defaultBorderTransparent="true" borderUsingDefaults="true">
<Series id="0" color="#33ccff">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house1.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
<Series id="1" color="#cc33cc">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house2.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
</SeriesItems>

The SeriesItems has a couple of attributes, should be understandable. You then need to provide 'Series' items for the number of columns per data set you want to plot.

The SFX element is the one that pulls the images in, notice the textureURL attribute, it holds the path to the image you want to embed. The textureDisplayMode can take the following values TDM_STRETCHED | TDM_TILED. Ignore the second - the same SFX element can be used to add a chart background and you can stretch or tile it. Tiling is not going to be relevant here.

The final output looks something like this:

Bit stretched but you get the idea, sample files here. This will work with just about all versions of Publisher and BIBeans.

More charting goodness today with a cheesy title linking us to a cheesy song from big time brat packer, Dean Martin. I get shouted down by the rest of my family for belting out 'when the moon hits your eye ...' from the shower|kitchen|while driving|etc.

One of the gifts the boys got over the holidaze was 'Guitar Hero World Tour'. For the uninitiated, its a 'video game' whereby up to four players can play guitar, bass, drums or sing along to songs from nearly all genres. You are all in a band and have to play to audiences to earn money and to unlock more songs. Its all great fun, I get to strut my stuff in the basement while the boys simultaneously beat a poor drum kit to death and rip it up on a plastic guitar. Im pleased, for the most part, that they both play the 'real' instruments too, they just wont let me join their band!

The one drawback of the game, from the gamers perspective at least, is the lack of function to be able to load a song of choice and have its component parts i.e. drum, bass, etc, broken out for you. They have some funky, build your own tune thang but its not the same. My kids loved the idea initially, until they realized that they would need to break out the dickie bows and drum brushes while Dad crooned his way through Dean Martin's repertoire. I did try to cheer them up with the fact that I also loved The Smiths and they were more 'rock.' But then they have heard me singing that non-stop since a friend bought me a 'The Smiths' greatest hits CD for the holidays. According to one son, I'm apparently, an old fart but I still bashed out Dammit by Blink 182 last night with a 200 note streak on 'Hard', take that whippersnapper!

OK, back to charting ... got some data looking like this:

<TEST>
<LIST_G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>8</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>1</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Loans Outstanding</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>7.1</BALANCE>
<IS_ORDER>2</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4.6</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China, People'S Republic Of</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12.7</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
</LIST_G_HEADER>
<CS_TOTAL>10.8</CS_TOTAL>
</TEST>

Requirement is to have pie charts showing 'Undisbursed Effective Loans by Country' and 'No. of Outstanding Loans by Country'. That data is a little tricky with the loan type being stored in that horribly named, DECODE_HEADER_B_UNDISBURSED_EF element but we can do it.

Its back to our old friend XPATH for some help to 'filter' the records that we chart. If you remember, xpath is a way of limiting records using a 'where' clause in SQL speak. You can get very sophisticated but in this case is a simple clause ie filter records where DECODE_HEADER_B_UNDISBURSED_EF = 'Undisbursed Effective Loans' for one chart and DECODE_HEADER_B_UNDISBURSED_EF = 'No. of Outstanding Loans' for the other.

The chart dialog in the template builder can not currently help on adding the 'where' clause so you'll have to dig into the code but we can get you most of the way with the dialog.

Setup the chart like so:

Now we need to add the xpath expression, double click the image thats been embedded in the RTF to get the image properties dialog. Go to the Web tab to see the code. Now look for the three instances of './/G_HEADER' and add the following - [DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans'] so you have.

.//G_HEADER[DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans']

This will limit the records to those that are of the 'No. of Outstanding Loans' type. Now run the template to see the required chart. Repeat for the other loan type et voila! you will have the required charts. You can use XPATH expressions to help you build pretty much anything you want. Sample files here.

If you're in Colorado and want to catch me and der boyz rockin' out, stop by our basement, we're here all week!

A freaky funky Friday fry up today, sorry my son has discovered aliteration and we are trying to out do each other. This nugget of BIP goodness comes from template miester, Hok-Min, on the development team.

When you are using @section in your template for the commands, 'for-each' or 'for-each-group' (e.g. <?for-each@section: ...?>), then an empty/invalid PDF can be generated if XML data file has no data for that for-each loop.

To workaround this issue, please follow these steps:

1) add a section-break at the end of RTF document.
2) On the last page, add XDO command <?if@section: not(//ID)?>No Data Found<?end if?>

Have a wonderfully wicked weekend!

Apologies to those that caught this post early and were wondering what 'alterations' had to do with a freaky funky Friday. Also managed to get the instructions corrected too. Thanks to Leslie the post actually makes some sense now. Thanks Leslie!

If you have been suffering with Template Builder not installing correctly, or like me, you have installed and uninstalled so many different versions you might want to clean house. Once you have cleaned up things should go more smoothly.

Thanks to Junichi, who is the Template Builder lead developer, for these clean up steps.

1. Uninstall BIP desktop from control->Add or remove programs.
2. Open explorer and go to "C:\WINDOWS\assembly".
3. Check if there are assemblies which start with "TB" If present, remove them all.
4. Open the MS Word startup directory and check there are no files there. The directory is normally the following.

C:\Documents and Settings\<user name><Application Data\Microsoft\Word\STARTUP

5. Open MS Word and check that you don't see the BIP tool bar.
-> If you see it, please move Normal.dot to another directory and try again.

6. Please check that the OS user you use has an administrator privilege on the PC, this is really important.
7. Please go to Control Panel -> Add or Remove programs and check if the followings have been installed.
Shared Add-in Extensibility Update for Microsoft .NET Framework 2.0 (KB908002)
Shared Add-in Support Update for Microsoft .NET Framework 2.0 (KB908002)

8. Install BIP Desktop again

Step 5 did it for me!

After following the 8 step plan, Im now clean and sober, sorry that's the other plan Im following. Should have said, your PC/laptop will be clean and fresh with a new working BIP install.

BIP Extensions? Regular readers may remember I wrote about building BIP extensions a while back. Its a means of adding functionality to your templates by making an external call to a java class. Its very very powerful and completely unsupported right now, as I warned before - do not log an SR if you get into trouble.
When I last wrote about them I got a few comments and Frank Menne from HSM in Germany went ahead and built one. Im only bringing them up again because I had cause to use one in a recent POC. In my orignal post I showed how you could pass a value thru to the extension for it to return some processed text. This POC required some more sophisticated processing. Take the following data:

<RECORD>

 <CNT>1</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>2</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>3</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>4</CNT>

 <TYPE>Individual</TYPE>

</RECORD>

<RECORD>

 <CNT>5</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>6</CNT>

 <TYPE>Individual</TYPE>

</RECORD>

public class certExtension {
    public certExtension() {
    }

    public static final String NumDesc(NodeList nodes, String eleName)
               {
                    String retStr ="";                   
                    NodeList nl;
                    Element pkg;
                    Element element;
                   String outp = "";
                    for (int i = 0; i < nodes.getLength(); i++) {
                           element = (Element) nodes.item(i);
                           nl = element.getElementsByTagName(eleName);
                           pkg = (Element) nl.item(0);
                           Node pd = pkg.getFirstChild();
                           CharacterData cd = (CharacterData) pd;
                           ...
                           }
                        }
                      return(retStr);
                 }

No nota post about some prima donna football (soccer) player that does not want to muss his hair up nor loose more precious brain cells putting his head to the ball. Far more exciting than that, its more templating fun.
I have been exchanging a few mails with Tony this morning. How can one conditionally call a header into a report? is the crux of his question.

The answer, is relatively simple, for a change. You can use a sub template (I have written about those elsewhere in this blog) to store the header (and footer) layouts you want to possibly call.
They take the format

<?template:Header1?>

... header 1 layout table, etc

<?end template Header1?>


<?template:Header2?>

... header 2 layout table, etc

<?end template Header2?>

<?template:Footer1?>

... footer 1 layout table, etc

<?end template Footer1?>

and so on, just use an RTF template to make life simple on the layout.
You need to make this sub template accessible either via a URL or directory path, from the root directory - no relative path support at the moment.

In the main template you need to register the sub template you want to use with an import statement:

<?import:http://127.0.0.1:9704/HeaderFooter.rtf?>

Now you can call the various header and footer templates you have in the sub template. In the Word header you can not use formfields so you have to write the clear text such as:

<?if:.//DEPARTMENT_NAME=’Accounting’?>
 <?call@inlines:Header?>
  <?with-param:ReportName;string('Accounting Report’)?> 
 <?end call?>
<?end if?>

you can use a choose: statement too, nested 'if' will not work thou.
I built some samples, get em here. You'll notice the parameter passing in the template call, think I have covered that elsewhere too.

Been reminiscing about music from my youth, a boy of the eighties there was so much going on back then. I was into some weird stuff Gene Loves Jezebel, the Boot Hill Foottappers, the list goes on, I was also into the Sisters of Mercy where the title for this post comes from. Im nothing if not consistent with my tenuous blog titles.

Quick question on an internal forum asking how to show just the first and last record in a group. XPATH to the rescue there are a couple of functions we can use to fetch just the first and last record. Lets assume we are looping over EMPLOYEES.

<?for-each:EMPLOYEES?>

this will fetch all records. With the addition of the XPATH expression

<?for-each:EMPLOYEES[position()='1' or position()=last()]?>

The XPATH standard allows us to create a filter, if or boolean expression to limit the number of rows to be rendered.

position() - returns the current record being process. So position() = 1 will only return the first record

last() - returns the last record pointer. So position()=last() will give us the last record only.

Combining them together with an 'or' statement ensures we only get the first and last record in our group. Done!

A few weeks back I documented an extension I built for a potential customer that passed a chunk of the XML tree out and passed back some formatted text.

Well I have another coming up, more of that later this week. One problem was how to test the extension in the template builder. It was a bit of a pain, having to merge the extension class and any supporting classes into an existing zip or jar that the builder knew about.

Today, I actually bothered to reach out to 'template builder meister' Junichi and ask if I could add a classpath command or in some way include my extension jar. Ask and you shall receive ... simple stuff, this communicating with people :0)

Just open RTF2PDF.jar under jlib directory, for me thats 'D:\Program Files\Oracle\BIP\BIP Desktop\Template Builder for Word\jlib'. You will see MANIFEST.MF file inside. Back up that files and then add a file path to class-path in the manifest file, the new jar/zip will be used when you run the preview.

The manifest file looks like this out of the box

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar
Main-Class: RTF2PDF

With my extension and supporting library I now have

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar D:/Work/idauto/idfontextension.zip D:/Work/idauto/LinearBarCode.jar
Main-Class: RTF2PDF 

Update

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar ./idfontextension.zip ./LinearBarCode.jar
Main-Class: RTF2PDF

as my file entry ... works great!

When I now hit preview for any of the formats supported my extension is pulled in without the need for all that merging.
If you are now wondering how I opened the RTF2PDF.jar file, I used winzip or zip or you could use the java tools.
Thanks for the tip Junichi. Big prizes for anyone guessing what my latest extension project has been. OK there's no prize but you'll get a mention :0)

Rainer Willems from our German office has come up with a way to allow you get formulas into your Excel outputs from an RTF template, its a neat idea. It needs some thought up front about where data is going to be rendered in the Excel output, but thats easy enough to find out, just run the template to Excel et voila.
You can check out the details here

http://www.oracle.com/global/de/community/bip/tipps/rtf_to_excel/index_en.html

and for German readers

http://www.oracle.com/global/de/community/bip/tipps/rtf_to_excel/index.html

Just one drawback, that Rainer mentions, once down this path with an RTF template its forever a 'Excel' output template. Thats unless you are on 10.1.3.4 Standalone or above when your template becomes self aware of what its being run for e.g. PDF, RTF or Excel. Then you can add some logic e.g if output != 'Excel' then hide this section of the template end if. More on that another day.

I have something to confess and an apology at the same time. Last year the folks from IDAutomation approached me for some help to show our common customers how they could use the new java barcode package that they had developed. I agreed and then promptly got caught up in all sorts of other projects. More than a year later I have finally come up with a solution - its been gnawing away at me for all this time. Apologies to the IDAUtomation folks especially Brant.

So what is the package and what does it do? Im not going to get into detail here, you can check those out on the IDAutomation web site, suffice to say its a set of java APIs that generate a barcode font image that can then be embedded in a document. Not just the simpler or 'linear' fonts such as Code39, Code128, etc oh no! They also provide a package for the 2D barcodes such as PDF417 and the tougher and more beguiling (from a human eye perspective, Maxicode format. There is not a barcode font file in sight, its a small, neat and perfectly formed (well almost, I'll get to that later) java library that will generate a gif or jpeg image of the data you want encoded.

How do you go about using it? well first up its not free but the download is, so you can try it out and see if it fits. They have the java libraries to generate an image but you can also set the whole thing up as a servlet so the solution can be used across your intranet. For this article Im just going to cover the java class approach, saving the servlet for another time (not another year's wait I promise)
To use it, I have employed the BIP extension framework - no tough rules to adhere to and I introduced the framework back in September 2007 with a couple of followup posts here and here on some more real world examples.

Not to build too much anticipation but, Im going to split the blog entry here - long entries are tiresome to read at the best of times and reading me, blathering on for page after page is not fun ... so my wife tells me :0)
Until tomorrow ... adieu!

A flurry of mails that I had not got to over the last few daze have sparked some blogging activity at last. Got a question, how to remove the blue borders that are appearing around my images in HTML output when I add a link to them. For example:

No its not me as a kid but my 16 and 13 year old ... as kids. Dang they have grown in the last 4 years. The refrigerator is always empty, towels on the floor, water bill through the roof, my car has no gas in it, again - see you got me started now!

See that border, the almighty users are not gonna want that. Its fixable but we kept it a secret, Leslie will get it into the docs as soon as its possible but for now, read on.

In your template:
1. Double click or right click > Format Picture to get to the picture properties
2. In the Web tab put

<xsl:attribute name="border">0pt solid black</xsl:attribute>

3. Save and run et voila.

For the tech heads all we are doing is setting the border attribute on the image to 0pt effectively hiding it.
This is available from 10.1.3.3.2 onwards.

See, my sons sans blue border, they still don't clear up after themselves thou. Publisher is not that good :0)

I wrote about removing duplicate data a while back, I hit it as a problem again this week. Lets just say I was using an almost undocumented feature of the data extraction engine that gave me some unexpected results. Thats all Im saying cos Im not supposed to be using it.

I ended up with a bunch of duplicated rows which was anoying but because I was not supposed to be using it I could not moan or bug the dev boys and gals. So I needed a work around, thankfully the RTF template came to the rescue.
I combined a BIP function, xdoxslt:distinct_values and XSLs fabulous concept of a variable to get rid of my duplicate rows.

As I have mentioned before variables in XSL are strange things, they are not updatable which is a drag for most of us but they can hold complete chunks of the data tree which is a big plus for many of us.

All I had in my template was:

<?variable:metrics;xdosxlt:distinct_values(G_METRICS)?>

you could also go with this neat XPATH solution which checks an entry's siblings for duplicates.

<?variable:metrics;/APCARS/LIST_G_METRICS/G_METRICS[not(HOUR=preceding-sibling::G_METRICS/HOUR)]>>

We now have a variable that has the data we need. Now all we do is loop over it just like a regular repeating node

<?for-each:$metrics?> ... <? end for-each?>

Ideally I`d have that bug fixed so we get the right data set presented to the template but its not a bug cos Im not meant to be using that feature right :0)

Too many questions on the love of my life, BIP charts. Oh the joy of finding out how to add a trendline or a fixed horizontal line, the list goes on. Its quite easy really but that's because I have an internal chart rendering tool that generates the XML I need to do cool stuff in charts. Some times I have to resort to scouring the chart DTD but the tool lets me test the feature I want. Sadly, the tool is not owned by BIP so we do not have the authority to make it public but I so wish it were. It would help you to help yourselves sooo much.

Two things to cover today ...

X Axis Labels

I've a Bar chart where X-axis labels are coming as horizontally-alligned and one value above, next value in a level below...so on. Is it possible change the alignment to -45 degree as it is available in excel chart? x-axis data label points will be displayed in uniform way then.

45 degrees is not possible at the moment. you can get them to show at 90 or 270 degrees thou

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE"/>

you can influence other properties too

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE">

There was a followup kindly answered by Vetsrini

Fixed Axis Scale

Is it possible to define a fixed scale for x-axis? I see by default Bipublisher decides scale based on the value coming from dataset. I want to set scale always from 0 to 500 with label showing at interval of 10.

<X1Axis axisMinAutoScaled="false" axisMinValue="0" axisMaxAutoScaled="false" axisMaxValue="500" majorTickStepAutomatic="false" majorTickStep="10"/>

and then,
Static Chart Line

How do I show a static line on my chart?

Vetsrini and I seemed to be in a race to answer charting questions yesterday :0)

Inside the Graph tag use:

<Y1ReferenceLine>
 <ReferenceLine index="0" visible="true" lineWidth="2" text="My Ref Line" value="550.0" displayedInLegend="true" lineColor="#3366ff"/>
</Y1ReferenceLine>

and finally

Trend Lines

Does BIP have the ability to create trend/regression lines?

Yep, it does, manual job like the others above but possible.
Under the Graph tag

<SeriesItems defaultFitlineType="FT_LINEAR"/>

Valid vaues for fitlinetype are FT_LINEAR|FT_LOGARITHMIC|FT_EXPONENTIAL

if you want multiple lines fitted to data points

<SeriesItems>
<Series id="0" fitlineType="FT_LOGARITHMIC"/>
<Series id="1" fitlineType="FT_EXPONENTIAL"/>
</SeriesItems>

I'll blog em as they come in and we manage to answer them. Happier charting!

The snow has nearly all gone, just in time for the next batch! It was a douzy of a storm, fun driving conditions, amazing how much you forget over the hot dry 'roaded' summer. My newly licensed son was itching to get out there. Mom wouldn't let him, I had to agree, our insurance couldn't take it and we have way too many trees near our house. The trees looked very pretty thou and speaking of trees (what a segue) I have been asked numerous times if BIP can generate a tree structure, the more ambitious ask for an org chart. I plan to take a look at how to do the org chart another time, for now, how about a nice tree structure.

Betw