Friday, May 7, 2010

Date Formatting and Handling for Siebel Reporting

I have discussed about the Siebel Date related issue before at one of the posts. The issue is that the IO returns the date data in a different format from the one BI Publisher expects (Canonical format), which prevents BI Publisher from formatting the date or handling the date data with its functions.

There are some workarounds available I discussed about at the post. But not anymore! Siebel Reporting product development team at Oracle has addressed the issue so you don’t need the workaround anymore!

IO Returns Date in Canonical Format with !

UPDATE: Actually, this functionality didn’t make it to the production. It’s planned, but I don’t know when or which release will have the fix. I will update once I get the update. Meanwhile you can use the following ‘getCanonicalDate’ function.

First, with the latest FixPack, which was released recently, the IO now returns the date in the canonical date format. So you can use BI Publisher’s date related function or formatting with the data without any workaround.

New Function - getCanonicalDate

Second, if you can’t apply the FixPack then you can download an updated XSLFunction.jar file from Oracle Support page (ID 764191.1) and replace the original with this new one. This new library includes a function called, ‘getCanonicalDate’, and this is the one you can use to convert the Siebel date string to the canonical date format.

Here is an example of how you can use the ‘getCanonicalDate’ function.


xdoxslt:date_diff('d', psfn:getCanonicalDate(CreatedDate), psfn:getCanonicalDate(LastUpdatedDate), $_XDOLOCALE, $_XDOTIMEZONE)

As you see in the example, make sure you declare the ‘psfn’ namespace first before you use the ‘getCanonicalDate’ function. You can take a look at ‘Siebel’s Extended Function for RTF Template’ if you don’t know how to enable your Word Add-ins Template Builder to test with the library.

And of course, you need to replace the existing one with this new library at the server side to take this in effect when you run the report. You need to do this at the two places, one is at BI Publisher server, and another is at Siebel home.

BI Publisher Server

Take a look at the bookshelf, ‘Copying Siebel JAR files to BI Publisher’, which describes the detail.

Siebel Home

Replace %SIEBEL_HOME%/classes/XSLFunctions.jar with the downloaded one.


  1. Hi Kanichiro,

    nice information. Do you have more info, how the canonical date output can be achieved? I ran a quick test on and the output for a date field is similar to "05/06/2010 07:03:25".

    This isn't canonical, is it?

    I think there must be some user property...

    Thanks for any insight


  2. Hi,
    thanks for sharing. Finally we have the canonical format from Siebel!
    I downloaded the XSLFunctions.jar and copied it to local jar directory for use with BIP Desktop. Seems to work fine and now we can use the format-date function to format the output of psfn:getCanonicalDate().
    Thanks Kanichiro!

  3. Alex, found out that the fix didn't make it to the production. Hopefully the next release.

    Benjamin, glad to hear it worked!

  4. Hello Kanichiro,

    let me ask you about currency formating. I wish to show numbers like 12.345,68€. I use format-currency for that and the values are displayed correctly in rtf preview mode (!), while in the application I get the format like €12,345.68. Any chances to display currency like in the preview?

    Many thanks

  5. Hi Kanichiro,
    For Siebel BI integration. Got stuck comparing the dates & using it in if condition to filter out the records.For comparing dates I am using exp like this.
    "xdoxslt:date_diff(‘d’, ‘2009-07-21’, ‘2009-08-21’, $_XDOLOCALE, $_XDOTIMEZONE)"

    Next step want to store the above expresison in variable/parameter for using in if, trying this:
    "?variable name=”Test” select=”xdofx:substr(‘07/21/2009’,7,4)||’-‘||substr(‘07/21/2009’,1,2)||’-‘||substr(‘07/21/2009’,4,2)”?"
    But this expression is failing.
    Can you please tell me the how to declare a variale & assign value to it & access in template(say in if condition).